-- ==========================================================
-- Database Schema for Telegram Online Courses Platform
-- Designed for MySQL 8.0+ / MariaDB 10.4+
-- Charset: utf8mb4 / Collation: utf8mb4_unicode_ci
-- ==========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------
-- 1. جدول کاربران ربات تلگرام (users)
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `first_name` VARCHAR(255) NOT NULL,
  `last_name` VARCHAR(255) NULL DEFAULT NULL,
  `username` VARCHAR(191) NULL DEFAULT NULL,
  `phone` VARCHAR(30) NULL DEFAULT NULL,
  `is_blocked` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_users_telegram_id` (`telegram_id` ASC),
  INDEX `idx_users_username` (`username` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- 2. جدول دسته‌بندی دوره‌ها (categories)
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_categories_slug` (`slug` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- 3. جدول دوره‌های آموزشی (courses)
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `courses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `instructor` VARCHAR(150) NOT NULL,
  `price` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'قیمت دوره به تومان',
  `is_free` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = رایگان، 0 = پولی',
  `banner_file_id` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Telegram photo file_id',
  `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_courses_slug` (`slug` ASC),
  INDEX `idx_courses_status` (`status` ASC),
  INDEX `fk_courses_category_idx` (`category_id` ASC),
  CONSTRAINT `fk_courses_category`
    FOREIGN KEY (`category_id`)
    REFERENCES `categories` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- 4. جدول جلسات و ویدیوهای آموزشی (lessons)
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `lessons` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `telegram_file_id` VARCHAR(255) NOT NULL COMMENT 'Telegram video file_id',
  `duration_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 1,
  `is_free_preview` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = جلسه پیش نمایش رایگان',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `fk_lessons_course_idx` (`course_id` ASC),
  INDEX `idx_lessons_sort` (`course_id` ASC, `sort_order` ASC),
  CONSTRAINT `fk_lessons_course`
    FOREIGN KEY (`course_id`)
    REFERENCES `courses` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- 5. جدول تراکنش‌های مالی زرین‌پال (transactions)
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `amount` INT UNSIGNED NOT NULL COMMENT 'مبلغ به تومان خوانده شده قطعی از دیتابیس',
  `authority` VARCHAR(64) NOT NULL COMMENT 'شناسه تراکنش صادر شده توسط زرین‌پال',
  `ref_id` VARCHAR(64) NULL DEFAULT NULL COMMENT 'شماره پیگیری زرین‌پال پس از Verify',
  `status` ENUM('pending', 'success', 'failed', 'canceled') NOT NULL DEFAULT 'pending',
  `response_code` INT NULL DEFAULT NULL COMMENT 'کد پاسخ زرین‌پال (100 یا 101)',
  `card_pan` VARCHAR(32) NULL DEFAULT NULL COMMENT 'شماره کارت ماسک شده خریدار',
  `error_message` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `verified_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_transactions_authority` (`authority` ASC),
  INDEX `idx_transactions_status` (`status` ASC),
  INDEX `fk_transactions_user_idx` (`user_id` ASC),
  INDEX `fk_transactions_course_idx` (`course_id` ASC),
  CONSTRAINT `fk_transactions_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_transactions_course`
    FOREIGN KEY (`course_id`)
    REFERENCES `courses` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- 6. جدول خریدهای قطعی و دسترسی‌های ثبت شده (purchases)
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchases` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `course_id` INT UNSIGNED NOT NULL,
  `transaction_id` INT UNSIGNED NOT NULL,
  `amount_paid` INT UNSIGNED NOT NULL,
  `purchased_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_user_course_unique` (`user_id` ASC, `course_id` ASC),
  INDEX `fk_purchases_transaction_idx` (`transaction_id` ASC),
  CONSTRAINT `fk_purchases_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_purchases_course`
    FOREIGN KEY (`course_id`)
    REFERENCES `courses` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_purchases_transaction`
    FOREIGN KEY (`transaction_id`)
    REFERENCES `transactions` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- 7. جدول مدیران پنل (admins)
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(80) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `last_login` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_admins_username` (`username` ASC),
  UNIQUE INDEX `idx_admins_email` (`email` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------
-- وارد کردن داده‌های اولیه نمونه (Initial Seeds)
-- ----------------------------------------------------------

-- ۱. مدیر پیش‌فرض (نام کاربری: admin | رمز عبور: admin123)
-- Hash generated by password_hash('admin123', PASSWORD_BCRYPT)
INSERT INTO `admins` (`username`, `password_hash`, `full_name`, `email`)
VALUES ('admin', '$2y$10$E9.UkmZ6K7sO9v9l6E1C4.3s2k8tZ0eW.WpXo6.p0k3zQpQcT0Bsy', 'مدیر سیستم', 'admin@example.com')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- ۲. دسته‌بندی‌های نمونه
INSERT INTO `categories` (`id`, `name`, `slug`) VALUES
(1, 'برنامه‌نویسی وب', 'web-development'),
(2, 'طراحی رابط کاربری UI/UX', 'ui-ux-design'),
(3, 'هوش مصنوعی و داده', 'ai-and-data')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- ۳. دوره‌های آموزشی نمونه (یکی پولی ویژه و دیگری رایگان)
INSERT INTO `courses` (`id`, `category_id`, `title` , `slug`, `description`, `instructor`, `price`, `is_free`, `banner_file_id`, `status`) VALUES
(1, 1, 'دوره جامع توسعه ربات تلگرام با PHP 8 و زرین‌پال', 'php-telegram-bot-mastery', 'در این دوره پیشرفته یاد می‌گیرید چگونه ربات‌های تجاری تلگرام را با معماری MVC، درگاه پرداخت زرین‌پال v4، مقابله با Double Spending و مدیریت جلسات پیاده‌سازی کنید.', 'مهندس علیرضا رضایی', 290000, 0, NULL, 'active'),
(2, 1, 'دوره رایگان مفاهیم پایه Webhook و API در تلگرام', 'telegram-webhook-basics', 'آشنایی با متدهای اصلی تلگرام، تنظیم وبهوک SSL، فرمت پیام‌ها و ارسال فایل ویدیویی از طریق سرور.', 'مهندس سارا محمدی', 0, 1, NULL, 'active')
ON DUPLICATE KEY UPDATE `title`=VALUES(`title`);

-- ۴. جلسات نمونه برای دوره‌ها
INSERT INTO `lessons` (`id`, `course_id`, `title`, `description`, `telegram_file_id`, `duration_seconds`, `sort_order`, `is_free_preview`) VALUES
(1, 1, 'جلسه اول: آشنایی با معماری MVC و وبهوک تلگرام', 'بررسی ساختار کلی پروژه و پاسخ سریع 200 OK به تلگرام', 'BAACAgIAAxkBAAIBY1234567890sample1', 720, 1, 1),
(2, 1, 'جلسه دوم: اتصال به درگاه پرداخت زرین‌پال v4 و امنیت', 'بررسی متدهای Request و Verify و جلوگیری از دابل اسپندینگ', 'BAACAgIAAxkBAAIBY1234567890sample2', 1140, 2, 0),
(3, 1, 'جلسه سوم: ارسال ویدیو با محافظت در برابر فوروارد', 'استفاده از file_id تلگرام و پارامتر protect_content', 'BAACAgIAAxkBAAIBY1234567890sample3', 960, 3, 0),
(4, 2, 'جلسه اول: وبهوک چیست و چگونه تنظیم می‌شود؟', 'آموزش استفاده از متد setWebhook تلگرام', 'BAACAgIAAxkBAAIBY1234567890sample4', 600, 1, 1)
ON DUPLICATE KEY UPDATE `title`=VALUES(`title`);

SET FOREIGN_KEY_CHECKS = 1;