-- ============================================================================
-- Video Testimonials + Batch Schedule + Countdown Timer — Gmind Institute
-- ============================================================================
-- Run this SQL on your database before uploading the PHP files.
-- ============================================================================

-- ══════════════════════════════════════════════════════════════════════════════
-- 1. 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;

-- ══════════════════════════════════════════════════════════════════════════════
-- 2. BATCH SCHEDULES TABLE
-- ══════════════════════════════════════════════════════════════════════════════
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;

-- ══════════════════════════════════════════════════════════════════════════════
-- 3. COUNTDOWN SETTINGS (insert into existing settings table)
-- ══════════════════════════════════════════════════════════════════════════════
-- Try inserting with setting_key/setting_value columns (new schema)
INSERT IGNORE 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');

-- Also try with key_name/value columns (old schema — some installs use this)
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');

-- ══════════════════════════════════════════════════════════════════════════════
-- 4. SAMPLE DATA (optional — remove if not needed)
-- ══════════════════════════════════════════════════════════════════════════════
INSERT 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`) VALUES
('CCC Morning Batch', NULL, '2026-07-01', '2026-09-30', '9:00 AM - 11:00 AM', 'Mon-Wed-Fri', 25, 18, 3500.00, 'upcoming', 1, 1, 1),
('O-Level Evening Batch', NULL, '2026-07-15', '2026-12-15', '5:00 PM - 7:00 PM', 'Tue-Thu-Sat', 20, 15, 5500.00, 'upcoming', 1, 0, 2),
('ADCA Weekend Batch', NULL, '2026-07-05', '2027-01-05', '10:00 AM - 1:00 PM', 'Sat-Sun', 30, 22, 8000.00, 'upcoming', 0, 0, 3),
('Tally Prime Fast Track', NULL, '2026-07-10', '2026-08-10', '11:00 AM - 1:00 PM', 'Mon-Fri', 15, 10, 3000.00, 'upcoming', 0, 0, 4);
