-- ============================================================================
-- GMIND INSTITUTE — Fix Modules: WhatsApp + Live Chat + Countdown + New Batch
-- ============================================================================
-- Run this SQL in phpMyAdmin against the `advaysin_gmind` database.
-- This script is IDEMPOTENT and SAFE — it can be run multiple times.
--
-- It is also ROBUST against the "old backup" problem: if any of the tables
-- (batch_schedules, chat_conversations, chat_messages, video_testimonials)
-- already exist with an OLDER schema missing newer columns, this script
-- will detect each missing column via INFORMATION_SCHEMA and ADD it with
-- ALTER TABLE — without dropping any existing data.
--
-- Strategy per table:
--   1. CREATE TABLE IF NOT EXISTS  (creates if the table is entirely missing)
--   2. For each expected column, run ALTER TABLE ADD COLUMN only if the
--      column is missing (checked via INFORMATION_SCHEMA.COLUMNS).
--   3. Insert settings rows into `settings` table — guarded by column-
--      existence checks for both `setting_key`/`setting_value` (new schema)
--      AND `key_name`/`value` (legacy schema).
--   4. Insert sample batch rows only if the table is empty.
-- ============================================================================


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION A — Helper: ensure a column exists in a table
-- ══════════════════════════════════════════════════════════════════════════════
-- Each block below uses INFORMATION_SCHEMA to check whether the column exists;
-- if not, it builds an ALTER TABLE statement dynamically and executes it.
-- This works on MySQL 5.7+, MySQL 8.0+, and MariaDB 10.x+, without needing
-- DELIMITER or stored procedures (so it runs cleanly in phpMyAdmin's SQL tab).

-- ─────────────────────────────────────────────────────────────────────────────
-- A.1 — chat_conversations table
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `chat_conversations` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `visitor_name` VARCHAR(100) DEFAULT NULL,
    `visitor_email` VARCHAR(255) DEFAULT NULL,
    `visitor_phone` VARCHAR(20) DEFAULT NULL,
    `session_id` VARCHAR(128) NOT NULL,
    `status` ENUM('active', 'closed', 'pending') NOT NULL DEFAULT 'pending',
    `last_message_at` TIMESTAMP NULL DEFAULT NULL,
    `admin_unread` INT UNSIGNED NOT NULL DEFAULT 0,
    `visitor_unread` 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`),
    INDEX `idx_chat_conv_session` (`session_id`),
    INDEX `idx_chat_conv_status` (`status`),
    INDEX `idx_chat_conv_last_msg` (`last_message_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ensure each expected column exists (skip if already present)
-- (guarded blocks — only ALTER if the column is missing)

-- visitor_name
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'visitor_name');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `visitor_name` VARCHAR(100) DEFAULT NULL AFTER `id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- visitor_email
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'visitor_email');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `visitor_email` VARCHAR(255) DEFAULT NULL AFTER `visitor_name`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- visitor_phone
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'visitor_phone');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `visitor_phone` VARCHAR(20) DEFAULT NULL AFTER `visitor_email`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- session_id
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'session_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `session_id` VARCHAR(128) NOT NULL AFTER `visitor_phone`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- status (if exists but wrong type, we leave it alone — too risky to auto-cast)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'status');
SET @sql := IF(@col = 0, "ALTER TABLE `chat_conversations` ADD COLUMN `status` ENUM('active','closed','pending') NOT NULL DEFAULT 'pending' AFTER `session_id`", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- last_message_at
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'last_message_at');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `last_message_at` TIMESTAMP NULL DEFAULT NULL AFTER `status`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- admin_unread
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'admin_unread');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `admin_unread` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `last_message_at`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- visitor_unread
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'visitor_unread');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `visitor_unread` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `admin_unread`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- created_at
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'created_at');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- updated_at
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_conversations' AND COLUMN_NAME = 'updated_at');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_conversations` ADD COLUMN `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ─────────────────────────────────────────────────────────────────────────────
-- A.2 — chat_messages table
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `chat_messages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id` INT UNSIGNED NOT NULL,
    `sender_type` ENUM('visitor', 'admin') NOT NULL,
    `sender_id` INT UNSIGNED DEFAULT NULL,
    `message` TEXT NOT NULL,
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_chat_msg_conv` (`conversation_id`),
    INDEX `idx_chat_msg_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- conversation_id
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_messages' AND COLUMN_NAME = 'conversation_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_messages` ADD COLUMN `conversation_id` INT UNSIGNED NOT NULL AFTER `id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- sender_type
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_messages' AND COLUMN_NAME = 'sender_type');
SET @sql := IF(@col = 0, "ALTER TABLE `chat_messages` ADD COLUMN `sender_type` ENUM('visitor','admin') NOT NULL AFTER `conversation_id`", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- sender_id
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_messages' AND COLUMN_NAME = 'sender_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_messages` ADD COLUMN `sender_id` INT UNSIGNED DEFAULT NULL AFTER `sender_type`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- message
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_messages' AND COLUMN_NAME = 'message');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_messages` ADD COLUMN `message` TEXT NOT NULL AFTER `sender_id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- is_read
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_messages' AND COLUMN_NAME = 'is_read');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_messages` ADD COLUMN `is_read` TINYINT(1) NOT NULL DEFAULT 0 AFTER `message`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- created_at
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_messages' AND COLUMN_NAME = 'created_at');
SET @sql := IF(@col = 0, 'ALTER TABLE `chat_messages` ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Foreign key (only if it doesn't already exist — checked via INFORMATION_SCHEMA.KEY_COLUMN_USAGE)
SET @fk := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'chat_messages'
              AND CONSTRAINT_NAME = 'fk_chat_msg_conversation');
