-- =============================================================
-- AstroSutraa Academy - MySQL Schema (MVP)
-- =============================================================
-- Run this file in phpMyAdmin or via: mysql -u root astrosutraa_academy < schema.sql
-- =============================================================

CREATE DATABASE IF NOT EXISTS `astrosutraa_academy`
    DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `astrosutraa_academy`;

SET FOREIGN_KEY_CHECKS = 0;

-- -------------------------------------------------------------
-- Users (all roles share one table)
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `email` VARCHAR(190) NOT NULL UNIQUE,
    `phone` VARCHAR(20) DEFAULT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('super_admin','admin','trainer','student','support') NOT NULL DEFAULT 'student',
    `status` ENUM('active','inactive','pending','suspended') NOT NULL DEFAULT 'active',
    `profile_image` VARCHAR(255) DEFAULT NULL,
    `bio` TEXT DEFAULT NULL,
    `email_verified_at` DATETIME DEFAULT NULL,
    `phone_verified_at` DATETIME DEFAULT NULL,
    `remember_token` VARCHAR(100) DEFAULT NULL,
    `last_login_at` DATETIME DEFAULT NULL,
    `last_login_ip` VARCHAR(45) DEFAULT NULL,
    `deleted_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_users_role` (`role`),
    KEY `idx_users_status` (`status`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Trainers (extends users)
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `trainers`;
CREATE TABLE `trainers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL UNIQUE,
    `headline` VARCHAR(255) DEFAULT NULL,
    `expertise` TEXT DEFAULT NULL,
    `specializations` TEXT DEFAULT NULL,
    `experience_years` INT DEFAULT 0,
    `languages` VARCHAR(255) DEFAULT NULL,
    `website` VARCHAR(255) DEFAULT NULL,
    `social_links` TEXT DEFAULT NULL,
    `bank_name` VARCHAR(100) DEFAULT NULL,
    `account_no` VARCHAR(50) DEFAULT NULL,
    `ifsc` VARCHAR(20) DEFAULT NULL,
    `pan` VARCHAR(20) DEFAULT NULL,
    `gst` VARCHAR(20) DEFAULT NULL,
    `default_revenue_share` DECIMAL(5,2) DEFAULT 50.00,
    `approval_status` ENUM('pending','approved','rejected') DEFAULT 'pending',
    `approved_at` DATETIME DEFAULT NULL,
    `approved_by` INT UNSIGNED DEFAULT NULL,
    `rejection_reason` TEXT DEFAULT NULL,
    `deleted_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_trainers_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Categories
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `parent_id` INT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(120) NOT NULL,
    `slug` VARCHAR(150) NOT NULL UNIQUE,
    `description` TEXT DEFAULT NULL,
    `icon` VARCHAR(255) DEFAULT NULL,
    `image` VARCHAR(255) DEFAULT NULL,
    `sort_order` INT DEFAULT 0,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_cat_parent` (`parent_id`),
    KEY `idx_cat_status` (`status`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Courses
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `trainer_id` INT UNSIGNED DEFAULT NULL,
    `created_by` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `short_description` TEXT DEFAULT NULL,
    `full_description` LONGTEXT DEFAULT NULL,
    `category_id` INT UNSIGNED DEFAULT NULL,
    `level` ENUM('beginner','intermediate','advanced') DEFAULT 'beginner',
    `language` VARCHAR(50) DEFAULT 'English',
    `course_type` ENUM('recorded','live_batch','hybrid','webinar_funnel','certification','mentorship','bundle','free_lead_magnet') DEFAULT 'recorded',
    `is_free` TINYINT(1) DEFAULT 0,
    `duration_text` VARCHAR(100) DEFAULT NULL,
    `duration_hours` DECIMAL(7,2) DEFAULT 0,
    `thumbnail` VARCHAR(255) DEFAULT NULL,
    `intro_video_url` VARCHAR(500) DEFAULT NULL,
    `regular_price` DECIMAL(10,2) DEFAULT 0,
    `sale_price` DECIMAL(10,2) DEFAULT 0,
    `payment_link` VARCHAR(500) DEFAULT NULL,
    `access_validity_days` INT DEFAULT 365,
    `certificate_enabled` TINYINT(1) DEFAULT 0,
    `status` ENUM('draft','review','published','paused','archived') DEFAULT 'draft',
    `revenue_share_override` DECIMAL(5,2) DEFAULT NULL,
    `seo_title` VARCHAR(255) DEFAULT NULL,
    `seo_description` TEXT DEFAULT NULL,
    `og_image` VARCHAR(255) DEFAULT NULL,
    `total_enrolled` INT DEFAULT 0,
    `rating_avg` DECIMAL(3,2) DEFAULT 0,
    `rating_count` INT DEFAULT 0,
    `is_featured` TINYINT(1) DEFAULT 0,
    `published_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_courses_trainer` (`trainer_id`),
    KEY `idx_courses_category` (`category_id`),
    KEY `idx_courses_status` (`status`),
    KEY `idx_courses_type` (`course_type`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Course Modules
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `course_modules`;
CREATE TABLE `course_modules` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `sort_order` INT DEFAULT 0,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_modules_course` (`course_id`),
    CONSTRAINT `fk_modules_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Course Lessons
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `course_lessons`;
CREATE TABLE `course_lessons` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `module_id` INT UNSIGNED NOT NULL,
    `course_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `type` ENUM('video','pdf','text','quiz','assignment','live_recording') DEFAULT 'video',
    `content` LONGTEXT DEFAULT NULL,
    `video_url` VARCHAR(500) DEFAULT NULL,
    `is_drm_protected` TINYINT(1) DEFAULT 0,
    `resource_file` VARCHAR(500) DEFAULT NULL,
    `duration_minutes` INT DEFAULT 0,
    `is_free_preview` TINYINT(1) DEFAULT 0,
    `drip_days` INT DEFAULT 0,
    `sort_order` INT DEFAULT 0,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `approval_status` ENUM('pending','approved','rejected') DEFAULT 'pending',
    `rejection_reason` TEXT DEFAULT NULL,
    `approved_at` DATETIME DEFAULT NULL,
    `approved_by` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_lessons_module` (`module_id`),
    KEY `idx_lessons_course` (`course_id`),
    KEY `idx_lessons_approval` (`approval_status`),
    CONSTRAINT `fk_lessons_module` FOREIGN KEY (`module_id`) REFERENCES `course_modules`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lessons_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Enrollments
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `enrollments`;
CREATE TABLE `enrollments` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `student_id` INT UNSIGNED NOT NULL,
    `course_id` INT UNSIGNED NOT NULL,
    `status` ENUM('active','expired','revoked','pending') DEFAULT 'active',
    `access_start_date` DATE DEFAULT NULL,
    `access_expiry_date` DATE DEFAULT NULL,
    `payment_id` INT UNSIGNED DEFAULT NULL,
    `source` ENUM('purchase','admin','free','webinar','scholarship','bundle') DEFAULT 'purchase',
    `admin_note` TEXT DEFAULT NULL,
    `created_by` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_student_course` (`student_id`,`course_id`),
    KEY `idx_enrol_student` (`student_id`),
    KEY `idx_enrol_course` (`course_id`),
    KEY `idx_enrol_status` (`status`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Payments
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `course_id` INT UNSIGNED DEFAULT NULL,
    `webinar_id` INT UNSIGNED DEFAULT NULL,
    `amount_gross` DECIMAL(10,2) DEFAULT 0,
    `discount` DECIMAL(10,2) DEFAULT 0,
    `coupon_id` INT UNSIGNED DEFAULT NULL,
    `tax` DECIMAL(10,2) DEFAULT 0,
    `gateway_fee` DECIMAL(10,2) DEFAULT 0,
    `amount_net` DECIMAL(10,2) DEFAULT 0,
    `currency` VARCHAR(10) DEFAULT 'INR',
    `gateway` ENUM('razorpay_link','razorpay','cashfree','payu','stripe','manual','upi') DEFAULT 'razorpay_link',
    `gateway_payment_id` VARCHAR(190) DEFAULT NULL,
    `gateway_ref` VARCHAR(190) DEFAULT NULL,
    `payment_link` VARCHAR(500) DEFAULT NULL,
    `status` ENUM('initiated','pending','success','failed','refunded') DEFAULT 'initiated',
    `payment_method` VARCHAR(50) DEFAULT NULL,
    `payer_email` VARCHAR(190) DEFAULT NULL,
    `payer_phone` VARCHAR(20) DEFAULT NULL,
    `verified_by` INT UNSIGNED DEFAULT NULL,
    `verified_at` DATETIME DEFAULT NULL,
    `refunded_at` DATETIME DEFAULT NULL,
    `meta_data` LONGTEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_pay_user` (`user_id`),
    KEY `idx_pay_course` (`course_id`),
    KEY `idx_pay_status` (`status`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Trainer Earnings
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `trainer_earnings`;
CREATE TABLE `trainer_earnings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `trainer_id` INT UNSIGNED NOT NULL,
    `payment_id` INT UNSIGNED NOT NULL,
    `course_id` INT UNSIGNED NOT NULL,
    `gross_amount` DECIMAL(10,2) DEFAULT 0,
    `discount` DECIMAL(10,2) DEFAULT 0,
    `gateway_fee` DECIMAL(10,2) DEFAULT 0,
    `net_revenue` DECIMAL(10,2) DEFAULT 0,
    `share_percent` DECIMAL(5,2) DEFAULT 0,
    `trainer_earning` DECIMAL(10,2) DEFAULT 0,
    `platform_earning` DECIMAL(10,2) DEFAULT 0,
    `status` ENUM('calculated','pending_payout','included_in_payout','paid','reversed') DEFAULT 'calculated',
    `payout_id` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_earn_trainer` (`trainer_id`),
    KEY `idx_earn_status` (`status`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Trainer Payouts
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `trainer_payouts`;
CREATE TABLE `trainer_payouts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `trainer_id` INT UNSIGNED NOT NULL,
    `period_start` DATE DEFAULT NULL,
    `period_end` DATE DEFAULT NULL,
    `total_amount` DECIMAL(12,2) DEFAULT 0,
    `deductions` DECIMAL(12,2) DEFAULT 0,
    `net_payable` DECIMAL(12,2) DEFAULT 0,
    `status` ENUM('pending','approved','paid','hold','cancelled') DEFAULT 'pending',
    `payment_ref` VARCHAR(190) DEFAULT NULL,
    `payment_method` VARCHAR(50) DEFAULT NULL,
    `paid_at` DATETIME DEFAULT NULL,
    `approved_by` INT UNSIGNED DEFAULT NULL,
    `paid_by` INT UNSIGNED DEFAULT NULL,
    `note` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_payout_trainer` (`trainer_id`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Webinars
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `webinars`;
CREATE TABLE `webinars` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `trainer_id` INT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `description` LONGTEXT DEFAULT NULL,
    `provider` ENUM('webinar_gg','zoom','google_meet','youtube_live','custom') DEFAULT 'zoom',
    `external_join_link` VARCHAR(500) DEFAULT NULL,
    `scheduled_at` DATETIME DEFAULT NULL,
    `duration_minutes` INT DEFAULT 60,
    `is_free` TINYINT(1) DEFAULT 1,
    `price` DECIMAL(10,2) DEFAULT 0,
    `payment_link` VARCHAR(500) DEFAULT NULL,
    `max_seats` INT DEFAULT 0,
    `related_course_id` INT UNSIGNED DEFAULT NULL,
    `registration_status` ENUM('open','closed') DEFAULT 'open',
    `recording_url` VARCHAR(500) DEFAULT NULL,
    `follow_up_offer` TEXT DEFAULT NULL,
    `thumbnail` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('upcoming','live','completed','cancelled') DEFAULT 'upcoming',
    `share_token` VARCHAR(64) DEFAULT NULL,
    `approval_status` ENUM('pending','approved','rejected') DEFAULT 'pending',
    `rejection_reason` TEXT DEFAULT NULL,
    `approved_at` DATETIME DEFAULT NULL,
    `approved_by` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_share_token` (`share_token`),
    KEY `idx_web_trainer` (`trainer_id`),
    KEY `idx_web_status` (`status`),
    KEY `idx_web_approval` (`approval_status`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `webinar_registrations`;
CREATE TABLE `webinar_registrations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `webinar_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `status` ENUM('registered','attended','no_show','cancelled') DEFAULT 'registered',
    `payment_id` INT UNSIGNED DEFAULT NULL,
    `joined_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_webinar_user` (`webinar_id`,`user_id`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Lesson Progress
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `lesson_progress`;
CREATE TABLE `lesson_progress` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `enrollment_id` INT UNSIGNED NOT NULL,
    `lesson_id` INT UNSIGNED NOT NULL,
    `status` ENUM('not_started','in_progress','completed') DEFAULT 'not_started',
    `watch_time_seconds` INT DEFAULT 0,
    `completed_at` DATETIME DEFAULT NULL,
    `last_accessed_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_enrol_lesson` (`enrollment_id`,`lesson_id`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Reviews
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `course_reviews`;
CREATE TABLE `course_reviews` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `course_id` INT UNSIGNED NOT NULL,
    `student_id` INT UNSIGNED NOT NULL,
    `rating` TINYINT NOT NULL,
    `review_text` TEXT DEFAULT NULL,
    `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_review_course` (`course_id`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Coupons
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `coupons`;
CREATE TABLE `coupons` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(60) NOT NULL UNIQUE,
    `type` ENUM('percentage','fixed') DEFAULT 'percentage',
    `value` DECIMAL(10,2) DEFAULT 0,
    `max_uses` INT DEFAULT 0,
    `used_count` INT DEFAULT 0,
    `applicable_to` ENUM('all','course','category','trainer') DEFAULT 'all',
    `applicable_ids` TEXT DEFAULT NULL,
    `min_amount` DECIMAL(10,2) DEFAULT 0,
    `valid_from` DATETIME DEFAULT NULL,
    `valid_until` DATETIME DEFAULT NULL,
    `status` ENUM('active','inactive','expired') DEFAULT 'active',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Certificates
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `certificates`;
CREATE TABLE `certificates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `student_id` INT UNSIGNED NOT NULL,
    `course_id` INT UNSIGNED NOT NULL,
    `certificate_number` VARCHAR(60) NOT NULL UNIQUE,
    `issued_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `pdf_path` VARCHAR(500) DEFAULT NULL,
    `qr_code` VARCHAR(500) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Support Tickets
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `support_tickets`;
CREATE TABLE `support_tickets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ticket_number` VARCHAR(40) NOT NULL UNIQUE,
    `user_id` INT UNSIGNED NOT NULL,
    `category` ENUM('payment','course_access','technical','certificate','trainer_query','refund','other') DEFAULT 'other',
    `subject` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `status` ENUM('open','pending','resolved','closed') DEFAULT 'open',
    `priority` ENUM('low','medium','high','urgent') DEFAULT 'medium',
    `assigned_to` INT UNSIGNED DEFAULT NULL,
    `related_course_id` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_ticket_user` (`user_id`),
    KEY `idx_ticket_status` (`status`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `support_ticket_replies`;
CREATE TABLE `support_ticket_replies` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ticket_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `message` TEXT NOT NULL,
    `is_internal_note` TINYINT(1) DEFAULT 0,
    `attachment` VARCHAR(500) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Landing Pages
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `landing_pages`;
CREATE TABLE `landing_pages` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `page_type` ENUM('course','webinar','offer','trainer','bundle','general') DEFAULT 'course',
    `related_id` INT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `template` VARCHAR(100) DEFAULT 'default',
    `sections` LONGTEXT DEFAULT NULL,
    `seo_title` VARCHAR(255) DEFAULT NULL,
    `seo_description` TEXT DEFAULT NULL,
    `og_image` VARCHAR(500) DEFAULT NULL,
    `meta_pixel_id` VARCHAR(100) DEFAULT NULL,
    `ga4_id` VARCHAR(100) DEFAULT NULL,
    `google_ads_id` VARCHAR(100) DEFAULT NULL,
    `custom_scripts` LONGTEXT DEFAULT NULL,
    `status` ENUM('draft','published') DEFAULT 'draft',
    `created_by` INT UNSIGNED DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Notifications
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `type` VARCHAR(60) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `link` VARCHAR(500) DEFAULT NULL,
    `is_read` TINYINT(1) DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_notif_user` (`user_id`,`is_read`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- User Sessions (content protection)
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `user_sessions`;
CREATE TABLE `user_sessions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `session_token` VARCHAR(128) NOT NULL,
    `device_fingerprint` VARCHAR(190) DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `last_active_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_sess_user` (`user_id`)
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Lesson Notes
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `lesson_notes`;
CREATE TABLE `lesson_notes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `lesson_id` INT UNSIGNED NOT NULL,
    `enrollment_id` INT UNSIGNED NOT NULL,
    `note_text` TEXT NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Student Questions (ask trainer)
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `student_questions`;
CREATE TABLE `student_questions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `student_id` INT UNSIGNED NOT NULL,
    `course_id` INT UNSIGNED NOT NULL,
    `lesson_id` INT UNSIGNED DEFAULT NULL,
    `trainer_id` INT UNSIGNED DEFAULT NULL,
    `question` TEXT NOT NULL,
    `answer` TEXT DEFAULT NULL,
    `status` ENUM('open','answered','closed') DEFAULT 'open',
    `answered_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- -------------------------------------------------------------
-- Settings
-- -------------------------------------------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `group_name` VARCHAR(60) NOT NULL,
    `key_name` VARCHAR(100) NOT NULL,
    `value` LONGTEXT DEFAULT NULL,
    `type` VARCHAR(30) DEFAULT 'string',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_setting` (`group_name`,`key_name`)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================================
-- Seed Data
-- =============================================================

-- Super admin (password: Admin@123)
INSERT INTO `users` (`name`,`email`,`phone`,`password_hash`,`role`,`status`,`email_verified_at`) VALUES
('AstroSutraa Admin','admin@astrosutraa.com','9999999999','$2y$10$8YzN8XaHJ2eVbW3LzKZ.M.7BbF7Z7sJrCEuYbN8sV5JZqv2gZ2gKa','super_admin','active',NOW()),
('Sample Trainer','trainer@astrosutraa.com','9876543210','$2y$10$8YzN8XaHJ2eVbW3LzKZ.M.7BbF7Z7sJrCEuYbN8sV5JZqv2gZ2gKa','trainer','active',NOW()),
('Demo Student','student@astrosutraa.com','9876500000','$2y$10$8YzN8XaHJ2eVbW3LzKZ.M.7BbF7Z7sJrCEuYbN8sV5JZqv2gZ2gKa','student','active',NOW());

-- Trainer profile
INSERT INTO `trainers` (`user_id`,`headline`,`expertise`,`experience_years`,`languages`,`default_revenue_share`,`approval_status`,`approved_at`) VALUES
(2,'Vedic Astrologer & Tarot Reader','Vedic Astrology, Tarot, Numerology',12,'Hindi, English',50.00,'approved',NOW());

-- Categories
INSERT INTO `categories` (`name`,`slug`,`description`,`icon`,`sort_order`,`status`) VALUES
('Astrology','astrology','Vedic and Western astrology courses','star',1,'active'),
('Tarot','tarot','Tarot card reading and interpretation','layers',2,'active'),
('Numerology','numerology','Chaldean and Pythagorean numerology','hash',3,'active'),
('Vastu','vastu','Vastu Shastra and space healing','home',4,'active'),
('Reiki & Healing','reiki-healing','Reiki, pranic healing and energy work','heart',5,'active'),
('Manifestation','manifestation','Law of attraction and manifestation','sparkles',6,'active'),
('Mantra & Sadhana','mantra-sadhana','Mantra chanting and spiritual practice','om',7,'active'),
('Palmistry','palmistry','Hand reading and palm analysis','hand',8,'active');

-- Sample course
INSERT INTO `courses` (`trainer_id`,`created_by`,`title`,`slug`,`short_description`,`full_description`,`category_id`,`level`,`language`,`course_type`,`duration_text`,`duration_hours`,`regular_price`,`sale_price`,`access_validity_days`,`certificate_enabled`,`status`,`is_featured`,`published_at`) VALUES
(1,2,'Complete Vedic Astrology Foundation','complete-vedic-astrology-foundation','Master the fundamentals of Vedic Astrology in 12 weeks with live mentorship.','Learn the complete foundation of Vedic Astrology — planets, houses, signs, dashas, transits, and chart interpretation. This program is designed for absolute beginners and includes practice charts, case studies, and live doubt-clearing sessions.',1,'beginner','Hindi','recorded','12 weeks',24.00,9999.00,4999.00,365,1,'published',1,NOW()),
(1,2,'Tarot Mastery: Beginner to Reader','tarot-mastery-beginner-to-reader','Become a confident Tarot reader with the 78-card deck and live practice.','Comprehensive Tarot course covering Major Arcana, Minor Arcana, spreads, reversed cards, and ethical reading practices.',2,'beginner','English','hybrid','8 weeks',16.00,7999.00,3999.00,365,1,'published',1,NOW()),
(1,2,'Chaldean Numerology Practitioner','chaldean-numerology-practitioner','Decode names and dates using the ancient Chaldean system.','Step-by-step Chaldean numerology — name analysis, date of birth interpretation, and name correction practice.',3,'intermediate','Hindi','recorded','6 weeks',12.00,5999.00,2999.00,365,1,'published',0,NOW());

-- Default settings
INSERT INTO `settings` (`group_name`,`key_name`,`value`,`type`) VALUES
('site','name','AstroSutraa Academy','string'),
('site','tagline','Learn Mystic Sciences from Verified Trainers','string'),
('site','email','support@astrosutraa.com','string'),
('site','phone','+91 9999 99 9999','string'),
('site','currency','INR','string'),
('site','currency_symbol','₹','string'),
('site','default_revenue_share','50','number'),
('payment','razorpay_key_id','','string'),
('payment','razorpay_key_secret','','string'),
('payment','razorpay_enabled','0','boolean'),
('analytics','meta_pixel_id','','string'),
('analytics','ga4_id','','string'),
('analytics','google_ads_id','','string');