-- =========================================================
-- Telegram Course Bot - Future-ready MySQL Schema
-- Target: cPanel / PHP / MySQL 5.7+ or MySQL 8+
-- Currency unit inside DB: TOMAN
-- Point value: 1 point = 10,000 toman
-- Charset: utf8mb4
-- =========================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- برای اجرای مجدد در محیط تست، می‌توانی این بخش را فعال کنی.
-- در محیط واقعی با احتیاط استفاده شود.
-- SET FOREIGN_KEY_CHECKS = 0;
-- DROP TABLE IF EXISTS broadcast_recipients;
-- DROP TABLE IF EXISTS broadcasts;
-- DROP TABLE IF EXISTS referral_rewards;
-- DROP TABLE IF EXISTS referrals;
-- DROP TABLE IF EXISTS point_transactions;
-- DROP TABLE IF EXISTS point_packages;
-- DROP TABLE IF EXISTS coupon_redemptions;
-- DROP TABLE IF EXISTS coupon_users;
-- DROP TABLE IF EXISTS coupon_courses;
-- DROP TABLE IF EXISTS coupons;
-- DROP TABLE IF EXISTS payment_logs;
-- DROP TABLE IF EXISTS payments;
-- DROP TABLE IF EXISTS order_items;
-- DROP TABLE IF EXISTS orders;
-- DROP TABLE IF EXISTS course_reviews;
-- DROP TABLE IF EXISTS lesson_progress;
-- DROP TABLE IF EXISTS course_enrollments;
-- DROP TABLE IF EXISTS lesson_assets;
-- DROP TABLE IF EXISTS course_lessons;
-- DROP TABLE IF EXISTS course_sections;
-- DROP TABLE IF EXISTS courses;
-- DROP TABLE IF EXISTS categories;
-- DROP TABLE IF EXISTS admin_action_logs;
-- DROP TABLE IF EXISTS role_permissions;
-- DROP TABLE IF EXISTS admin_roles;
-- DROP TABLE IF EXISTS permissions;
-- DROP TABLE IF EXISTS roles;
-- DROP TABLE IF EXISTS admins;
-- DROP TABLE IF EXISTS user_states;
-- DROP TABLE IF EXISTS users;
-- DROP TABLE IF EXISTS settings;
-- SET FOREIGN_KEY_CHECKS = 1;

