-- Standard Student Table Schema
CREATE TABLE IF NOT EXISTS `students` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `full_name` VARCHAR(120) NOT NULL,
  `roll_no` INT NULL,
  `email` VARCHAR(255) NULL,
  `class_name` VARCHAR(60) NOT NULL,
  `age` TINYINT UNSIGNED NULL,
  `photo_url` VARCHAR(255) NULL,
  `about` VARCHAR(500) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Indexes for students
CREATE INDEX IF NOT EXISTS idx_students_roll_no ON students(roll_no);
CREATE INDEX IF NOT EXISTS idx_students_email ON students(email);

-- Admins table for application administrators
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(120) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(60) DEFAULT 'admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_login` DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sports and therapy content for the school programme
CREATE TABLE IF NOT EXISTS `school_sports` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(180) NOT NULL,
  `introduction` TEXT NULL,
  `description` TEXT NULL,
  `achievement` TEXT NULL,
  `achievement_date` VARCHAR(50) NULL,
  `image_url` VARCHAR(255) NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `kendra_sports` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(180) NOT NULL,
  `description` TEXT NULL,
  `achievement` TEXT NULL,
  `achievement_date` VARCHAR(50) NULL,
  `image_url` VARCHAR(255) NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `therapies` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(180) NOT NULL,
  `short_description` TEXT NULL,
  `description` TEXT NULL,
  `image_url` VARCHAR(255) NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `latest_updates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `headline` VARCHAR(220) NOT NULL,
  `summary` TEXT NULL,
  `content` LONGTEXT NULL,
  `image_url` VARCHAR(255) NULL,
  `date` VARCHAR(50) NULL,
  `category` VARCHAR(120) NULL,
  `slug` VARCHAR(220) NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `gallery_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(180) NULL,
  `caption` TEXT NULL,
  `category` VARCHAR(120) NULL,
  `image_url` VARCHAR(255) NOT NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `donation_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `mission_text` TEXT NULL,
  `why_support` TEXT NULL,
  `how_help` TEXT NULL,
  `account_name` VARCHAR(200) NULL,
  `bank_name` VARCHAR(200) NULL,
  `account_number` VARCHAR(120) NULL,
  `ifsc_code` VARCHAR(50) NULL,
  `upi_id` VARCHAR(120) NULL,
  `contact_name` VARCHAR(200) NULL,
  `contact_phone` VARCHAR(80) NULL,
  `contact_email` VARCHAR(200) NULL,
  `additional_notes` TEXT NULL,
  `image_url` VARCHAR(255) NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX IF NOT EXISTS idx_school_sports_published ON school_sports(is_published, display_order);
CREATE INDEX IF NOT EXISTS idx_kendra_sports_published ON kendra_sports(is_published, display_order);
CREATE INDEX IF NOT EXISTS idx_therapies_published ON therapies(is_published, display_order);
CREATE INDEX IF NOT EXISTS idx_latest_updates_published ON latest_updates(is_published, display_order, is_featured);
CREATE INDEX IF NOT EXISTS idx_gallery_published ON gallery_items(is_published, display_order);

-- =========================================================
-- Database: jiddain_news_db
-- Table: announcements
-- =========================================================
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `announcement_text` TEXT NOT NULL,
  `link_url` VARCHAR(500) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT(11) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `scrolling_announcements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(500) NOT NULL,
  `link_url` VARCHAR(500) NULL,
  `publish_date` DATE NULL,
  `expiry_date` DATE NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_announcements_active` (`is_published`, `display_order`, `publish_date`, `expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `galleries` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(180) NOT NULL,
  `description` TEXT NULL,
  `cover_image` VARCHAR(255) NULL,
  `event_date` DATE NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_galleries_published` (`is_published`, `display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `gallery_images` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `gallery_id` INT UNSIGNED NOT NULL,
  `image` VARCHAR(255) NOT NULL,
  `caption` VARCHAR(500) NULL,
  `display_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_gallery_images_gallery` (`gallery_id`, `display_order`),
  CONSTRAINT `fk_gallery_images_gallery` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product catalog schema (used by the public product page)
CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `description` TEXT NULL,
  `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_product_categories_slug` (`slug`),
  KEY `idx_product_categories_status` (`status`),
  KEY `idx_product_categories_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) NOT NULL,
  `slug` VARCHAR(180) NOT NULL,
  `code` VARCHAR(60) NULL,
  `description` TEXT NULL,
  `short_description` TEXT NULL,
  `price` DECIMAL(10,2) NULL,
  `original_price` DECIMAL(10,2) NULL,
  `discount_price` DECIMAL(10,2) NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `image` VARCHAR(255) NOT NULL DEFAULT 'img/product/default.png',
  `image_views` JSON NULL,
  `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  `featured` TINYINT(1) NOT NULL DEFAULT 0,
  `note` VARCHAR(255) NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_products_slug` (`slug`),
  UNIQUE KEY `uq_products_code` (`code`),
  KEY `idx_products_category_id` (`category_id`),
  KEY `idx_products_status` (`status`),
  KEY `idx_products_featured` (`featured`),
  KEY `idx_products_price` (`price`),
  CONSTRAINT `fk_products_category`
    FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_images` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` INT UNSIGNED NOT NULL,
  `image_url` VARCHAR(255) NOT NULL,
  `view` VARCHAR(40) NOT NULL DEFAULT 'front',
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product_images_product_id` (`product_id`),
  KEY `idx_product_images_sort_order` (`sort_order`),
  CONSTRAINT `fk_product_images_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `product_categories` (`name`, `slug`, `description`, `status`, `sort_order`)
SELECT 'stationary', 'stationary', 'Durable office and school files, available in bulk-friendly quantities.', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM `product_categories` WHERE `slug` = 'stationary');

INSERT INTO `product_categories` (`name`, `slug`, `description`, `status`, `sort_order`)
SELECT 'Handmade Products', 'handmade', 'Fabric and paper flowers, bouquets and customised greeting cards.', 'active', 2
WHERE NOT EXISTS (SELECT 1 FROM `product_categories` WHERE `slug` = 'handmade');

INSERT INTO `product_categories` (`name`, `slug`, `description`, `status`, `sort_order`)
SELECT 'Cleaning Products', 'cleaning', 'Phenyl, handwash, dishwash and liquid soap made fresh in multiple pack sizes.', 'active', 3
WHERE NOT EXISTS (SELECT 1 FROM `product_categories` WHERE `slug` = 'cleaning');

INSERT INTO `product_categories` (`name`, `slug`, `description`, `status`, `sort_order`)
SELECT 'Seasonal Products', 'seasonal', 'Festival-only pieces made around Diwali, Raksha Bandhan and other occasions.', 'active', 4
WHERE NOT EXISTS (SELECT 1 FROM `product_categories` WHERE `slug` = 'seasonal');

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Box File', 'sta-01', 'STA-01', 'Built for daily handling — office floors, school bags and shop counters — with a mechanism and board sturdy enough for repeat use.', 'Product code STA-01 from our stationary range.', 110.00, c.id, 'img/product/30.png', 'active', 1, '', 1
FROM `product_categories` c WHERE c.slug = 'stationary'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `short_description` = VALUES(`short_description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Flowers Bouquet (Velvet)', 'hmp-01', 'HMP-01', 'Soft velvet floral bouquet handcrafted for gifting and decor.', 'Handcrafted bouquet with a premium finish.', 300.00, c.id, 'img/product/6.png', 'active', 1, '', 2
FROM `product_categories` c WHERE c.slug = 'handmade'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `short_description` = VALUES(`short_description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Handwash (Rose, Lemon, Lavender)', 'cln-02', 'CLN-02', 'Handwash available in multiple fragrance options and bulk packs.', 'Rose, lemon and lavender variants available.', 430.00, c.id, 'img/product/25.png', 'active', 1, '', 3
FROM `product_categories` c WHERE c.slug = 'cleaning'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `short_description` = VALUES(`short_description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Floating Lotus Lights', 'floating-lotus-lights', NULL, 'Seasonal decorative light set for festive spaces and home styling.', 'Festive seasonal product.', 0.00, c.id, 'img/product/13.png', 'active', 0, 'Seasonal availability varies.', 4
FROM `product_categories` c WHERE c.slug = 'seasonal'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `short_description` = VALUES(`short_description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);