-- ============================================================
-- GERMAN MANTRA — Complete MySQL Database
-- Schema + Sample Data (INSERT statements)
-- Compatible: MySQL 8.0+ / MariaDB 10.6+
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
SET time_zone = '+05:30';

-- ============================================================
-- CREATE DATABASE
-- ============================================================
CREATE DATABASE IF NOT EXISTS `german_mantra`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `german_mantra`;

-- ============================================================
-- TABLE: users
-- ============================================================
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id`              CHAR(36)        NOT NULL,
  `name`            VARCHAR(150)    NOT NULL,
  `email`           VARCHAR(200)    UNIQUE,
  `mobile`          VARCHAR(20)     UNIQUE,
  `password`        VARCHAR(255)    DEFAULT NULL COMMENT 'bcrypt hashed',
  `google_id`       VARCHAR(100)    DEFAULT NULL,
  `picture`         TEXT            DEFAULT NULL,
  `role`            ENUM('student','admin') NOT NULL DEFAULT 'student',
  `level`           VARCHAR(30)     DEFAULT 'Beginner',
  `goal`            TEXT            DEFAULT NULL,
  `is_verified`     TINYINT(1)      NOT NULL DEFAULT 1,
  `is_active`       TINYINT(1)      NOT NULL DEFAULT 1,
  `last_login`      DATETIME        DEFAULT NULL,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_email`    (`email`),
  INDEX `idx_mobile`   (`mobile`),
  INDEX `idx_role`     (`role`),
  INDEX `idx_is_active`(`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: courses
-- ============================================================
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `id`              CHAR(36)        NOT NULL,
  `title`           VARCHAR(200)    NOT NULL,
  `slug`            VARCHAR(200)    NOT NULL UNIQUE,
  `level`           VARCHAR(30)     NOT NULL,
  `type`            VARCHAR(50)     NOT NULL DEFAULT 'live+recorded',
  `price`           DECIMAL(10,2)   NOT NULL,
  `original_price`  DECIMAL(10,2)   NOT NULL,
  `duration`        VARCHAR(50)     DEFAULT NULL,
  `total_lessons`   INT             NOT NULL DEFAULT 0,
  `rating`          DECIMAL(2,1)    NOT NULL DEFAULT 0.0,
  `review_count`    INT             NOT NULL DEFAULT 0,
  `enrolled_count`  INT             NOT NULL DEFAULT 0,
  `instructor`      VARCHAR(150)    NOT NULL DEFAULT 'Kritika Rai',
  `description`     TEXT            DEFAULT NULL,
  `thumbnail`       TEXT            DEFAULT NULL,
  `is_published`    TINYINT(1)      NOT NULL DEFAULT 1,
  `is_featured`     TINYINT(1)      NOT NULL DEFAULT 0,
  `has_certificate` TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_slug`       (`slug`),
  INDEX `idx_level`             (`level`),
  INDEX `idx_is_published`      (`is_published`),
  INDEX `idx_is_featured`       (`is_featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: enrollments
-- ============================================================
DROP TABLE IF EXISTS `enrollments`;
CREATE TABLE `enrollments` (
  `id`                  CHAR(36)        NOT NULL,
  `user_id`             CHAR(36)        NOT NULL,
  `course_id`           CHAR(36)        NOT NULL,
  `payment_id`          CHAR(36)        DEFAULT NULL,
  `progress`            INT             NOT NULL DEFAULT 0 COMMENT 'percentage 0-100',
  `access_type`         VARCHAR(30)     NOT NULL DEFAULT 'lifetime',
  `expires_at`          DATETIME        DEFAULT NULL,
  `completed_at`        DATETIME        DEFAULT NULL,
  `created_at`          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_user_course` (`user_id`, `course_id`),
  INDEX `idx_user_id`   (`user_id`),
  INDEX `idx_course_id` (`course_id`),
  CONSTRAINT `fk_enroll_user`   FOREIGN KEY (`user_id`)   REFERENCES `users`(`id`)   ON DELETE CASCADE,
  CONSTRAINT `fk_enroll_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: lessons
-- ============================================================
DROP TABLE IF EXISTS `lessons`;
CREATE TABLE `lessons` (
  `id`              CHAR(36)        NOT NULL,
  `course_id`       CHAR(36)        NOT NULL,
  `title`           VARCHAR(255)    NOT NULL,
  `description`     TEXT            DEFAULT NULL,
  `video_url`       TEXT            DEFAULT NULL,
  `duration_min`    INT             DEFAULT NULL COMMENT 'duration in minutes',
  `sort_order`      INT             NOT NULL DEFAULT 0,
  `is_free`         TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '1=demo lesson',
  `is_published`    TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_course_id`   (`course_id`),
  INDEX `idx_sort_order`  (`sort_order`),
  CONSTRAINT `fk_lesson_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: lesson_progress
-- ============================================================
DROP TABLE IF EXISTS `lesson_progress`;
CREATE TABLE `lesson_progress` (
  `id`              CHAR(36)        NOT NULL,
  `enrollment_id`   CHAR(36)        NOT NULL,
  `user_id`         CHAR(36)        NOT NULL,
  `lesson_id`       CHAR(36)        NOT NULL,
  `course_id`       CHAR(36)        NOT NULL,
  `is_completed`    TINYINT(1)      NOT NULL DEFAULT 0,
  `watch_time_sec`  INT             NOT NULL DEFAULT 0,
  `completed_at`    DATETIME        DEFAULT NULL,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_user_lesson` (`user_id`, `lesson_id`),
  INDEX `idx_enrollment_id`   (`enrollment_id`),
  INDEX `idx_user_id`         (`user_id`),
  INDEX `idx_lesson_id`       (`lesson_id`),
  CONSTRAINT `fk_lp_enrollment` FOREIGN KEY (`enrollment_id`) REFERENCES `enrollments`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_lp_user`       FOREIGN KEY (`user_id`)       REFERENCES `users`(`id`)       ON DELETE CASCADE,
  CONSTRAINT `fk_lp_lesson`     FOREIGN KEY (`lesson_id`)     REFERENCES `lessons`(`id`)     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: live_classes
-- ============================================================
DROP TABLE IF EXISTS `live_classes`;
CREATE TABLE `live_classes` (
  `id`              CHAR(36)        NOT NULL,
  `course_id`       CHAR(36)        NOT NULL,
  `title`           VARCHAR(255)    NOT NULL,
  `description`     TEXT            DEFAULT NULL,
  `class_date`      DATE            NOT NULL,
  `start_time`      TIME            NOT NULL,
  `end_time`        TIME            NOT NULL,
  `instructor`      VARCHAR(150)    NOT NULL DEFAULT 'Kritika Rai',
  `meeting_link`    TEXT            NOT NULL,
  `recording_url`   TEXT            DEFAULT NULL,
  `status`          ENUM('scheduled','live','completed','cancelled') NOT NULL DEFAULT 'scheduled',
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_course_id`   (`course_id`),
  INDEX `idx_class_date`  (`class_date`),
  INDEX `idx_status`      (`status`),
  CONSTRAINT `fk_lc_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: payments
-- ============================================================
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id`                    CHAR(36)        NOT NULL,
  `user_id`               CHAR(36)        NOT NULL,
  `course_id`             CHAR(36)        NOT NULL,
  `course_name`           VARCHAR(200)    NOT NULL,
  `amount`                DECIMAL(10,2)   NOT NULL,
  `discount`              DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `final_amount`          DECIMAL(10,2)   NOT NULL,
  `coupon_code`           VARCHAR(50)     DEFAULT NULL,
  `razorpay_order_id`     VARCHAR(100)    DEFAULT NULL,
  `razorpay_payment_id`   VARCHAR(100)    DEFAULT NULL,
  `razorpay_signature`    VARCHAR(255)    DEFAULT NULL,
  `payment_method`        VARCHAR(50)     DEFAULT NULL COMMENT 'UPI/Card/NetBanking',
  `status`                ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  `paid_at`               DATETIME        DEFAULT NULL,
  `refunded_at`           DATETIME        DEFAULT NULL,
  `created_at`            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_id`             (`user_id`),
  INDEX `idx_course_id`           (`course_id`),
  INDEX `idx_status`              (`status`),
  INDEX `idx_razorpay_order_id`   (`razorpay_order_id`),
  INDEX `idx_paid_at`             (`paid_at`),
  CONSTRAINT `fk_payment_user`    FOREIGN KEY (`user_id`)   REFERENCES `users`(`id`)   ON DELETE RESTRICT,
  CONSTRAINT `fk_payment_course`  FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: coupons
-- ============================================================
DROP TABLE IF EXISTS `coupons`;
CREATE TABLE `coupons` (
  `id`              CHAR(36)        NOT NULL,
  `code`            VARCHAR(50)     NOT NULL UNIQUE,
  `type`            ENUM('percentage','fixed') NOT NULL,
  `value`           DECIMAL(10,2)   NOT NULL,
  `max_discount`    DECIMAL(10,2)   DEFAULT NULL,
  `usage_limit`     INT             DEFAULT NULL,
  `used_count`      INT             NOT NULL DEFAULT 0,
  `course_scope`    VARCHAR(50)     NOT NULL DEFAULT 'all' COMMENT 'all or course_id',
  `expires_at`      DATE            DEFAULT NULL,
  `is_active`       TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_code`     (`code`),
  INDEX `idx_is_active`       (`is_active`),
  INDEX `idx_expires_at`      (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: study_materials
-- ============================================================
DROP TABLE IF EXISTS `study_materials`;
CREATE TABLE `study_materials` (
  `id`              CHAR(36)        NOT NULL,
  `course_id`       CHAR(36)        NOT NULL,
  `title`           VARCHAR(255)    NOT NULL,
  `file_type`       VARCHAR(20)     NOT NULL COMMENT 'PDF/MP3/Video/Zip',
  `file_url`        TEXT            NOT NULL,
  `file_size_kb`    INT             DEFAULT NULL,
  `sort_order`      INT             NOT NULL DEFAULT 0,
  `is_published`    TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_course_id` (`course_id`),
  CONSTRAINT `fk_material_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: tests
-- ============================================================
DROP TABLE IF EXISTS `tests`;
CREATE TABLE `tests` (
  `id`              CHAR(36)        NOT NULL,
  `course_id`       CHAR(36)        NOT NULL,
  `title`           VARCHAR(255)    NOT NULL,
  `description`     TEXT            DEFAULT NULL,
  `duration_min`    INT             NOT NULL DEFAULT 30,
  `total_questions` INT             NOT NULL DEFAULT 10,
  `passing_percent` INT             NOT NULL DEFAULT 60,
  `is_published`    TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_course_id` (`course_id`),
  CONSTRAINT `fk_test_course` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: questions
-- ============================================================
DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `id`              CHAR(36)        NOT NULL,
  `test_id`         CHAR(36)        NOT NULL,
  `question_text`   TEXT            NOT NULL,
  `option_a`        VARCHAR(500)    NOT NULL,
  `option_b`        VARCHAR(500)    NOT NULL,
  `option_c`        VARCHAR(500)    DEFAULT NULL,
  `option_d`        VARCHAR(500)    DEFAULT NULL,
  `correct_option`  CHAR(1)         NOT NULL COMMENT 'A/B/C/D',
  `explanation`     TEXT            DEFAULT NULL,
  `sort_order`      INT             NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_test_id` (`test_id`),
  CONSTRAINT `fk_question_test` FOREIGN KEY (`test_id`) REFERENCES `tests`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: test_attempts
-- ============================================================
DROP TABLE IF EXISTS `test_attempts`;
CREATE TABLE `test_attempts` (
  `id`              CHAR(36)        NOT NULL,
  `test_id`         CHAR(36)        NOT NULL,
  `user_id`         CHAR(36)        NOT NULL,
  `score_percent`   INT             NOT NULL DEFAULT 0,
  `correct_answers` INT             NOT NULL DEFAULT 0,
  `total_questions` INT             NOT NULL DEFAULT 0,
  `time_taken_sec`  INT             DEFAULT NULL,
  `is_passed`       TINYINT(1)      NOT NULL DEFAULT 0,
  `completed_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_test_id` (`test_id`),
  INDEX `idx_user_id` (`user_id`),
  CONSTRAINT `fk_attempt_test` FOREIGN KEY (`test_id`) REFERENCES `tests`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_attempt_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: certificates
-- ============================================================
DROP TABLE IF EXISTS `certificates`;
CREATE TABLE `certificates` (
  `id`              CHAR(36)        NOT NULL,
  `user_id`         CHAR(36)        NOT NULL,
  `course_id`       CHAR(36)        NOT NULL,
  `enrollment_id`   CHAR(36)        NOT NULL,
  `certificate_no`  VARCHAR(50)     NOT NULL UNIQUE,
  `issued_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `pdf_url`         TEXT            DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_cert_no`      (`certificate_no`),
  UNIQUE INDEX `idx_user_course`  (`user_id`, `course_id`),
  INDEX `idx_user_id`             (`user_id`),
  CONSTRAINT `fk_cert_user`       FOREIGN KEY (`user_id`)       REFERENCES `users`(`id`)       ON DELETE CASCADE,
  CONSTRAINT `fk_cert_course`     FOREIGN KEY (`course_id`)     REFERENCES `courses`(`id`)     ON DELETE RESTRICT,
  CONSTRAINT `fk_cert_enrollment` FOREIGN KEY (`enrollment_id`) REFERENCES `enrollments`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: notifications
-- ============================================================
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id`              CHAR(36)        NOT NULL,
  `title`           VARCHAR(255)    NOT NULL,
  `body`            TEXT            NOT NULL,
  `audience`        VARCHAR(30)     NOT NULL DEFAULT 'all' COMMENT 'all/course/individual',
  `course_id`       CHAR(36)        DEFAULT NULL,
  `recipient_count` INT             NOT NULL DEFAULT 0,
  `sent_by`         CHAR(36)        NOT NULL COMMENT 'admin user_id',
  `sent_at`         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: testimonials
-- ============================================================
DROP TABLE IF EXISTS `testimonials`;
CREATE TABLE `testimonials` (
  `id`              CHAR(36)        NOT NULL,
  `user_id`         CHAR(36)        DEFAULT NULL,
  `name`            VARCHAR(150)    NOT NULL,
  `course`          VARCHAR(100)    DEFAULT NULL,
  `rating`          TINYINT         NOT NULL DEFAULT 5,
  `review_text`     TEXT            NOT NULL,
  `is_approved`     TINYINT(1)      NOT NULL DEFAULT 0,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_is_approved` (`is_approved`),
  CONSTRAINT `fk_testimonial_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: faqs
-- ============================================================
DROP TABLE IF EXISTS `faqs`;
CREATE TABLE `faqs` (
  `id`              CHAR(36)        NOT NULL,
  `question`        TEXT            NOT NULL,
  `answer`          TEXT            NOT NULL,
  `sort_order`      INT             NOT NULL DEFAULT 0,
  `is_published`    TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: contact_messages
-- ============================================================
DROP TABLE IF EXISTS `contact_messages`;
CREATE TABLE `contact_messages` (
  `id`              CHAR(36)        NOT NULL,
  `name`            VARCHAR(150)    NOT NULL,
  `email`           VARCHAR(200)    DEFAULT NULL,
  `mobile`          VARCHAR(20)     DEFAULT NULL,
  `subject`         VARCHAR(255)    DEFAULT 'General Enquiry',
  `message`         TEXT            NOT NULL,
  `is_read`         TINYINT(1)      NOT NULL DEFAULT 0,
  `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_is_read`   (`is_read`),
  INDEX `idx_created_at`(`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: otp_store
-- ============================================================
DROP TABLE IF EXISTS `otp_store`;
CREATE TABLE `otp_store` (
  `id`          INT             NOT NULL AUTO_INCREMENT,
  `mobile`      VARCHAR(20)     NOT NULL,
  `otp`         VARCHAR(6)      NOT NULL,
  `purpose`     VARCHAR(30)     NOT NULL DEFAULT 'login',
  `expires_at`  DATETIME        NOT NULL,
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_mobile_purpose` (`mobile`, `purpose`),
  INDEX `idx_expires_at`     (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: refresh_tokens
-- ============================================================
DROP TABLE IF EXISTS `refresh_tokens`;
CREATE TABLE `refresh_tokens` (
  `id`          INT             NOT NULL AUTO_INCREMENT,
  `user_id`     CHAR(36)        NOT NULL,
  `token`       VARCHAR(512)    NOT NULL,
  `expires_at`  DATETIME        NOT NULL,
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user_id`   (`user_id`),
  INDEX `idx_token`     (`token`(255)),
  CONSTRAINT `fk_rt_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: settings
-- ============================================================
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `key`         VARCHAR(100)    NOT NULL,
  `value`       TEXT            NOT NULL,
  `updated_at`  DATETIME        DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- ============================================================
-- SAMPLE DATA — INSERT STATEMENTS
-- ============================================================
-- ============================================================

-- ============================================================
-- USERS (1 Admin + 5 Students)
-- Password for all: Test@123 (bcrypt hash below)
-- Admin password: Admin@123
-- ============================================================
INSERT INTO `users` (`id`,`name`,`email`,`mobile`,`password`,`role`,`level`,`goal`,`is_verified`,`is_active`,`last_login`,`created_at`) VALUES
('u-admin-001','Kritika Rai','kritika@germanmantra.in','+91 98765 00001','$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMeJfhS9l7AQKrVZL.kVN9j6Oy','admin',NULL,'Run German Mantra platform',1,1,'2026-08-30 10:00:00','2026-01-01 00:00:00'),
('u-student-001','Priya Sharma','priya.sharma@gmail.com','+91 98765 43210','$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMeJfhS9l7AQKrVZL.kVN9j6Oy','student','A1','Study Masters in Germany',1,1,'2026-08-30 09:00:00','2026-05-02 11:00:00'),
('u-student-002','Rohan Kumar','rohan.kumar@gmail.com','+91 87654 32109','$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMeJfhS9l7AQKrVZL.kVN9j6Oy','student','A2','Job in Germany',1,1,'2026-08-29 18:00:00','2026-06-10 12:00:00'),
('u-student-003','Ananya Kapoor','ananya.kapoor@gmail.com','+91 76543 21098','$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMeJfhS9l7AQKrVZL.kVN9j6Oy','student','Beginner','GOETHE A1 certification',1,1,'2026-08-28 14:00:00','2026-07-15 10:00:00'),
('u-student-004','Vikram Patel','vikram.patel@gmail.com','+91 65432 10987','$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMeJfhS9l7AQKrVZL.kVN9j6Oy','student','B1','Work permit in Germany',1,1,'2026-08-29 20:00:00','2026-04-20 09:00:00'),
('u-student-005','Sneha Joshi','sneha.joshi@gmail.com','+91 54321 09876','$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMeJfhS9l7AQKrVZL.kVN9j6Oy','student','A2','Travel to Germany',1,1,'2026-08-27 16:00:00','2026-03-05 08:00:00');

-- ============================================================
-- COURSES (6 courses)
-- ============================================================
INSERT INTO `courses` (`id`,`title`,`slug`,`level`,`type`,`price`,`original_price`,`duration`,`total_lessons`,`rating`,`review_count`,`enrolled_count`,`instructor`,`description`,`is_published`,`is_featured`,`has_certificate`,`created_at`) VALUES
('c-001','German A1 — Complete Beginner','german-a1-complete-beginner','A1','live+recorded',4999.00,7999.00,'3 Months',42,4.9,180,180,'Kritika Rai','Start from zero and reach A1 level with live classes, recorded lessons and guided study material. Perfect for absolute beginners.',1,1,1,'2026-01-01 00:00:00'),
('c-002','German A2 — Elementary Level','german-a2-elementary','A2','live+recorded',5999.00,9999.00,'3 Months',56,4.8,120,120,'Kritika Rai','Build on A1 foundations — expand vocabulary, grammar and everyday conversation skills to reach A2 level.',1,1,1,'2026-01-01 00:00:00'),
('c-003','German B1 — Intermediate','german-b1-intermediate','B1','live+recorded',7499.00,12999.00,'4 Months',68,4.9,85,85,'Kritika Rai','Achieve conversational fluency and prepare for the GOETHE B1 examination. Focus on complex grammar and real-world usage.',1,0,1,'2026-01-01 00:00:00'),
('c-004','German B2 — Upper Intermediate','german-b2-upper-intermediate','B2','live+recorded',9999.00,15999.00,'4 Months',80,4.9,60,60,'Kritika Rai','Master professional-level German for study abroad and careers in Germany. GOETHE B2 exam preparation included.',1,0,1,'2026-01-01 00:00:00'),
('c-005','German Speaking Course','german-speaking-course','All Levels','live',3499.00,5999.00,'6 Weeks',30,4.9,200,200,'Kritika Rai','Focused speaking practice — roleplay, pronunciation, conversation drills with live feedback from Kritika ma am.',1,1,1,'2026-01-01 00:00:00'),
('c-006','German Grammar Masterclass','german-grammar-masterclass','All Levels','recorded',2999.00,4999.00,'8 Weeks',48,4.8,150,150,'Kritika Rai','All German grammar cases, tenses, and sentence structure — from articles to complex sentences. Self-paced recorded course.',1,0,1,'2026-01-01 00:00:00');

-- ============================================================
-- LESSONS — A1 course (sample 10 of 42)
-- ============================================================
INSERT INTO `lessons` (`id`,`course_id`,`title`,`description`,`duration_min`,`sort_order`,`is_free`,`is_published`,`created_at`) VALUES
('l-001','c-001','Introduction to German Language','Overview of German alphabet, sounds and language basics',45,1,1,1,'2026-01-05 00:00:00'),
('l-002','c-001','Greetings & Basic Vocabulary','Hallo, Guten Morgen, Wie heißen Sie — essential greetings',40,2,0,1,'2026-01-05 00:00:00'),
('l-003','c-001','Numbers 1–100','German numbers, counting and basic arithmetic expressions',35,3,0,1,'2026-01-05 00:00:00'),
('l-004','c-001','Articles — der, die, das','Definite and indefinite articles, noun gender in German',50,4,0,1,'2026-01-05 00:00:00'),
('l-005','c-001','Personal Pronouns','ich, du, er, sie, es, wir, ihr, sie — pronoun system',40,5,0,1,'2026-01-05 00:00:00'),
('l-006','c-001','Basic Verbs — sein & haben','To be and to have — the most essential German verbs',45,6,0,1,'2026-01-05 00:00:00'),
('l-007','c-001','Present Tense Conjugation','Regular verb conjugation in Präsens tense',55,7,0,1,'2026-01-05 00:00:00'),
('l-008','c-001','Nominative Case','Subject case — who is performing the action?',50,8,0,1,'2026-01-05 00:00:00'),
('l-009','c-001','Accusative Case','Object case — what is receiving the action?',55,9,0,1,'2026-01-05 00:00:00'),
('l-010','c-001','Dative Case','Indirect object case — to whom/for whom?',55,10,0,1,'2026-01-05 00:00:00');

-- ============================================================
-- LIVE CLASSES
-- ============================================================
INSERT INTO `live_classes` (`id`,`course_id`,`title`,`description`,`class_date`,`start_time`,`end_time`,`instructor`,`meeting_link`,`recording_url`,`status`,`created_at`) VALUES
('lc-001','c-001','A1 — Articles & Gender (der/die/das)','Practice session for German articles and noun gender',     '2026-08-30','18:00:00','19:30:00','Kritika Rai','https://zoom.us/j/demo001',NULL,'scheduled','2026-08-25 00:00:00'),
('lc-002','c-002','A2 — Modal Verbs (können, müssen, dürfen)','Deep dive into German modal verbs and their usage',       '2026-09-02','19:00:00','20:30:00','Kritika Rai','https://zoom.us/j/demo002',NULL,'scheduled','2026-08-25 00:00:00'),
('lc-003','c-005','Speaking — Everyday Conversations & Roleplay','Practice common conversations: shopping, travel, office',  '2026-09-05','20:00:00','21:30:00','Kritika Rai','https://zoom.us/j/demo003',NULL,'scheduled','2026-08-25 00:00:00'),
('lc-004','c-001','A1 — Introduction to Noun Cases','Overview of nominative and accusative case with examples',  '2026-08-25','18:00:00','19:30:00','Kritika Rai','https://zoom.us/j/demo004','https://recordings.example.com/lc-004','completed','2026-08-20 00:00:00'),
('lc-005','c-001','A1 — Greetings & Basic Vocabulary','First class of A1 batch — alphabet, greetings, introductions','2026-08-20','18:00:00','19:30:00','Kritika Rai','https://zoom.us/j/demo005','https://recordings.example.com/lc-005','completed','2026-08-15 00:00:00');

-- ============================================================
-- ENROLLMENTS
-- ============================================================
INSERT INTO `enrollments` (`id`,`user_id`,`course_id`,`payment_id`,`progress`,`access_type`,`completed_at`,`created_at`) VALUES
('en-001','u-student-001','c-001','pay-001',67,'lifetime',NULL,'2026-05-02 12:00:00'),
('en-002','u-student-001','c-002','pay-002',21,'lifetime',NULL,'2026-06-10 12:00:00'),
('en-003','u-student-001','c-005','pay-003',80,'lifetime',NULL,'2026-06-15 12:00:00'),
('en-004','u-student-001','c-006','pay-004',100,'lifetime','2026-08-25 00:00:00','2026-05-15 12:00:00'),
('en-005','u-student-002','c-002','pay-005',54,'lifetime',NULL,'2026-06-10 14:00:00'),
('en-006','u-student-002','c-003','pay-006',30,'lifetime',NULL,'2026-07-01 10:00:00'),
('en-007','u-student-003','c-001','pay-007',38,'lifetime',NULL,'2026-07-15 11:00:00'),
('en-008','u-student-004','c-003','pay-008',91,'lifetime',NULL,'2026-04-20 09:00:00'),
('en-009','u-student-004','c-004','pay-009',45,'lifetime',NULL,'2026-05-20 09:00:00'),
('en-010','u-student-005','c-001','pay-010',72,'lifetime',NULL,'2026-03-05 08:00:00'),
('en-011','u-student-005','c-005','pay-011',90,'lifetime',NULL,'2026-03-20 08:00:00');

-- ============================================================
-- PAYMENTS
-- ============================================================
INSERT INTO `payments` (`id`,`user_id`,`course_id`,`course_name`,`amount`,`discount`,`final_amount`,`coupon_code`,`razorpay_order_id`,`razorpay_payment_id`,`payment_method`,`status`,`paid_at`,`created_at`) VALUES
('pay-001','u-student-001','c-001','German A1 — Complete Beginner',  4999.00,0.00,4999.00,NULL,'order_demo_001','pay_demo_001','UPI',    'paid','2026-05-02 12:00:00','2026-05-02 11:55:00'),
('pay-002','u-student-001','c-002','German A2 — Elementary Level',   5999.00,0.00,5999.00,NULL,'order_demo_002','pay_demo_002','NetBanking','paid','2026-06-10 12:00:00','2026-06-10 11:55:00'),
('pay-003','u-student-001','c-005','German Speaking Course',          3499.00,700.00,2799.00,'GERMAN20','order_demo_003','pay_demo_003','UPI',  'paid','2026-06-15 12:00:00','2026-06-15 11:55:00'),
('pay-004','u-student-001','c-006','German Grammar Masterclass',      2999.00,500.00,2499.00,'FLAT500','order_demo_004','pay_demo_004','Card', 'paid','2026-05-15 12:00:00','2026-05-15 11:55:00'),
('pay-005','u-student-002','c-002','German A2 — Elementary Level',   5999.00,0.00,5999.00,NULL,'order_demo_005','pay_demo_005','UPI',    'paid','2026-06-10 14:00:00','2026-06-10 13:55:00'),
('pay-006','u-student-002','c-003','German B1 — Intermediate',        7499.00,0.00,7499.00,NULL,'order_demo_006','pay_demo_006','Card',   'paid','2026-07-01 10:00:00','2026-07-01 09:55:00'),
('pay-007','u-student-003','c-001','German A1 — Complete Beginner',  4999.00,0.00,4999.00,NULL,'order_demo_007','pay_demo_007','UPI',    'paid','2026-07-15 11:00:00','2026-07-15 10:55:00'),
('pay-008','u-student-004','c-003','German B1 — Intermediate',        7499.00,0.00,7499.00,NULL,'order_demo_008','pay_demo_008','NetBanking','paid','2026-04-20 09:00:00','2026-04-20 08:55:00'),
('pay-009','u-student-004','c-004','German B2 — Upper Intermediate',  9999.00,0.00,9999.00,NULL,'order_demo_009','pay_demo_009','Card',   'paid','2026-05-20 09:00:00','2026-05-20 08:55:00'),
('pay-010','u-student-005','c-001','German A1 — Complete Beginner',  4999.00,999.00,4000.00,'GERMAN20','order_demo_010','pay_demo_010','UPI','paid','2026-03-05 08:00:00','2026-03-05 07:55:00'),
('pay-011','u-student-005','c-005','German Speaking Course',          3499.00,0.00,3499.00,NULL,'order_demo_011','pay_demo_011','UPI',    'paid','2026-03-20 08:00:00','2026-03-20 07:55:00'),
-- Failed payment example
('pay-012','u-student-003','c-002','German A2 — Elementary Level',   5999.00,0.00,5999.00,NULL,'order_demo_012',NULL,NULL,'failed',NULL,'2026-08-28 10:00:00');

-- ============================================================
-- LESSON PROGRESS (Priya — enrolled in A1, 28/42 done)
-- ============================================================
INSERT INTO `lesson_progress` (`id`,`enrollment_id`,`user_id`,`lesson_id`,`course_id`,`is_completed`,`watch_time_sec`,`completed_at`,`created_at`) VALUES
('lp-001','en-001','u-student-001','l-001','c-001',1,2700,'2026-05-03 10:00:00','2026-05-03 10:00:00'),
('lp-002','en-001','u-student-001','l-002','c-001',1,2400,'2026-05-05 10:00:00','2026-05-05 10:00:00'),
('lp-003','en-001','u-student-001','l-003','c-001',1,2100,'2026-05-07 10:00:00','2026-05-07 10:00:00'),
('lp-004','en-001','u-student-001','l-004','c-001',1,3000,'2026-05-09 10:00:00','2026-05-09 10:00:00'),
('lp-005','en-001','u-student-001','l-005','c-001',1,2400,'2026-05-11 10:00:00','2026-05-11 10:00:00'),
('lp-006','en-001','u-student-001','l-006','c-001',1,2700,'2026-05-13 10:00:00','2026-05-13 10:00:00'),
('lp-007','en-001','u-student-001','l-007','c-001',1,3300,'2026-05-15 10:00:00','2026-05-15 10:00:00'),
('lp-008','en-001','u-student-001','l-008','c-001',1,3000,'2026-05-17 10:00:00','2026-05-17 10:00:00'),
('lp-009','en-001','u-student-001','l-009','c-001',1,3300,'2026-05-19 10:00:00','2026-05-19 10:00:00'),
('lp-010','en-001','u-student-001','l-010','c-001',0,1200,NULL,'2026-08-28 10:00:00');

-- ============================================================
-- COUPONS
-- ============================================================
INSERT INTO `coupons` (`id`,`code`,`type`,`value`,`max_discount`,`usage_limit`,`used_count`,`course_scope`,`expires_at`,`is_active`,`created_at`) VALUES
('cp-001','GERMAN20','percentage',20.00,1000.00,100,45,'all','2026-09-30',1,'2026-01-01 00:00:00'),
('cp-002','FLAT500','fixed',500.00,500.00,50,23,'all','2026-10-15',1,'2026-01-01 00:00:00'),
('cp-003','WELCOME15','percentage',15.00,750.00,80,80,'all','2026-08-15',0,'2026-01-01 00:00:00'),
('cp-004','A1SPECIAL','percentage',25.00,1500.00,30,12,'c-001','2026-12-31',1,'2026-06-01 00:00:00');

-- ============================================================
-- STUDY MATERIALS
-- ============================================================
INSERT INTO `study_materials` (`id`,`course_id`,`title`,`file_type`,`file_url`,`file_size_kb`,`sort_order`,`is_published`,`created_at`) VALUES
('mat-001','c-001','A1 Grammar Notes — Chapter 1','PDF','https://cdn.germanmantra.in/materials/a1-grammar-ch1.pdf',2458,1,1,'2026-01-10 00:00:00'),
('mat-002','c-001','Vocabulary List — Week 1-4','PDF','https://cdn.germanmantra.in/materials/a1-vocab-w1-4.pdf',1843,2,1,'2026-01-10 00:00:00'),
('mat-003','c-001','Worksheet — Articles Practice','PDF','https://cdn.germanmantra.in/materials/a1-articles-worksheet.pdf',912,3,1,'2026-01-10 00:00:00'),
('mat-004','c-001','Pronunciation Audio — Unit 1','MP3','https://cdn.germanmantra.in/materials/a1-pronunciation-u1.mp3',14336,4,1,'2026-01-10 00:00:00'),
('mat-005','c-006','Grammar Summary Sheet — All Cases','PDF','https://cdn.germanmantra.in/materials/grammar-all-cases.pdf',3174,1,1,'2026-01-15 00:00:00'),
('mat-006','c-002','A2 Modal Verbs Cheat Sheet','PDF','https://cdn.germanmantra.in/materials/a2-modal-verbs.pdf',573,1,1,'2026-01-15 00:00:00'),
('mat-007','c-005','Conversation Practice Audio — Module 1','MP3','https://cdn.germanmantra.in/materials/speaking-conv-m1.mp3',22528,1,1,'2026-01-15 00:00:00'),
('mat-008','c-006','Verb Conjugation Tables — All Tenses','PDF','https://cdn.germanmantra.in/materials/verb-conjugation-all.pdf',1229,2,1,'2026-01-15 00:00:00');

-- ============================================================
-- TESTS
-- ============================================================
INSERT INTO `tests` (`id`,`course_id`,`title`,`description`,`duration_min`,`total_questions`,`passing_percent`,`is_published`,`created_at`) VALUES
('test-001','c-001','Unit 5 Test — Dative Case','Test on German dative case usage and examples',30,25,60,1,'2026-06-01 00:00:00'),
('test-002','c-001','A1 Mid-Term Practice Test','Comprehensive test covering Units 1-6',60,50,70,1,'2026-07-01 00:00:00'),
('test-003','c-001','Grammar Quiz — Unit 4','Quick quiz on articles and noun cases',20,20,60,1,'2026-05-20 00:00:00'),
('test-004','c-001','Vocabulary Test — Chapter 3','Test on chapters 3 vocabulary words',15,15,60,1,'2026-05-10 00:00:00'),
('test-005','c-002','A2 Module 1 Test — Modal Verbs','Test on können, müssen, dürfen, wollen',25,20,65,1,'2026-07-15 00:00:00');

-- ============================================================
-- QUESTIONS (5 sample questions for test-001)
-- ============================================================
INSERT INTO `questions` (`id`,`test_id`,`question_text`,`option_a`,`option_b`,`option_c`,`option_d`,`correct_option`,`explanation`,`sort_order`) VALUES
('q-001','test-001','Which article is used for "Vater" (father) in the dative case?','der','die','dem','den','C','Masculine nouns use "dem" in the dative case. Vater is masculine (der Vater), so dative = dem Vater.',1),
('q-002','test-001','Select the correct dative form: "Ich helfe ___ Frau" (I help the woman)','die','der','dem','den','B','Feminine nouns use "der" in the dative case. Frau is feminine (die Frau), so dative = der Frau.',2),
('q-003','test-001','What is "Haus" in German?','Car','House','Dog','Tree','B','Haus means House in German. It is a neuter noun (das Haus).',3),
('q-004','test-001','"Schmetterling" means…','Butterfly','Flower','River','Mountain','A','Schmetterling = Butterfly. It is one of the most beautiful German words!',4),
('q-005','test-001','Which is correct? "Ich gebe ___ Kind das Buch"','der','die','dem','des','C','Kind (child) is neuter (das Kind). In dative case, neuter noun takes "dem". So: dem Kind.',5);

-- ============================================================
-- TEST ATTEMPTS (Priya's completed tests)
-- ============================================================
INSERT INTO `test_attempts` (`id`,`test_id`,`user_id`,`score_percent`,`correct_answers`,`total_questions`,`time_taken_sec`,`is_passed`,`completed_at`) VALUES
('att-001','test-003','u-student-001',88,17,20,720,1,'2026-07-20 15:00:00'),
('att-002','test-004','u-student-001',93,14,15,540,1,'2026-07-25 16:00:00'),
('att-003','test-003','u-student-002',75,15,20,900,1,'2026-07-21 11:00:00'),
('att-004','test-005','u-student-002',80,16,20,1080,1,'2026-08-01 10:00:00'),
('att-005','test-003','u-student-004',95,19,20,480,1,'2026-06-15 09:00:00');

-- ============================================================
-- CERTIFICATES
-- ============================================================
INSERT INTO `certificates` (`id`,`user_id`,`course_id`,`enrollment_id`,`certificate_no`,`issued_at`,`pdf_url`) VALUES
('cert-001','u-student-001','c-006','en-004','GM-CERT-2026-0041','2026-08-25 10:00:00','https://cdn.germanmantra.in/certs/GM-CERT-2026-0041.pdf'),
('cert-002','u-student-004','c-003','en-008','GM-CERT-2026-0038','2026-08-10 10:00:00','https://cdn.germanmantra.in/certs/GM-CERT-2026-0038.pdf'),
('cert-003','u-student-005','c-005','en-011','GM-CERT-2026-0035','2026-07-28 10:00:00','https://cdn.germanmantra.in/certs/GM-CERT-2026-0035.pdf');

-- ============================================================
-- TESTIMONIALS
-- ============================================================
INSERT INTO `testimonials` (`id`,`user_id`,`name`,`course`,`rating`,`review_text`,`is_approved`,`created_at`) VALUES
('test-t-001','u-student-001','Priya Sharma','German A1',5,'Kritika ma\'am explains everything so clearly. I went from zero German to passing my A1 in just 3 months! The live classes are incredibly interactive and the study material is top-notch.',1,'2026-08-20 00:00:00'),
('test-t-002','u-student-002','Rohan Kumar','German B1',5,'Best German coaching online! I am now studying my Masters in Berlin. The structured curriculum and Kritika ma\'am\'s teaching is absolutely unmatched. Highly recommend!',1,'2026-08-15 00:00:00'),
('test-t-003','u-student-005','Sneha Joshi','German Speaking Course',5,'The speaking course transformed my confidence completely. The roleplay sessions and instant feedback from Kritika ma\'am are invaluable. Worth every rupee!',1,'2026-08-10 00:00:00'),
('test-t-004','u-student-003','Ananya Kapoor','German A1',4,'Great course structure and very supportive instructor. The WhatsApp support is a lifesaver when you have doubts.',1,'2026-08-05 00:00:00'),
(NULL,NULL,'Vikram Mehta','German B2',5,'Passed my B2 exam on first attempt thanks to German Mantra. The grammar masterclass was particularly helpful.',1,'2026-07-28 00:00:00'),
(NULL,NULL,'Nidhi Sharma','German A2',5,'From a complete beginner to confidently speaking German in 6 months. Kritika ma\'am is an exceptional teacher!',1,'2026-07-20 00:00:00');

-- ============================================================
-- FAQs
-- ============================================================
INSERT INTO `faqs` (`id`,`question`,`answer`,`sort_order`,`is_published`,`created_at`) VALUES
('faq-001','Are classes live or recorded?','German Mantra offers both! Live interactive Zoom classes with Kritika ma\'am, plus all sessions are recorded and accessible forever for revision at your own pace.',1,1,'2026-01-01 00:00:00'),
('faq-002','What is the course duration?','A1 and A2 are 3 months each. B1 and B2 are 4 months each. The Speaking Course is 6 weeks and Grammar Masterclass is 8 weeks.',2,1,'2026-01-01 00:00:00'),
('faq-003','Do I get a certificate?','Yes! On completing any course and passing the final test, you receive a verified Certificate of Completion from German Mantra. Certificates are PDF downloads.',3,1,'2026-01-01 00:00:00'),
('faq-004','Is there a free trial class?','Absolutely — you can attend 1 free demo class before enrolling in any course. Register on our website and we will schedule your demo.',4,1,'2026-01-01 00:00:00'),
('faq-005','What payment methods are accepted?','We accept all major payment methods via Razorpay — UPI (GPay, PhonePe, Paytm), Credit/Debit Cards, Net Banking, and EMI options.',5,1,'2026-01-01 00:00:00'),
('faq-006','Can I access classes from outside India?','Yes! German Mantra is fully online and accessible from anywhere in the world. Payments can be made in INR via international cards.',6,1,'2026-01-01 00:00:00');

-- ============================================================
-- CONTACT MESSAGES (sample)
-- ============================================================
INSERT INTO `contact_messages` (`id`,`name`,`email`,`mobile`,`subject`,`message`,`is_read`,`created_at`) VALUES
('msg-001','Aarav Singh','aarav@gmail.com','+91 90000 00001','Course Enquiry — German B2','Hello, I have completed B1 and want to enroll in B2. When does the next batch start? Also is there any discount available?',0,'2026-08-30 09:00:00'),
('msg-002','Meera Patel','meera@gmail.com','+91 90000 00002','Payment Issue','I made a payment of Rs 4999 for A1 course yesterday but my enrollment is not showing. Order ID: order_demo_999. Please help.',0,'2026-08-29 14:00:00'),
('msg-003','Siddharth Rao','siddharth@gmail.com','+91 90000 00003','Live Class Schedule Query','Can you please share the complete schedule for the upcoming A2 batch? I want to check if the timings work for me before enrolling.',1,'2026-08-28 11:00:00');

-- ============================================================
-- SETTINGS
-- ============================================================
INSERT INTO `settings` (`key`,`value`) VALUES
('brand_name',          'German Mantra'),
('founder_name',        'Kritika Rai'),
('contact_email',       'hello@germanmantra.in'),
('whatsapp_number',     '+91XXXXXXXXXX'),
('instagram_url',       'https://instagram.com/germanmantra'),
('youtube_url',         'https://youtube.com/@germanmantra'),
('facebook_url',        'https://facebook.com/germanmantra'),
('razorpay_mode',       'test'),
('currency',            'INR'),
('gst_percent',         '18'),
('registration_enabled','1'),
('enrollment_enabled',  '1'),
('email_notifications', '1'),
('cert_auto_issue',     '1'),
('maintenance_mode',    '0');

-- ============================================================
-- NOTIFICATIONS (sample sent)
-- ============================================================
INSERT INTO `notifications` (`id`,`title`,`body`,`audience`,`course_id`,`recipient_count`,`sent_by`,`sent_at`) VALUES
('notif-001','Live Class Tomorrow at 6 PM!','German A1 class on Articles & Gender is scheduled for tomorrow, Aug 30 at 6:00 PM. Join via Zoom link in your dashboard.','course','c-001',180,'u-admin-001','2026-08-29 10:00:00'),
('notif-002','New Study Material Uploaded','Grammar Notes Chapter 7 has been added to your German A1 course. Download from Study Material section.','course','c-001',180,'u-admin-001','2026-08-27 12:00:00'),
('notif-003','Welcome Offer — 20% Off All Courses!','Use code GERMAN20 to get 20% off on any course. Valid till September 30, 2026. Refer a friend and earn extra rewards!','all',NULL,532,'u-admin-001','2026-08-25 09:00:00');

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- USEFUL VIEWS
-- ============================================================

-- View: Revenue summary per course
CREATE OR REPLACE VIEW `v_course_revenue` AS
SELECT
  c.id,
  c.title,
  c.level,
  c.enrolled_count,
  c.price,
  COUNT(p.id)               AS total_transactions,
  SUM(p.final_amount)       AS total_revenue,
  AVG(p.final_amount)       AS avg_payment
FROM courses c
LEFT JOIN payments p ON p.course_id = c.id AND p.status = 'paid'
GROUP BY c.id, c.title, c.level, c.enrolled_count, c.price;

-- View: Student enrollment summary
CREATE OR REPLACE VIEW `v_student_summary` AS
SELECT
  u.id,
  u.name,
  u.email,
  u.mobile,
  u.level,
  u.is_active,
  u.created_at AS joined_at,
  COUNT(e.id)            AS enrolled_courses,
  SUM(p.final_amount)    AS total_paid,
  AVG(e.progress)        AS avg_progress
FROM users u
LEFT JOIN enrollments e ON e.user_id = u.id
LEFT JOIN payments p    ON p.user_id = u.id AND p.status = 'paid'
WHERE u.role = 'student'
GROUP BY u.id, u.name, u.email, u.mobile, u.level, u.is_active, u.created_at;

-- View: Monthly revenue
CREATE OR REPLACE VIEW `v_monthly_revenue` AS
SELECT
  DATE_FORMAT(paid_at, '%Y-%m')   AS month,
  COUNT(*)                         AS transactions,
  SUM(final_amount)                AS revenue,
  SUM(discount)                    AS total_discount
FROM payments
WHERE status = 'paid'
GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
ORDER BY month DESC;

-- ============================================================
-- USEFUL QUERIES (as comments for reference)
-- ============================================================

-- Get all enrolled students for a course:
-- SELECT u.name, u.email, e.progress, e.created_at
-- FROM enrollments e
-- JOIN users u ON u.id = e.user_id
-- WHERE e.course_id = 'c-001'
-- ORDER BY e.created_at DESC;

-- Get revenue stats:
-- SELECT * FROM v_monthly_revenue;

-- Get student with full details:
-- SELECT * FROM v_student_summary WHERE id = 'u-student-001';

-- Get pending payments:
-- SELECT u.name, u.email, p.course_name, p.final_amount, p.created_at
-- FROM payments p
-- JOIN users u ON u.id = p.user_id
-- WHERE p.status = 'pending'
-- ORDER BY p.created_at DESC;

-- Get test pass rate per test:
-- SELECT t.title,
--   COUNT(a.id) AS total_attempts,
--   SUM(a.is_passed) AS passed,
--   ROUND(AVG(a.score_percent), 1) AS avg_score
-- FROM tests t
-- LEFT JOIN test_attempts a ON a.test_id = t.id
-- GROUP BY t.id, t.title;

-- ============================================================
-- END OF GERMAN MANTRA SQL DUMP
-- Total Tables: 16
-- Total Sample Records: ~100+
-- ============================================================