-- ============================================================================
-- Gmind Institute - New Module Tables
-- ============================================================================
-- Add these tables to the existing advaysin_gmind database
-- These tables support: Demo Class, Batch Schedule, Callback, Enquiry
-- ============================================================================

-- 1. Demo Class Requests (Free Demo Class Registration)
CREATE TABLE IF NOT EXISTS `demo_class_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `student_name` VARCHAR(255) NOT NULL COMMENT 'Student name',
    `contact` VARCHAR(30) NOT NULL COMMENT 'Contact number',
    `email` VARCHAR(255) DEFAULT NULL COMMENT 'Email address',
    `course_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to courses.id',
    `preferred_time` ENUM('morning','afternoon','evening') DEFAULT NULL COMMENT 'Preferred time slot',
    `demo_date` DATE DEFAULT NULL COMMENT 'Preferred demo date',
    `branch_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to branches.id',
    `message` TEXT DEFAULT NULL COMMENT 'Additional message',
    `status` ENUM('pending','confirmed','completed','cancelled') NOT NULL DEFAULT 'pending' COMMENT 'Request status',
    `source` VARCHAR(50) NOT NULL DEFAULT 'website' COMMENT 'Source of request',
    `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_demo_status` (`status`),
    INDEX `idx_demo_course` (`course_id`),
    INDEX `idx_demo_branch` (`branch_id`),
    INDEX `idx_demo_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Free demo class registration requests';

-- 2. Batches (Batch Schedule / Timetable)
CREATE TABLE IF NOT EXISTS `batches` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `batch_name` VARCHAR(255) NOT NULL COMMENT 'Batch name/identifier',
    `course_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to courses.id',
    `branch_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to branches.id',
    `trainer_name` VARCHAR(255) DEFAULT NULL COMMENT 'Trainer/faculty name',
    `shift` ENUM('morning','afternoon','evening','weekend') NOT NULL DEFAULT 'morning' COMMENT 'Batch shift',
    `timing` VARCHAR(100) NOT NULL COMMENT 'Batch timing e.g. 9:00 AM - 11:00 AM',
    `start_date` DATE NOT NULL COMMENT 'Batch start date',
    `end_date` DATE DEFAULT NULL COMMENT 'Batch end date',
    `total_seats` INT NOT NULL DEFAULT 30 COMMENT 'Total seats in batch',
    `available_seats` INT NOT NULL DEFAULT 30 COMMENT 'Currently available seats',
    `fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Batch fee',
    `status` ENUM('upcoming','ongoing','completed','cancelled') NOT NULL DEFAULT 'upcoming' COMMENT 'Batch status',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `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_batches_course` (`course_id`),
    INDEX `idx_batches_branch` (`branch_id`),
    INDEX `idx_batches_shift` (`shift`),
    INDEX `idx_batches_status` (`status`),
    INDEX `idx_batches_active` (`is_active`),
    CONSTRAINT `fk_batches_course_id`
        FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_batches_branch_id`
        FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Batch schedules and timetables';

-- 3. Seat Bookings (Students booking seats in batches)
CREATE TABLE IF NOT EXISTS `seat_bookings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `batch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to batches.id',
    `student_name` VARCHAR(255) NOT NULL COMMENT 'Student name',
    `contact` VARCHAR(30) NOT NULL COMMENT 'Contact number',
    `email` VARCHAR(255) DEFAULT NULL COMMENT 'Email address',
    `status` ENUM('booked','confirmed','cancelled') NOT NULL DEFAULT 'booked' COMMENT 'Booking status',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_seat_booking_batch` (`batch_id`),
    INDEX `idx_seat_booking_status` (`status`),
    INDEX `idx_seat_booking_created` (`created_at`),
    CONSTRAINT `fk_seat_booking_batch_id`
        FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Seat bookings for batches';

-- 4. Callback Requests
CREATE TABLE IF NOT EXISTS `callback_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Requester name',
    `contact` VARCHAR(30) NOT NULL COMMENT 'Contact number',
    `preferred_time` ENUM('morning','afternoon','evening') DEFAULT NULL COMMENT 'Preferred callback time',
    `course_interest` VARCHAR(255) DEFAULT NULL COMMENT 'Interested course',
    `status` ENUM('pending','called','no_response','callback_scheduled') NOT NULL DEFAULT 'pending' COMMENT 'Callback status',
    `source` VARCHAR(50) NOT NULL DEFAULT 'website_popup' COMMENT 'Source of request',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_callback_status` (`status`),
    INDEX `idx_callback_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Callback requests from website';

-- 5. Enquiries (Quick Enquiry from popup)
CREATE TABLE IF NOT EXISTS `enquiries` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Enquirer name',
    `contact` VARCHAR(30) NOT NULL COMMENT 'Contact number',
    `email` VARCHAR(255) DEFAULT NULL COMMENT 'Email address',
    `course_interest` VARCHAR(255) DEFAULT NULL COMMENT 'Interested course',
    `message` TEXT DEFAULT NULL COMMENT 'Enquiry message',
    `status` ENUM('new','contacted','qualified','enrolled','lost') NOT NULL DEFAULT 'new' COMMENT 'Enquiry status',
    `source` VARCHAR(50) NOT NULL DEFAULT 'website_popup' COMMENT 'Source of enquiry',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_enquiry_status` (`status`),
    INDEX `idx_enquiry_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Website enquiries';

-- ============================================================================
-- Add new settings for WhatsApp and Maps
-- ============================================================================
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('whatsapp_number', '919305640290'),
    ('whatsapp_message', 'Hi, I am interested in your courses. Please share details.'),
    ('google_maps_api_key', ''),
    ('dark_mode_enabled', '0'),
    ('multilingual_enabled', '1');

-- ============================================================================
-- End of New Tables
-- ============================================================================
