-- ============================================================================
-- Gmind Institute - Database Schema
-- ============================================================================
-- MySQL 8.0+ | InnoDB | utf8mb4_unicode_ci
-- PHP 8.2-8.4 Compatible
-- Generated: 2025-03-04
-- ============================================================================

-- ============================================================================
-- Database Creation
-- ============================================================================
CREATE DATABASE IF NOT EXISTS `advaysin_gmind`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `advaysin_gmind`;

-- ============================================================================
-- Drop Tables (Reverse Dependency Order - child tables first)
-- ============================================================================
DROP TABLE IF EXISTS `exam_results`;
DROP TABLE IF EXISTS `exam_questions`;
DROP TABLE IF EXISTS `students`;
DROP TABLE IF EXISTS `exam_results`;
DROP TABLE IF EXISTS `blog_posts`;
DROP TABLE IF EXISTS `branch_news`;
DROP TABLE IF EXISTS `testimonials`;
DROP TABLE IF EXISTS `toppers`;
DROP TABLE IF EXISTS `contacts`;
DROP TABLE IF EXISTS `franchisees`;
DROP TABLE IF EXISTS `exam_questions`;
DROP TABLE IF EXISTS `exams`;
DROP TABLE IF EXISTS `faqs`;
DROP TABLE IF EXISTS `pages`;
DROP TABLE IF EXISTS `announcements`;
DROP TABLE IF EXISTS `sliders`;
DROP TABLE IF EXISTS `courses`;
DROP TABLE IF EXISTS `branches`;
DROP TABLE IF EXISTS `affiliations`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `settings`;