SET @sql := IF(@fk = 0,
    'ALTER TABLE `chat_messages` ADD CONSTRAINT `fk_chat_msg_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `chat_conversations`(`id`) ON DELETE CASCADE',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ─────────────────────────────────────────────────────────────────────────────
-- A.3 — video_testimonials table
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `video_testimonials` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `student_name` VARCHAR(100) NOT NULL,
    `course_name` VARCHAR(150) DEFAULT NULL,
    `video_url` VARCHAR(500) NOT NULL,
    `thumbnail` VARCHAR(500) DEFAULT NULL,
    `short_text` VARCHAR(300) DEFAULT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `status` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_video_test_status` (`status`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ensure all columns exist (one-by-one guards)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'video_testimonials' AND COLUMN_NAME = 'student_name');
SET @sql := IF(@col = 0, 'ALTER TABLE `video_testimonials` ADD COLUMN `student_name` VARCHAR(100) NOT NULL AFTER `id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'video_testimonials' AND COLUMN_NAME = 'course_name');
SET @sql := IF(@col = 0, 'ALTER TABLE `video_testimonials` ADD COLUMN `course_name` VARCHAR(150) DEFAULT NULL AFTER `student_name`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'video_testimonials' AND COLUMN_NAME = 'video_url');
SET @sql := IF(@col = 0, 'ALTER TABLE `video_testimonials` ADD COLUMN `video_url` VARCHAR(500) NOT NULL AFTER `course_name`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'video_testimonials' AND COLUMN_NAME = 'thumbnail');
SET @sql := IF(@col = 0, 'ALTER TABLE `video_testimonials` ADD COLUMN `thumbnail` VARCHAR(500) DEFAULT NULL AFTER `video_url`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'video_testimonials' AND COLUMN_NAME = 'short_text');
SET @sql := IF(@col = 0, 'ALTER TABLE `video_testimonials` ADD COLUMN `short_text` VARCHAR(300) DEFAULT NULL AFTER `thumbnail`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'video_testimonials' AND COLUMN_NAME = 'sort_order');
SET @sql := IF(@col = 0, 'ALTER TABLE `video_testimonials` ADD COLUMN `sort_order` INT NOT NULL DEFAULT 0 AFTER `short_text`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'video_testimonials' AND COLUMN_NAME = 'status');
SET @sql := IF(@col = 0, 'ALTER TABLE `video_testimonials` ADD COLUMN `status` TINYINT(1) NOT NULL DEFAULT 1 AFTER `sort_order`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ─────────────────────────────────────────────────────────────────────────────
-- A.4 — batch_schedules table  (★ the one with the "old backup" problem ★)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `batch_schedules` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `course_id` INT UNSIGNED DEFAULT NULL,
    `batch_name` VARCHAR(200) NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE DEFAULT NULL,
    `timing` VARCHAR(100) DEFAULT NULL,
    `days` VARCHAR(100) DEFAULT NULL,
    `seats_total` INT UNSIGNED NOT NULL DEFAULT 0,
    `seats_available` INT UNSIGNED NOT NULL DEFAULT 0,
    `fee` DECIMAL(10,2) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `status` ENUM('upcoming', 'running', 'completed', 'cancelled') NOT NULL DEFAULT 'upcoming',
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
    `show_countdown` TINYINT(1) NOT NULL DEFAULT 0,
    `sort_order` INT 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`),
    INDEX `idx_batch_status` (`status`, `start_date`),
    INDEX `idx_batch_featured` (`is_featured`, `show_countdown`),
    INDEX `idx_batch_course` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ★ Add missing columns if the table already exists with an old schema ★

-- course_id
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'course_id');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `course_id` INT UNSIGNED DEFAULT NULL AFTER `id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- batch_name
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'batch_name');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `batch_name` VARCHAR(200) NOT NULL AFTER `course_id`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- start_date
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'start_date');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `start_date` DATE NOT NULL AFTER `batch_name`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- end_date
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'end_date');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `end_date` DATE DEFAULT NULL AFTER `start_date`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- timing
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'timing');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `timing` VARCHAR(100) DEFAULT NULL AFTER `end_date`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ★ days — the column the user's old backup was missing ★
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'days');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `days` VARCHAR(100) DEFAULT NULL AFTER `timing`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- seats_total
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'seats_total');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `seats_total` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `days`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- seats_available
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'seats_available');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `seats_available` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `seats_total`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- fee
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'fee');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `fee` DECIMAL(10,2) DEFAULT NULL AFTER `seats_available`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- description
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'description');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `description` TEXT DEFAULT NULL AFTER `fee`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- status
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'status');
SET @sql := IF(@col = 0, "ALTER TABLE `batch_schedules` ADD COLUMN `status` ENUM('upcoming','running','completed','cancelled') NOT NULL DEFAULT 'upcoming' AFTER `description`", 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- is_featured
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'is_featured');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `is_featured` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- show_countdown
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'show_countdown');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `show_countdown` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_featured`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- sort_order
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'sort_order');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `sort_order` INT NOT NULL DEFAULT 0 AFTER `show_countdown`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- created_at
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'created_at');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- updated_at
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND COLUMN_NAME = 'updated_at');
SET @sql := IF(@col = 0, 'ALTER TABLE `batch_schedules` ADD COLUMN `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Useful indexes (only add if missing)
SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND INDEX_NAME = 'idx_batch_status');
SET @sql := IF(@idx = 0, 'ALTER TABLE `batch_schedules` ADD INDEX `idx_batch_status` (`status`, `start_date`)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'batch_schedules' AND INDEX_NAME = 'idx_batch_featured');
SET @sql := IF(@idx = 0, 'ALTER TABLE `batch_schedules` ADD INDEX `idx_batch_featured` (`is_featured`, `show_countdown`)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION B — Insert admin-controllable settings
-- ══════════════════════════════════════════════════════════════════════════════
-- The settings table on production has BOTH column pairs:
--   - setting_key  / setting_value  (new schema)
--   - key_name     / value          (legacy schema)
-- We insert into each pair separately, but ONLY if that pair's columns exist,
-- so the script won't fail on either schema variant.

-- B.1 — Detect which column pairs exist on the `settings` table
SET @has_new := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'settings' AND COLUMN_NAME = 'setting_key');
SET @has_old := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'settings' AND COLUMN_NAME = 'key_name');