-- =========================================================
-- 1) GLOBAL SETTINGS
-- =========================================================

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` TEXT NULL,
  value_type ENUM('string','integer','decimal','boolean','json') NOT NULL DEFAULT 'string',
  description VARCHAR(255) NULL,
  is_public TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (`key`, `value`, value_type, description, is_public)
VALUES
('currency_unit', 'toman', 'string', 'واحد پول داخل دیتابیس', 1),
('point_value_toman', '10000', 'integer', 'ارزش هر امتیاز به تومان', 1),
('course_access_type', 'lifetime', 'string', 'دسترسی دوره‌ها دائمی است', 1),
('default_referral_reward_start', '0', 'integer', 'پاداش پیش‌فرض دعوت برای استارت', 0),
('default_referral_reward_join_channel', '0', 'integer', 'پاداش پیش‌فرض دعوت برای عضویت کانال', 0),
('default_referral_reward_first_purchase', '0', 'integer', 'پاداش پیش‌فرض دعوت برای اولین خرید', 0)
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

-- =========================================================
-- 2) USERS & STATES
-- =========================================================

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL UNIQUE,
  username VARCHAR(255) NULL,
  first_name VARCHAR(255) NULL,
  last_name VARCHAR(255) NULL,
  language_code VARCHAR(20) NULL,

  status ENUM('active','blocked','banned','deleted') NOT NULL DEFAULT 'active',

  is_bot TINYINT(1) NOT NULL DEFAULT 0,
  is_channel_member TINYINT(1) NOT NULL DEFAULT 0,
  channel_checked_at DATETIME NULL,

  referral_code VARCHAR(32) NOT NULL UNIQUE,
  referred_by_user_id BIGINT UNSIGNED NULL,

  points_balance INT NOT NULL DEFAULT 0,
  total_points_earned INT NOT NULL DEFAULT 0,
  total_points_spent INT NOT NULL DEFAULT 0,

  total_spent_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,
  orders_count INT UNSIGNED NOT NULL DEFAULT 0,

  notifications_enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_activity_at DATETIME NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_users_telegram_id (telegram_id),
  INDEX idx_users_username (username),
  INDEX idx_users_status (status),
  INDEX idx_users_referral_code (referral_code),
  INDEX idx_users_referred_by (referred_by_user_id),

  CONSTRAINT fk_users_referred_by
    FOREIGN KEY (referred_by_user_id)
    REFERENCES users(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_states (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  state VARCHAR(255) NOT NULL,
  data_json JSON NULL,
  expires_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_user_states_expires_at (expires_at),

  CONSTRAINT fk_user_states_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 3) ADMINS, ROLES, PERMISSIONS, LOGS
-- =========================================================

CREATE TABLE IF NOT EXISTS admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  note VARCHAR(255) NULL,
  created_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_admins_user_id (user_id),
  INDEX idx_admins_status (status),

  CONSTRAINT fk_admins_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_admins_created_by
    FOREIGN KEY (created_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  title VARCHAR(150) NOT NULL,
  description VARCHAR(255) NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL UNIQUE,
  title VARCHAR(150) NOT NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_roles (
  admin_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  assigned_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (admin_id, role_id),

  CONSTRAINT fk_admin_roles_admin
    FOREIGN KEY (admin_id)
    REFERENCES admins(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_admin_roles_role
    FOREIGN KEY (role_id)
    REFERENCES roles(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_admin_roles_assigned_by
    FOREIGN KEY (assigned_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (role_id, permission_id),

  CONSTRAINT fk_role_permissions_role
    FOREIGN KEY (role_id)
    REFERENCES roles(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_role_permissions_permission
    FOREIGN KEY (permission_id)
    REFERENCES permissions(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_action_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(150) NOT NULL,
  entity_type VARCHAR(100) NULL,
  entity_id VARCHAR(100) NULL,
  old_data_json JSON NULL,
  new_data_json JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_admin_logs_admin (admin_id),
  INDEX idx_admin_logs_action (action),
  INDEX idx_admin_logs_entity (entity_type, entity_id),
  INDEX idx_admin_logs_created_at (created_at),

  CONSTRAINT fk_admin_logs_admin
    FOREIGN KEY (admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL,

  CONSTRAINT fk_admin_logs_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (name, title, description, is_system)
VALUES
('owner', 'مالک', 'دسترسی کامل به همه بخش‌ها', 1),
('manager', 'مدیر', 'مدیریت عمومی ربات', 1),
('content_admin', 'ادمین محتوا', 'مدیریت دوره‌ها، درس‌ها و فایل‌ها', 1),
('finance', 'مالی', 'مدیریت سفارش‌ها، پرداخت‌ها و تخفیف‌ها', 1),
('support', 'پشتیبانی', 'مشاهده کاربران و پشتیبانی', 1),
('broadcaster', 'ارسال پیام همگانی', 'ساخت و ارسال broadcast', 1)
ON DUPLICATE KEY UPDATE title = VALUES(title), description = VALUES(description);

INSERT INTO permissions (name, title, description)
VALUES
('users.view', 'مشاهده کاربران', NULL),
('users.manage', 'مدیریت کاربران', NULL),
('admins.manage', 'مدیریت ادمین‌ها', NULL),
('courses.view', 'مشاهده دوره‌ها', NULL),
('courses.manage', 'مدیریت دوره‌ها', NULL),
('orders.view', 'مشاهده سفارش‌ها', NULL),
('orders.manage', 'مدیریت سفارش‌ها', NULL),
('payments.view', 'مشاهده پرداخت‌ها', NULL),
('points.manage', 'مدیریت امتیازها', NULL),
('coupons.manage', 'مدیریت کدهای تخفیف', NULL),
('broadcasts.manage', 'مدیریت پیام همگانی', NULL),
('settings.manage', 'مدیریت تنظیمات', NULL)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- =========================================================
-- 4) COURSE STRUCTURE
-- Category -> Course -> Section -> Lesson -> Asset
-- =========================================================

CREATE TABLE IF NOT EXISTS categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  parent_id BIGINT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT NULL,
  icon VARCHAR(50) NULL,
  cover_file_id VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','hidden','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_categories_parent (parent_id),
  INDEX idx_categories_status_sort (status, sort_order),

  CONSTRAINT fk_categories_parent
    FOREIGN KEY (parent_id)
    REFERENCES categories(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS courses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id BIGINT UNSIGNED NULL,

  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  short_description VARCHAR(500) NULL,
  description TEXT NULL,

  teacher_name VARCHAR(255) NULL,
  level ENUM('beginner','intermediate','advanced','all') NOT NULL DEFAULT 'all',

  cover_file_id VARCHAR(255) NULL,
  intro_video_file_id VARCHAR(255) NULL,

  price_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,
  price_points INT UNSIGNED NOT NULL DEFAULT 0,

  is_free TINYINT(1) NOT NULL DEFAULT 0,
  is_purchasable_with_money TINYINT(1) NOT NULL DEFAULT 1,
  is_purchasable_with_points TINYINT(1) NOT NULL DEFAULT 1,

  access_type ENUM('lifetime') NOT NULL DEFAULT 'lifetime',

  status ENUM('draft','published','hidden','archived') NOT NULL DEFAULT 'draft',
  sort_order INT NOT NULL DEFAULT 0,

  total_lessons INT UNSIGNED NOT NULL DEFAULT 0,
  total_assets INT UNSIGNED NOT NULL DEFAULT 0,
  duration_minutes INT UNSIGNED NOT NULL DEFAULT 0,

  sales_count INT UNSIGNED NOT NULL DEFAULT 0,
  students_count INT UNSIGNED NOT NULL DEFAULT 0,
  reviews_count INT UNSIGNED NOT NULL DEFAULT 0,
  rating_avg DECIMAL(3,2) NOT NULL DEFAULT 0.00,

  search_text TEXT NULL,

  published_at DATETIME NULL,
  created_by_admin_id BIGINT UNSIGNED NULL,
  updated_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_courses_category (category_id),
  INDEX idx_courses_status_sort (status, sort_order),
  INDEX idx_courses_price_toman (price_toman),
  INDEX idx_courses_price_points (price_points),
  INDEX idx_courses_published_at (published_at),
  FULLTEXT INDEX ft_courses_search (title, short_description, description, search_text),

  CONSTRAINT fk_courses_category
    FOREIGN KEY (category_id)
    REFERENCES categories(id)
    ON DELETE SET NULL,

  CONSTRAINT fk_courses_created_by
    FOREIGN KEY (created_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL,

  CONSTRAINT fk_courses_updated_by
    FOREIGN KEY (updated_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS course_sections (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('draft','published','hidden','archived') NOT NULL DEFAULT 'published',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_sections_course_sort (course_id, sort_order),
  INDEX idx_sections_status (status),

  CONSTRAINT fk_sections_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS course_lessons (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id BIGINT UNSIGNED NOT NULL,
  section_id BIGINT UNSIGNED NULL,

  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  lesson_type ENUM('video','text','file','quiz','mixed') NOT NULL DEFAULT 'mixed',

  is_preview TINYINT(1) NOT NULL DEFAULT 0,
  duration_minutes INT UNSIGNED NOT NULL DEFAULT 0,

  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('draft','published','hidden','archived') NOT NULL DEFAULT 'published',

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_lessons_course_sort (course_id, sort_order),
  INDEX idx_lessons_section_sort (section_id, sort_order),
  INDEX idx_lessons_status (status),

  CONSTRAINT fk_lessons_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_lessons_section
    FOREIGN KEY (section_id)
    REFERENCES course_sections(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lesson_assets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  course_id BIGINT UNSIGNED NOT NULL,
  lesson_id BIGINT UNSIGNED NOT NULL,

  asset_type ENUM('video','document','photo','audio','text','link','other') NOT NULL,
  title VARCHAR(255) NULL,
  description TEXT NULL,

  telegram_file_id VARCHAR(255) NULL,
  external_url TEXT NULL,
  content_text MEDIUMTEXT NULL,

  file_name VARCHAR(255) NULL,
  mime_type VARCHAR(100) NULL,
  file_size_bytes BIGINT UNSIGNED NULL,

  caption TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','hidden','deleted') NOT NULL DEFAULT 'active',

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_assets_course (course_id),
  INDEX idx_assets_lesson_sort (lesson_id, sort_order),
  INDEX idx_assets_type (asset_type),
  INDEX idx_assets_status (status),

  CONSTRAINT fk_assets_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_assets_lesson
    FOREIGN KEY (lesson_id)
    REFERENCES course_lessons(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 5) COURSE ACCESS, PROGRESS, REVIEWS
-- =========================================================

CREATE TABLE IF NOT EXISTS course_enrollments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  course_id BIGINT UNSIGNED NOT NULL,

  access_type ENUM('purchase_money','purchase_points','free','gift','admin_grant','coupon_100','migration') NOT NULL,
  status ENUM('active','revoked','refunded') NOT NULL DEFAULT 'active',

  order_id BIGINT UNSIGNED NULL,
  granted_by_admin_id BIGINT UNSIGNED NULL,

  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  lifetime_access TINYINT(1) NOT NULL DEFAULT 1,

  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uq_enrollment_user_course (user_id, course_id),
  INDEX idx_enrollments_course (course_id),
  INDEX idx_enrollments_status (status),
  INDEX idx_enrollments_order (order_id),

  CONSTRAINT fk_enrollments_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_enrollments_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_enrollments_admin
    FOREIGN KEY (granted_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lesson_progress (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  course_id BIGINT UNSIGNED NOT NULL,
  lesson_id BIGINT UNSIGNED NOT NULL,

  status ENUM('not_started','in_progress','completed') NOT NULL DEFAULT 'not_started',
  progress_percent TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_asset_id BIGINT UNSIGNED NULL,
  completed_at DATETIME NULL,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uq_lesson_progress (user_id, lesson_id),
  INDEX idx_progress_user_course (user_id, course_id),
  INDEX idx_progress_status (status),

  CONSTRAINT fk_progress_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_progress_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_progress_lesson
    FOREIGN KEY (lesson_id)
    REFERENCES course_lessons(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_progress_asset
    FOREIGN KEY (last_asset_id)
    REFERENCES lesson_assets(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS course_reviews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  course_id BIGINT UNSIGNED NOT NULL,

  rating TINYINT UNSIGNED NOT NULL,
  comment TEXT NULL,
  admin_reply TEXT NULL,

  status ENUM('pending','approved','rejected','deleted') NOT NULL DEFAULT 'pending',
  reviewed_by_admin_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uq_review_user_course (user_id, course_id),
  INDEX idx_reviews_course_status (course_id, status),
  INDEX idx_reviews_rating (rating),

  CONSTRAINT fk_reviews_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_reviews_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_reviews_admin
    FOREIGN KEY (reviewed_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL,

  CONSTRAINT chk_review_rating CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 6) ORDERS, PAYMENTS, COUPONS
-- Currency: TOMAN
-- =========================================================

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_code VARCHAR(40) NOT NULL UNIQUE,
  user_id BIGINT UNSIGNED NOT NULL,

  order_type ENUM('course_purchase','point_purchase','mixed') NOT NULL DEFAULT 'course_purchase',
  payment_method ENUM('money','points','mixed','free') NOT NULL DEFAULT 'money',

  status ENUM('draft','pending_payment','paid','cancelled','expired','failed','refunded','partially_refunded') NOT NULL DEFAULT 'draft',

  subtotal_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,
  discount_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,
  total_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,

  subtotal_points INT UNSIGNED NOT NULL DEFAULT 0,
  discount_points INT UNSIGNED NOT NULL DEFAULT 0,
  total_points INT UNSIGNED NOT NULL DEFAULT 0,

  coupon_id BIGINT UNSIGNED NULL,
  coupon_code VARCHAR(100) NULL,

  paid_at DATETIME NULL,
  expires_at DATETIME NULL,
  note VARCHAR(255) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_orders_user_status (user_id, status),
  INDEX idx_orders_status (status),
  INDEX idx_orders_code (order_code),
  INDEX idx_orders_coupon (coupon_id),
  INDEX idx_orders_created_at (created_at),

  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,

  item_type ENUM('course','point_package','manual') NOT NULL,
  course_id BIGINT UNSIGNED NULL,
  point_package_id BIGINT UNSIGNED NULL,

  title VARCHAR(255) NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,

  unit_price_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,
  total_price_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,

  unit_price_points INT UNSIGNED NOT NULL DEFAULT 0,
  total_price_points INT UNSIGNED NOT NULL DEFAULT 0,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_order_items_order (order_id),
  INDEX idx_order_items_course (course_id),
  INDEX idx_order_items_point_package (point_package_id),

  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id)
    REFERENCES orders(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_order_items_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,

  gateway VARCHAR(100) NOT NULL,
  gateway_authority VARCHAR(255) NULL,
  gateway_ref_id VARCHAR(255) NULL,

  amount_toman BIGINT UNSIGNED NOT NULL,
  status ENUM('created','pending','paid','failed','cancelled','refunded') NOT NULL DEFAULT 'created',

  request_payload_json JSON NULL,
  verify_payload_json JSON NULL,
  callback_payload_json JSON NULL,

  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uq_gateway_authority (gateway, gateway_authority),
  UNIQUE KEY uq_gateway_ref_id (gateway, gateway_ref_id),
  INDEX idx_payments_order (order_id),
  INDEX idx_payments_user (user_id),
  INDEX idx_payments_status (status),

  CONSTRAINT fk_payments_order
    FOREIGN KEY (order_id)
    REFERENCES orders(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_payments_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  gateway VARCHAR(100) NULL,
  event_type VARCHAR(100) NOT NULL,
  payload_json JSON NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_payment_logs_payment (payment_id),
  INDEX idx_payment_logs_order (order_id),
  INDEX idx_payment_logs_event (event_type),

  CONSTRAINT fk_payment_logs_payment
    FOREIGN KEY (payment_id)
    REFERENCES payments(id)
    ON DELETE SET NULL,

  CONSTRAINT fk_payment_logs_order
    FOREIGN KEY (order_id)
    REFERENCES orders(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coupons (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(100) NOT NULL UNIQUE,
  title VARCHAR(255) NULL,
  description TEXT NULL,

  discount_type ENUM('percent','fixed_toman','fixed_points','free') NOT NULL,
  discount_value DECIMAL(12,2) NOT NULL DEFAULT 0.00,

  applies_to ENUM('money','points','both') NOT NULL DEFAULT 'both',
  target_type ENUM('all_courses','selected_courses','selected_users','first_purchase') NOT NULL DEFAULT 'all_courses',

  min_order_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,
  min_order_points INT UNSIGNED NOT NULL DEFAULT 0,
  max_discount_toman BIGINT UNSIGNED NULL,
  max_discount_points INT UNSIGNED NULL,

  usage_limit_total INT UNSIGNED NULL,
  usage_limit_per_user INT UNSIGNED NULL,
  used_count INT UNSIGNED NOT NULL DEFAULT 0,

  starts_at DATETIME NULL,
  expires_at DATETIME NULL,

  status ENUM('active','inactive','expired','archived') NOT NULL DEFAULT 'active',

  created_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_coupons_code (code),
  INDEX idx_coupons_status_dates (status, starts_at, expires_at),
  INDEX idx_coupons_applies_to (applies_to),

  CONSTRAINT fk_coupons_admin
    FOREIGN KEY (created_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coupon_courses (
  coupon_id BIGINT UNSIGNED NOT NULL,
  course_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (coupon_id, course_id),

  CONSTRAINT fk_coupon_courses_coupon
    FOREIGN KEY (coupon_id)
    REFERENCES coupons(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_coupon_courses_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coupon_users (
  coupon_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (coupon_id, user_id),

  CONSTRAINT fk_coupon_users_coupon
    FOREIGN KEY (coupon_id)
    REFERENCES coupons(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_coupon_users_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coupon_redemptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  coupon_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,

  discount_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,
  discount_points INT UNSIGNED NOT NULL DEFAULT 0,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_coupon_order (coupon_id, order_id),
  INDEX idx_coupon_redemptions_user (user_id),
  INDEX idx_coupon_redemptions_coupon_user (coupon_id, user_id),

  CONSTRAINT fk_coupon_redemptions_coupon
    FOREIGN KEY (coupon_id)
    REFERENCES coupons(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_coupon_redemptions_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_coupon_redemptions_order
    FOREIGN KEY (order_id)
    REFERENCES orders(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 7) POINTS & REFERRALS
-- 1 point = 10,000 toman
-- =========================================================

CREATE TABLE IF NOT EXISTS point_packages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  points INT UNSIGNED NOT NULL,
  price_toman BIGINT UNSIGNED NOT NULL,
  bonus_points INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_point_packages_status_sort (status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS point_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,

  type ENUM(
    'purchase',
    'spend_course',
    'referral_start',
    'referral_join_channel',
    'referral_first_purchase',
    'admin_add',
    'admin_subtract',
    'refund',
    'coupon_discount',
    'adjustment',
    'expire'
  ) NOT NULL,

  direction ENUM('credit','debit') NOT NULL,
  points INT NOT NULL,

  balance_before INT NOT NULL DEFAULT 0,
  balance_after INT NOT NULL DEFAULT 0,

  value_toman BIGINT UNSIGNED NOT NULL DEFAULT 0,

  order_id BIGINT UNSIGNED NULL,
  course_id BIGINT UNSIGNED NULL,
  referral_id BIGINT UNSIGNED NULL,
  admin_id BIGINT UNSIGNED NULL,

  description VARCHAR(255) NULL,
  meta_json JSON NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_point_tx_user_created (user_id, created_at),
  INDEX idx_point_tx_type (type),
  INDEX idx_point_tx_order (order_id),
  INDEX idx_point_tx_referral (referral_id),

  CONSTRAINT fk_point_tx_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_point_tx_order
    FOREIGN KEY (order_id)
    REFERENCES orders(id)
    ON DELETE SET NULL,

  CONSTRAINT fk_point_tx_course
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
    ON DELETE SET NULL,

  CONSTRAINT fk_point_tx_admin
    FOREIGN KEY (admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL,

  CONSTRAINT chk_point_tx_points_nonzero CHECK (points <> 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS referrals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referrer_user_id BIGINT UNSIGNED NOT NULL,
  invited_user_id BIGINT UNSIGNED NOT NULL,

  referral_code VARCHAR(32) NOT NULL,
  status ENUM('started','joined_channel','first_purchase','rejected') NOT NULL DEFAULT 'started',

  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  joined_channel_at DATETIME NULL,
  first_purchase_at DATETIME NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY uq_referrals_invited_user (invited_user_id),
  INDEX idx_referrals_referrer (referrer_user_id),
  INDEX idx_referrals_code (referral_code),
  INDEX idx_referrals_status (status),

  CONSTRAINT fk_referrals_referrer
    FOREIGN KEY (referrer_user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_referrals_invited
    FOREIGN KEY (invited_user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS referral_rewards (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referral_id BIGINT UNSIGNED NOT NULL,
  referrer_user_id BIGINT UNSIGNED NOT NULL,
  invited_user_id BIGINT UNSIGNED NOT NULL,

  reward_type ENUM('start','join_channel','first_purchase') NOT NULL,
  points INT UNSIGNED NOT NULL,
  point_transaction_id BIGINT UNSIGNED NULL,

  status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  paid_at DATETIME NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_referral_reward_once (referral_id, reward_type),
  INDEX idx_referral_rewards_referrer (referrer_user_id),
  INDEX idx_referral_rewards_status (status),

  CONSTRAINT fk_referral_rewards_referral
    FOREIGN KEY (referral_id)
    REFERENCES referrals(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_referral_rewards_referrer
    FOREIGN KEY (referrer_user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_referral_rewards_invited
    FOREIGN KEY (invited_user_id)
    REFERENCES users(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_referral_rewards_point_tx
    FOREIGN KEY (point_transaction_id)
    REFERENCES point_transactions(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- بعد از ساخت referrals و referral_rewards، کلید خارجی referral_id را به point_transactions اضافه می‌کنیم
-- چون هر دو جدول به هم وابستگی دارند، برای جلوگیری از خطای ترتیب ساخت، اینجا جداگانه انجام می‌شود.
SET @fk_exists := (
  SELECT COUNT(*)
  FROM information_schema.REFERENTIAL_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND CONSTRAINT_NAME = 'fk_point_tx_referral'
);

SET @sql := IF(@fk_exists = 0,
  'ALTER TABLE point_transactions
   ADD CONSTRAINT fk_point_tx_referral
   FOREIGN KEY (referral_id)
   REFERENCES referrals(id)
   ON DELETE SET NULL',
  'SELECT 1'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =========================================================
-- 8) BROADCASTS
-- Supports text, photo, video, document, audio, mixed JSON
-- =========================================================

CREATE TABLE IF NOT EXISTS broadcasts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,

  message_type ENUM('text','photo','video','document','audio','mixed') NOT NULL DEFAULT 'text',
  text MEDIUMTEXT NULL,
  telegram_file_id VARCHAR(255) NULL,
  caption TEXT NULL,
  media_json JSON NULL,

  target_type ENUM('all_users','active_users','course_students','not_enrolled','custom_users') NOT NULL DEFAULT 'all_users',
  target_course_id BIGINT UNSIGNED NULL,
  target_filter_json JSON NULL,

  status ENUM('draft','scheduled','sending','paused','completed','cancelled','failed') NOT NULL DEFAULT 'draft',

  total_recipients INT UNSIGNED NOT NULL DEFAULT 0,
  sent_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  blocked_count INT UNSIGNED NOT NULL DEFAULT 0,

  scheduled_at DATETIME NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,

  created_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_broadcasts_status (status),
  INDEX idx_broadcasts_scheduled (scheduled_at),
  INDEX idx_broadcasts_target_course (target_course_id),

  CONSTRAINT fk_broadcasts_course
    FOREIGN KEY (target_course_id)
    REFERENCES courses(id)
    ON DELETE SET NULL,

  CONSTRAINT fk_broadcasts_admin
    FOREIGN KEY (created_by_admin_id)
    REFERENCES admins(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS broadcast_recipients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  broadcast_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,

  status ENUM('pending','sent','failed','blocked','skipped') NOT NULL DEFAULT 'pending',
  telegram_message_id BIGINT NULL,
  error_message TEXT NULL,

  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  sent_at DATETIME NULL,
  last_attempt_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_broadcast_user (broadcast_id, user_id),
  INDEX idx_broadcast_recipients_status (broadcast_id, status),
  INDEX idx_broadcast_recipients_user (user_id),

  CONSTRAINT fk_broadcast_recipients_broadcast
    FOREIGN KEY (broadcast_id)
    REFERENCES broadcasts(id)
    ON DELETE CASCADE,

  CONSTRAINT fk_broadcast_recipients_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================================================
-- 9) OPTIONAL INITIAL DATA
-- =========================================================

INSERT INTO point_packages (title, points, price_toman, bonus_points, status, sort_order)
VALUES
('بسته ۱۰ امتیازی', 10, 100000, 0, 'active', 10),
('بسته ۳۰ امتیازی', 30, 300000, 2, 'active', 20),
('بسته ۵۰ امتیازی', 50, 500000, 5, 'active', 30)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- نمونه category اولیه
INSERT INTO categories (title, slug, description, sort_order, status)
VALUES ('عمومی', 'general', 'دسته‌بندی عمومی دوره‌ها', 1, 'active')
ON DUPLICATE KEY UPDATE title = VALUES(title), description = VALUES(description);

-- =========================================================
-- 10) RECOMMENDED VIEWS
-- =========================================================

CREATE OR REPLACE VIEW v_user_point_balances AS
SELECT
  u.id AS user_id,
  u.telegram_id,
  u.username,
  u.points_balance,
  COALESCE(SUM(CASE WHEN pt.direction = 'credit' THEN pt.points ELSE 0 END), 0) AS calculated_credits,
  COALESCE(SUM(CASE WHEN pt.direction = 'debit' THEN ABS(pt.points) ELSE 0 END), 0) AS calculated_debits
FROM users u
LEFT JOIN point_transactions pt ON pt.user_id = u.id
GROUP BY u.id, u.telegram_id, u.username, u.points_balance;

CREATE OR REPLACE VIEW v_course_public AS
SELECT
  c.id,
  c.category_id,
  c.title,
  c.slug,
  c.short_description,
  c.teacher_name,
  c.level,
  c.cover_file_id,
  c.price_toman,
  c.price_points,
  c.is_free,
  c.is_purchasable_with_money,
  c.is_purchasable_with_points,
  c.status,
  c.total_lessons,
  c.total_assets,
  c.duration_minutes,
  c.students_count,
  c.reviews_count,
  c.rating_avg,
  c.published_at
FROM courses c
WHERE c.status = 'published';

-- =========================================================
-- END
-- =========================================================