-- ============================================================================
-- 1. Sliders - Homepage carousel/slider images
-- ============================================================================
CREATE TABLE `sliders` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL COMMENT 'Slider headline text',
    `subtitle` VARCHAR(500) DEFAULT NULL COMMENT 'Slider sub-headline text',
    `image` VARCHAR(500) NOT NULL COMMENT 'Path to slider image',
    `link` VARCHAR(500) DEFAULT NULL COMMENT 'Optional URL when slider is clicked',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display order (lower = first)',
    `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_sliders_active_sort` (`active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Homepage carousel sliders';

-- ============================================================================
-- 2. Announcements - Scrolling/sticky announcements
-- ============================================================================
CREATE TABLE `announcements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` VARCHAR(1000) NOT NULL COMMENT 'Announcement text',
    `link` VARCHAR(500) DEFAULT NULL COMMENT 'Optional URL for more details',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_announcements_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Site-wide announcements';

-- ============================================================================
-- 3. Courses - Available courses offered by the institute
-- ============================================================================
CREATE TABLE `courses` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Course name',
    `category` ENUM('computer','accounting','programming','design','other') NOT NULL DEFAULT 'computer' COMMENT 'Course category',
    `duration` VARCHAR(100) NOT NULL COMMENT 'Human-readable duration e.g. 6 Months',
    `fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Course fee in INR',
    `description` TEXT DEFAULT NULL COMMENT 'Course description',
    `syllabus` TEXT DEFAULT NULL COMMENT 'Course syllabus/outline',
    `image` VARCHAR(500) DEFAULT NULL COMMENT 'Path to course thumbnail',
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=featured on homepage',
    `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_courses_category` (`category`),
    INDEX `idx_courses_active_featured` (`active`, `is_featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Courses offered by the institute';

-- ============================================================================
-- 4. Branches - Institute branch locations
-- ============================================================================
CREATE TABLE `branches` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Branch name',
    `city` VARCHAR(100) NOT NULL COMMENT 'City',
    `state` VARCHAR(100) NOT NULL COMMENT 'State',
    `address` TEXT NOT NULL COMMENT 'Full address',
    `phone` VARCHAR(30) DEFAULT NULL COMMENT 'Contact phone number',
    `map_link` VARCHAR(500) DEFAULT NULL COMMENT 'Google Maps embed/link URL',
    `image` VARCHAR(500) DEFAULT NULL COMMENT 'Path to branch photo',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_branches_city` (`city`),
    INDEX `idx_branches_state` (`state`),
    INDEX `idx_branches_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Institute branch locations';

-- ============================================================================
-- 5. Affiliations - Affiliations and partners
-- ============================================================================
CREATE TABLE `affiliations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Organization name',
    `logo` VARCHAR(500) DEFAULT NULL COMMENT 'Path to organization logo',
    `website` VARCHAR(500) DEFAULT NULL COMMENT 'Organization website URL',
    `type` ENUM('affiliation','partner') NOT NULL DEFAULT 'affiliation' COMMENT 'Type of relationship',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display order (lower = first)',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_affiliations_type_sort` (`type`, `sort_order`),
    INDEX `idx_affiliations_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Affiliations and partners';

-- ============================================================================
-- 6. Toppers - Top-performing students
-- ============================================================================
CREATE TABLE `toppers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Student name',
    `course_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to courses.id',
    `marks` VARCHAR(50) DEFAULT NULL COMMENT 'Marks obtained (text for flexibility)',
    `grade` VARCHAR(20) DEFAULT NULL COMMENT 'Grade e.g. A+, Distinction',
    `photo` VARCHAR(500) DEFAULT NULL COMMENT 'Path to student photo',
    `batch_year` YEAR DEFAULT NULL COMMENT 'Batch/academic year',
    `branch_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to branches.id (nullable for institute-level toppers)',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display order (lower = first)',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_toppers_course` (`course_id`),
    INDEX `idx_toppers_branch` (`branch_id`),
    INDEX `idx_toppers_batch_year` (`batch_year`),
    INDEX `idx_toppers_active_sort` (`active`, `sort_order`),
    CONSTRAINT `fk_toppers_course_id`
        FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_toppers_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='Top-performing students';

-- ============================================================================
-- 7. Branch News - News articles per branch
-- ============================================================================
CREATE TABLE `branch_news` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to branches.id',
    `title` VARCHAR(255) NOT NULL COMMENT 'News headline',
    `slug` VARCHAR(255) NOT NULL COMMENT 'URL-friendly slug (unique)',
    `content` TEXT NOT NULL COMMENT 'News article body',
    `image` VARCHAR(500) DEFAULT NULL COMMENT 'Path to featured image',
    `published_at` DATETIME DEFAULT NULL COMMENT 'Scheduled publish timestamp',
    `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`),
    UNIQUE INDEX `uk_branch_news_slug` (`slug`),
    INDEX `idx_branch_news_branch` (`branch_id`),
    INDEX `idx_branch_news_active_published` (`active`, `published_at`),
    CONSTRAINT `fk_branch_news_branch_id`
        FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='News articles per branch';

-- ============================================================================
-- 8. FAQs - Frequently Asked Questions
-- ============================================================================
CREATE TABLE `faqs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `question` VARCHAR(500) NOT NULL COMMENT 'FAQ question',
    `answer` TEXT NOT NULL COMMENT 'FAQ answer',
    `category` VARCHAR(100) DEFAULT NULL COMMENT 'FAQ grouping category',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display order (lower = first)',
    `show_on_home` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=show on homepage',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_faqs_category_sort` (`category`, `sort_order`),
    INDEX `idx_faqs_active_home` (`active`, `show_on_home`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Frequently Asked Questions';

-- ============================================================================
-- 9. Testimonials - Student testimonials/reviews
-- ============================================================================
CREATE TABLE `testimonials` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Reviewer name',
    `course_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to courses.id',
    `branch_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to branches.id',
    `rating` TINYINT UNSIGNED NOT NULL DEFAULT 5 COMMENT 'Rating 1-5',
    `review` TEXT DEFAULT NULL COMMENT 'Testimonial text',
    `photo` VARCHAR(500) DEFAULT NULL COMMENT 'Path to reviewer photo',
    `is_approved` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=approved for display',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_testimonials_course` (`course_id`),
    INDEX `idx_testimonials_branch` (`branch_id`),
    INDEX `idx_testimonials_approved` (`is_approved`),
    CONSTRAINT `fk_testimonials_course_id`
        FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_testimonials_branch_id`
        FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `chk_testimonials_rating`
        CHECK (`rating` BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Student testimonials and reviews';

-- ============================================================================
-- 10. Users - System users (admin, staff, student)
-- ============================================================================
CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Full name',
    `email` VARCHAR(255) NOT NULL COMMENT 'Email address (login)',
    `password` VARCHAR(255) NOT NULL COMMENT 'Bcrypt hashed password',
    `role` ENUM('admin','staff','student') NOT NULL DEFAULT 'student' COMMENT 'User role',
    `phone` VARCHAR(30) DEFAULT NULL COMMENT 'Phone number',
    `photo` VARCHAR(500) DEFAULT NULL COMMENT 'Path to profile photo',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active, 0=disabled',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_users_email` (`email`),
    INDEX `idx_users_role` (`role`),
    INDEX `idx_users_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='System users';

-- ============================================================================
-- 11. Students - Student enrollment details
-- ============================================================================
CREATE TABLE `students` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to users.id',
    `enrollment_no` VARCHAR(50) DEFAULT NULL COMMENT 'Unique enrollment number',
    `course_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to courses.id',
    `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to branches.id',
    `dob` DATE DEFAULT NULL COMMENT 'Date of birth',
    `address` TEXT DEFAULT NULL COMMENT 'Residential address',
    `guardian_name` VARCHAR(255) DEFAULT NULL COMMENT 'Parent/guardian name',
    `guardian_phone` VARCHAR(30) DEFAULT NULL COMMENT 'Parent/guardian phone',
    `status` ENUM('active','completed','dropped') NOT NULL DEFAULT 'active' COMMENT 'Enrollment status',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_students_enrollment_no` (`enrollment_no`),
    INDEX `idx_students_user` (`user_id`),
    INDEX `idx_students_course` (`course_id`),
    INDEX `idx_students_branch` (`branch_id`),
    INDEX `idx_students_status` (`status`),
    CONSTRAINT `fk_students_user_id`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_students_course_id`
        FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_students_branch_id`
        FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Student enrollment records';

-- ============================================================================
-- 12. Settings - Key-value application settings
-- ============================================================================
CREATE TABLE `settings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL COMMENT 'Unique setting identifier',
    `setting_value` TEXT DEFAULT NULL COMMENT 'Setting value',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_settings_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Application settings (key-value)';

-- ============================================================================
-- 13. Exams - Examination definitions
-- ============================================================================
CREATE TABLE `exams` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL COMMENT 'Exam title',
    `course_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to courses.id',
    `duration_minutes` INT NOT NULL DEFAULT 60 COMMENT 'Exam duration in minutes',
    `total_marks` INT NOT NULL DEFAULT 100 COMMENT 'Total marks for the exam',
    `pass_marks` INT NOT NULL DEFAULT 40 COMMENT 'Minimum marks to pass',
    `is_published` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=published/available',
    `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_exams_course` (`course_id`),
    INDEX `idx_exams_published` (`is_published`),
    CONSTRAINT `fk_exams_course_id`
        FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Examination definitions';

-- ============================================================================
-- 14. Exam Questions - Multiple-choice questions per exam
-- ============================================================================
CREATE TABLE `exam_questions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `exam_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to exams.id',
    `question_text` TEXT NOT NULL COMMENT 'Question text',
    `option_a` VARCHAR(500) NOT NULL COMMENT 'Option A',
    `option_b` VARCHAR(500) NOT NULL COMMENT 'Option B',
    `option_c` VARCHAR(500) NOT NULL COMMENT 'Option C',
    `option_d` VARCHAR(500) NOT NULL COMMENT 'Option D',
    `correct_answer` ENUM('a','b','c','d') NOT NULL COMMENT 'Correct option',
    `marks` INT NOT NULL DEFAULT 1 COMMENT 'Marks for this question',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display order within exam',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_exam_questions_exam` (`exam_id`),
    INDEX `idx_exam_questions_sort` (`exam_id`, `sort_order`),
    CONSTRAINT `fk_exam_questions_exam_id`
        FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Multiple-choice exam questions';

-- ============================================================================
-- 15. Exam Results - Student exam attempt results
-- ============================================================================
CREATE TABLE `exam_results` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `exam_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to exams.id',
    `student_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to students.id',
    `total_obtained` INT NOT NULL DEFAULT 0 COMMENT 'Marks obtained by student',
    `total_marks` INT NOT NULL DEFAULT 0 COMMENT 'Total marks in exam',
    `percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Percentage scored',
    `status` ENUM('pass','fail') NOT NULL COMMENT 'Pass/fail status',
    `started_at` DATETIME DEFAULT NULL COMMENT 'Exam start timestamp',
    `completed_at` DATETIME DEFAULT NULL COMMENT 'Exam completion timestamp',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_exam_results_exam` (`exam_id`),
    INDEX `idx_exam_results_student` (`student_id`),
    INDEX `idx_exam_results_status` (`status`),
    INDEX `idx_exam_results_exam_student` (`exam_id`, `student_id`),
    CONSTRAINT `fk_exam_results_exam_id`
        FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_exam_results_student_id`
        FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Student exam attempt results';

-- ============================================================================
-- 16. Pages - Static CMS pages (About, Privacy Policy, etc.)
-- ============================================================================
CREATE TABLE `pages` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL COMMENT 'Page title',
    `slug` VARCHAR(255) NOT NULL COMMENT 'URL-friendly slug (unique)',
    `content` LONGTEXT DEFAULT NULL COMMENT 'Page body content (HTML)',
    `image` VARCHAR(500) DEFAULT NULL COMMENT 'Path to featured image',
    `is_published` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=published, 0=draft',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_pages_slug` (`slug`),
    INDEX `idx_pages_published` (`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Static CMS pages';

-- ============================================================================
-- 17. Blog Posts - Blog articles
-- ============================================================================
CREATE TABLE `blog_posts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL COMMENT 'Blog post title',
    `slug` VARCHAR(255) NOT NULL COMMENT 'URL-friendly slug (unique)',
    `content` LONGTEXT DEFAULT NULL COMMENT 'Blog post body (HTML)',
    `image` VARCHAR(500) DEFAULT NULL COMMENT 'Path to featured image',
    `author_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to users.id',
    `is_published` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=published, 0=draft',
    `published_at` DATETIME DEFAULT NULL COMMENT 'Scheduled publish timestamp',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_blog_posts_slug` (`slug`),
    INDEX `idx_blog_posts_author` (`author_id`),
    INDEX `idx_blog_posts_published` (`is_published`, `published_at`),
    CONSTRAINT `fk_blog_posts_author_id`
        FOREIGN KEY (`author_id`) REFERENCES `users` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Blog posts';

-- ============================================================================
-- 18. Contacts - Contact form submissions
-- ============================================================================
CREATE TABLE `contacts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Sender name',
    `email` VARCHAR(255) NOT NULL COMMENT 'Sender email',
    `phone` VARCHAR(30) DEFAULT NULL COMMENT 'Sender phone',
    `subject` VARCHAR(255) DEFAULT NULL COMMENT 'Message subject',
    `message` TEXT NOT NULL COMMENT 'Message body',
    `is_read` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=read by admin',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_contacts_is_read` (`is_read`),
    INDEX `idx_contacts_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contact form submissions';

-- ============================================================================
-- 19. Franchisees - Franchise inquiry/applications
-- ============================================================================
CREATE TABLE `franchisees` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Applicant name',
    `email` VARCHAR(255) NOT NULL COMMENT 'Applicant email',
    `phone` VARCHAR(30) NOT NULL COMMENT 'Applicant phone',
    `city` VARCHAR(100) NOT NULL COMMENT 'Preferred city',
    `state` VARCHAR(100) NOT NULL COMMENT 'Preferred state',
    `investment_range` VARCHAR(100) DEFAULT NULL COMMENT 'Budget/investment range',
    `message` TEXT DEFAULT NULL COMMENT 'Additional message',
    `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending' COMMENT 'Application status',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_franchisees_status` (`status`),
    INDEX `idx_franchisees_city_state` (`city`, `state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Franchise inquiries and applications';

-- ============================================================================
-- Default Data: Admin User
-- ============================================================================
-- Password: Admin@123 (hashed with bcrypt via PHP password_hash)
-- Note: The $2y$ prefix is PHP's bcrypt variant; $2b$ is also compatible.
-- To regenerate: php -r "echo password_hash('Admin@123', PASSWORD_BCRYPT);"
INSERT INTO `users` (`name`, `email`, `password`, `role`, `phone`, `is_active`)
VALUES (
    'Admin',
    'admin@gmind.in',
    '$2y$12$hcpOH/.l6DuXX51dleTbYuM6y97WwTifJt0lrNdB/ctnD.S5EmPue',
    'admin',
    NULL,
    1
);

-- ============================================================================
-- Default Data: Essential Settings
-- ============================================================================
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('site_name', 'Gmind Institute'),
    ('site_tagline', 'Empowering Futures Through Technology Education'),
    ('site_email', 'info@gmind.in'),
    ('site_phone', ''),
    ('site_address', ''),
    ('footer_text', '© 2025 Gmind Institute. All Rights Reserved.'),
    ('social_facebook', ''),
    ('social_instagram', ''),
    ('social_youtube', ''),
    ('social_linkedin', ''),
    ('social_twitter', ''),
    ('contact_email', 'info@gmind.in'),
    ('maintenance_mode', '0');

-- ============================================================================
-- Schema Version Tracking
-- ============================================================================
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('schema_version', '1.0.0'),
    ('schema_created_at', NOW());

-- ============================================================================
-- End of Schema
-- ============================================================================