-- B.2 — WhatsApp module settings (insert into new schema if those columns exist)
SET @sql := IF(@has_new > 0,
    "INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
        ('whatsapp_enabled', '1'),
        ('whatsapp_number', '919305640290'),
        ('whatsapp_message', 'Hello Gmind Institute! I have a query.')
     ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`)",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- B.3 — WhatsApp module settings (insert into legacy schema if those columns exist)
SET @sql := IF(@has_old > 0,
    "INSERT IGNORE INTO `settings` (`key_name`, `value`) VALUES
        ('whatsapp_enabled', '1'),
        ('whatsapp_number', '919305640290'),
        ('whatsapp_message', 'Hello Gmind Institute! I have a query.')",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- B.4 — Live Chat module settings (new schema)
SET @sql := IF(@has_new > 0,
    "INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
        ('chat_enabled', '1'),
        ('chat_title', 'Gmind Institute Support'),
        ('chat_subtitle', 'We typically reply in a few minutes'),
        ('chat_welcome_message', 'Hello! Welcome to Gmind Institute. How can we help you today?')
     ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`)",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- B.5 — Live Chat module settings (legacy schema)
SET @sql := IF(@has_old > 0,
    "INSERT IGNORE INTO `settings` (`key_name`, `value`) VALUES
        ('chat_enabled', '1'),
        ('chat_title', 'Gmind Institute Support'),
        ('chat_subtitle', 'We typically reply in a few minutes'),
        ('chat_welcome_message', 'Hello! Welcome to Gmind Institute. How can we help you today?')",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- B.6 — Homepage Countdown settings (new schema)
SET @sql := IF(@has_new > 0,
    "INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
        ('countdown_enabled', '0'),
        ('countdown_title', 'Next Batch Starting Soon!'),
        ('countdown_subtitle', 'Limited seats available. Enroll now!'),
        ('countdown_target_date', ''),
        ('countdown_button_text', 'Enroll Now'),
        ('countdown_button_url', '/admission')
     ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`)",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- B.7 — Homepage Countdown settings (legacy schema)
SET @sql := IF(@has_old > 0,
    "INSERT IGNORE INTO `settings` (`key_name`, `value`) VALUES
        ('countdown_enabled', '0'),
        ('countdown_title', 'Next Batch Starting Soon!'),
        ('countdown_subtitle', 'Limited seats available. Enroll now!'),
        ('countdown_target_date', ''),
        ('countdown_button_text', 'Enroll Now'),
        ('countdown_button_url', '/admission')",
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;


-- ══════════════════════════════════════════════════════════════════════════════
-- SECTION C — Sample batch data (only if the table is empty)
-- ══════════════════════════════════════════════════════════════════════════════
-- All inserts use INSERT IGNORE + WHERE NOT EXISTS guards so they only run
-- the first time, and so they don't fail on UNIQUE constraints.

INSERT IGNORE INTO `batch_schedules`
    (`batch_name`, `course_id`, `start_date`, `end_date`, `timing`, `days`,
     `seats_total`, `seats_available`, `fee`, `status`, `is_featured`,
     `show_countdown`, `sort_order`)
SELECT
    'CCC Morning Batch', NULL,
    DATE_ADD(CURDATE(), INTERVAL 15 DAY),
    DATE_ADD(CURDATE(), INTERVAL 105 DAY),
    '9:00 AM - 11:00 AM', 'Mon-Wed-Fri',
    25, 18, 3500.00, 'upcoming', 1, 1, 1
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `batch_schedules` WHERE `batch_name` = 'CCC Morning Batch');

INSERT IGNORE INTO `batch_schedules`
    (`batch_name`, `course_id`, `start_date`, `end_date`, `timing`, `days`,
     `seats_total`, `seats_available`, `fee`, `status`, `is_featured`,
     `show_countdown`, `sort_order`)
SELECT
    'O-Level Evening Batch', NULL,
    DATE_ADD(CURDATE(), INTERVAL 30 DAY),
    DATE_ADD(CURDATE(), INTERVAL 180 DAY),
    '5:00 PM - 7:00 PM', 'Tue-Thu-Sat',
    20, 15, 5500.00, 'upcoming', 1, 0, 2
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `batch_schedules` WHERE `batch_name` = 'O-Level Evening Batch');

INSERT IGNORE INTO `batch_schedules`
    (`batch_name`, `course_id`, `start_date`, `end_date`, `timing`, `days`,
     `seats_total`, `seats_available`, `fee`, `status`, `is_featured`,
     `show_countdown`, `sort_order`)
SELECT
    'ADCA Weekend Batch', NULL,
    DATE_ADD(CURDATE(), INTERVAL 20 DAY),
    DATE_ADD(CURDATE(), INTERVAL 200 DAY),
    '10:00 AM - 1:00 PM', 'Sat-Sun',
    30, 22, 8000.00, 'upcoming', 1, 0, 3
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `batch_schedules` WHERE `batch_name` = 'ADCA Weekend Batch');

INSERT IGNORE INTO `batch_schedules`
    (`batch_name`, `course_id`, `start_date`, `end_date`, `timing`, `days`,
     `seats_total`, `seats_available`, `fee`, `status`, `is_featured`,
     `show_countdown`, `sort_order`)
SELECT
    'Tally Prime Fast Track', NULL,
    DATE_ADD(CURDATE(), INTERVAL 25 DAY),
    DATE_ADD(CURDATE(), INTERVAL 55 DAY),
    '11:00 AM - 1:00 PM', 'Mon-Fri',
    15, 10, 3000.00, 'upcoming', 1, 0, 4
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `batch_schedules` WHERE `batch_name` = 'Tally Prime Fast Track');


-- ══════════════════════════════════════════════════════════════════════════════
-- DONE — verify by running:
--   DESCRIBE batch_schedules;             -- should show all 17 columns
--   DESCRIBE chat_conversations;          -- should show all 11 columns
--   DESCRIBE chat_messages;               -- should show all 7 columns
--   SELECT * FROM settings WHERE setting_key LIKE 'whatsapp_%' OR setting_key LIKE 'chat_%' OR setting_key LIKE 'countdown_%';
--   SELECT id, batch_name, start_date, status, is_featured, show_countdown FROM batch_schedules;
-- ══════════════════════════════════════════════════════════════════════════════
