-- PermiStar — Core schema (MySQL 8 / MariaDB 10.4+)
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` VARCHAR(255) NOT NULL,
  `batch` INT UNSIGNED NOT NULL DEFAULT 1,
  `executed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_migrations_name` (`migration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `website_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(120) NOT NULL,
  `value` MEDIUMTEXT NULL,
  `group` VARCHAR(60) NOT NULL DEFAULT 'general',
  `is_secret` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_setting_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(128) NOT NULL,
  `data` MEDIUMTEXT NULL,
  `ip` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `user_id` INT UNSIGNED NULL,
  `admin_id` INT UNSIGNED NULL,
  `last_activity` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sessions_last` (`last_activity`),
  KEY `idx_sessions_user` (`user_id`),
  KEY `idx_sessions_admin` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(80) NOT NULL,
  `last_name` VARCHAR(80) NOT NULL,
  `mobile` VARCHAR(20) NOT NULL,
  `email` VARCHAR(160) NULL,
  `password` VARCHAR(255) NOT NULL,
  `telegram_username` VARCHAR(80) NULL,
  `telegram_chat_id` VARCHAR(64) NULL,
  `status` ENUM('active','blocked') NOT NULL DEFAULT 'active',
  `terms_accepted_at` DATETIME NULL,
  `session_version` INT UNSIGNED NOT NULL DEFAULT 1,
  `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `last_login_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_mobile` (`mobile`),
  KEY `idx_users_status` (`status`),
  KEY `idx_users_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admin_roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(60) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `description` VARCHAR(255) NULL,
  `permissions` MEDIUMTEXT NULL,
  `is_system` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(90) NOT NULL,
  `name` VARCHAR(160) NOT NULL,
  `group` VARCHAR(60) NOT NULL DEFAULT 'general',
  `critical` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permission_key` (`key`),
  KEY `idx_perm_group` (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` INT UNSIGNED NULL,
  `first_name` VARCHAR(80) NOT NULL,
  `last_name` VARCHAR(80) NOT NULL,
  `mobile` VARCHAR(20) NOT NULL,
  `email` VARCHAR(160) NULL,
  `password` VARCHAR(255) NOT NULL,
  `status` ENUM('active','disabled') NOT NULL DEFAULT 'active',
  `permissions` MEDIUMTEXT NULL,
  `failed_attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `locked_until` DATETIME NULL,
  `session_version` INT UNSIGNED NOT NULL DEFAULT 1,
  `last_login_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admins_mobile` (`mobile`),
  KEY `idx_admins_role` (`role_id`),
  CONSTRAINT `fk_admins_role` FOREIGN KEY (`role_id`) REFERENCES `admin_roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(140) NOT NULL,
  `slug` VARCHAR(160) NOT NULL,
  `description` TEXT NULL,
  `icon` VARCHAR(60) NULL,
  `image` VARCHAR(255) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `meta_title` VARCHAR(180) NULL,
  `meta_description` VARCHAR(300) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cat_slug` (`slug`),
  KEY `idx_cat_status_sort` (`status`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NULL,
  `title` VARCHAR(190) NOT NULL,
  `slug` VARCHAR(200) NOT NULL,
  `subtitle` VARCHAR(255) NULL,
  `description` MEDIUMTEXT NULL,
  `image` VARCHAR(255) NULL,
  `price_toman` INT UNSIGNED NOT NULL DEFAULT 0,
  `compare_price_toman` INT UNSIGNED NULL,
  `availability` ENUM('in_stock','limited','out_of_stock') NOT NULL DEFAULT 'in_stock',
  `stock_qty` INT NULL DEFAULT NULL,
  `min_qty` INT UNSIGNED NOT NULL DEFAULT 1,
  `max_qty` INT UNSIGNED NOT NULL DEFAULT 10,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `delivery_method` ENUM('manual','auto') NOT NULL DEFAULT 'manual',
  `delivery_note` VARCHAR(255) NULL,
  `views` INT UNSIGNED NOT NULL DEFAULT 0,
  `sales_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `meta_title` VARCHAR(180) NULL,
  `meta_description` VARCHAR(300) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_product_slug` (`slug`),
  KEY `idx_product_cat` (`category_id`),
  KEY `idx_product_active_sort` (`is_active`,`sort_order`),
  KEY `idx_product_featured` (`is_featured`),
  CONSTRAINT `fk_product_category` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `discount_codes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(60) NOT NULL,
  `type` ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
  `value` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `max_discount_toman` INT UNSIGNED NULL DEFAULT NULL,
  `min_order_toman` INT UNSIGNED NOT NULL DEFAULT 0,
  `usage_limit` INT UNSIGNED NULL DEFAULT NULL,
  `used_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `per_user_limit` INT UNSIGNED NOT NULL DEFAULT 1,
  `user_id` INT UNSIGNED NULL DEFAULT NULL,
  `product_id` INT UNSIGNED NULL DEFAULT NULL,
  `category_id` INT UNSIGNED NULL DEFAULT NULL,
  `starts_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_discount_code` (`code`),
  KEY `idx_discount_status` (`status`,`expires_at`),
  KEY `idx_discount_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no` VARCHAR(32) NOT NULL,
  `user_id` INT UNSIGNED NULL,
  `guest_mobile` VARCHAR(20) NULL,
  `first_name` VARCHAR(80) NULL,
  `last_name` VARCHAR(80) NULL,
  `telegram_username` VARCHAR(80) NULL,
  `telegram_chat_id` VARCHAR(64) NULL,
  `subtotal_toman` INT UNSIGNED NOT NULL DEFAULT 0,
  `discount_code` VARCHAR(60) NULL,
  `discount_toman` INT UNSIGNED NOT NULL DEFAULT 0,
  `total_toman` INT UNSIGNED NOT NULL DEFAULT 0,
  `paid_toman` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('pending','paid','processing','completed','cancelled','failed','refunded') NOT NULL DEFAULT 'pending',
  `delivery_status` ENUM('pending','processing','delivered','failed','manual_required') NOT NULL DEFAULT 'pending',
  `delivery_method` ENUM('manual','auto') NOT NULL DEFAULT 'manual',
  `payment_status` ENUM('unpaid','paid','failed','refunded') NOT NULL DEFAULT 'unpaid',
  `note` VARCHAR(500) NULL,
  `admin_note` VARCHAR(500) NULL,
  `ip` VARCHAR(45) NULL,
  `paid_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_order_no` (`order_no`),
  KEY `idx_orders_user` (`user_id`),
  KEY `idx_orders_status` (`status`),
  KEY `idx_orders_delivery` (`delivery_status`),
  KEY `idx_orders_created` (`created_at`),
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NULL,
  `title` VARCHAR(190) NOT NULL,
  `category_name` VARCHAR(140) NULL,
  `qty` INT UNSIGNED NOT NULL DEFAULT 1,
  `price_toman` INT UNSIGNED NOT NULL,
  `line_total_toman` INT UNSIGNED NOT NULL,
  `delivery_status` ENUM('pending','processing','delivered','failed') NOT NULL DEFAULT 'pending',
  `delivery_data` TEXT NULL,
  `delivered_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_items_order` (`order_id`),
  KEY `idx_items_product` (`product_id`),
  CONSTRAINT `fk_items_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NULL,
  `user_id` INT UNSIGNED NULL,
  `gateway` VARCHAR(40) NOT NULL DEFAULT 'zarinpal',
  `mode` ENUM('sandbox','production') NOT NULL DEFAULT 'production',
  `authority` VARCHAR(80) NULL,
  `ref_id` VARCHAR(80) NULL,
  `card_pan` VARCHAR(32) NULL,
  `amount` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('initiated','pending','success','failed','cancelled','duplicate') NOT NULL DEFAULT 'initiated',
  `gateway_code` INT NULL,
  `gateway_message` VARCHAR(255) NULL,
  `callback_ip` VARCHAR(45) NULL,
  `verified_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_authority` (`authority`),
  KEY `idx_payment_ref` (`ref_id`),
  KEY `idx_payment_order` (`order_id`),
  KEY `idx_payment_user` (`user_id`),
  KEY `idx_payment_status` (`status`),
  CONSTRAINT `fk_payment_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_id` INT UNSIGNED NULL,
  `order_id` INT UNSIGNED NULL,
  `type` VARCHAR(40) NOT NULL DEFAULT 'payment_request',
  `amount` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `reference` VARCHAR(120) NULL,
  `meta` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tx_payment` (`payment_id`),
  KEY `idx_tx_order` (`order_id`),
  KEY `idx_tx_type` (`type`),
  CONSTRAINT `fk_tx_payment` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tx_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `delivery_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` INT UNSIGNED NOT NULL,
  `order_item_id` INT UNSIGNED NULL,
  `provider` VARCHAR(60) NOT NULL DEFAULT 'manual',
  `action` VARCHAR(80) NOT NULL,
  `status` ENUM('success','failed','info') NOT NULL DEFAULT 'info',
  `message` VARCHAR(500) NULL,
  `payload` MEDIUMTEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_delivery_order` (`order_id`),
  CONSTRAINT `fk_delivery_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `discount_usages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `discount_code_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NULL,
  `order_id` INT UNSIGNED NULL,
  `amount` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_usage_code` (`discount_code_id`),
  KEY `idx_usage_user` (`user_id`),
  CONSTRAINT `fk_usage_code` FOREIGN KEY (`discount_code_id`) REFERENCES `discount_codes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_usage_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 `tickets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_no` VARCHAR(24) NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `subject` VARCHAR(190) NOT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'general',
  `priority` ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status` ENUM('open','in_progress','waiting_customer','answered','closed') NOT NULL DEFAULT 'open',
  `assigned_admin_id` INT UNSIGNED NULL,
  `related_order_id` INT UNSIGNED NULL,
  `last_reply_at` DATETIME NULL,
  `closed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ticket_no` (`ticket_no`),
  KEY `idx_ticket_user` (`user_id`),
  KEY `idx_ticket_status` (`status`),
  KEY `idx_ticket_assigned` (`assigned_admin_id`),
  CONSTRAINT `fk_ticket_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 `ticket_messages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` INT UNSIGNED NOT NULL,
  `sender_type` ENUM('user','admin','system') NOT NULL DEFAULT 'user',
  `sender_id` INT UNSIGNED NULL,
  `sender_name` VARCHAR(120) NULL,
  `body` MEDIUMTEXT NOT NULL,
  `is_internal` TINYINT(1) NOT NULL DEFAULT 0,
  `ip` VARCHAR(45) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_msg_ticket` (`ticket_id`,`created_at`),
  CONSTRAINT `fk_msg_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ticket_attachments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` INT UNSIGNED NOT NULL,
  `message_id` INT UNSIGNED NULL,
  `file_name` VARCHAR(190) NOT NULL,
  `stored_name` VARCHAR(190) NOT NULL,
  `mime` VARCHAR(120) NULL,
  `size` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_att_ticket` (`ticket_id`),
  CONSTRAINT `fk_att_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(190) NOT NULL,
  `body` MEDIUMTEXT NULL,
  `type` ENUM('info','success','warning','important') NOT NULL DEFAULT 'info',
  `priority` INT NOT NULL DEFAULT 0,
  `show_bar` TINYINT(1) NOT NULL DEFAULT 1,
  `show_popup` TINYINT(1) NOT NULL DEFAULT 0,
  `show_dashboard` TINYINT(1) NOT NULL DEFAULT 1,
  `starts_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ann_status` (`status`,`priority`,`starts_at`,`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NULL,
  `admin_id` INT UNSIGNED NULL,
  `title` VARCHAR(190) NOT NULL,
  `body` TEXT NULL,
  `type` VARCHAR(40) NOT NULL DEFAULT 'info',
  `link` VARCHAR(255) NULL,
  `read_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notif_user` (`user_id`,`read_at`),
  KEY `idx_notif_admin` (`admin_id`,`read_at`),
  CONSTRAINT `fk_notif_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 `sms_otps` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone` VARCHAR(20) NOT NULL,
  `code_hash` VARCHAR(255) NOT NULL,
  `purpose` ENUM('register','login','reset','change') NOT NULL DEFAULT 'register',
  `expires_at` DATETIME NOT NULL,
  `attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `used` TINYINT(1) NOT NULL DEFAULT 0,
  `consumed_at` DATETIME NULL,
  `ip` VARCHAR(45) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_otp_phone` (`phone`,`used`,`expires_at`),
  KEY `idx_otp_ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `login_history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NULL,
  `admin_id` INT UNSIGNED NULL,
  `mobile` VARCHAR(20) NULL,
  `ip` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `success` TINYINT(1) NOT NULL DEFAULT 1,
  `note` VARCHAR(190) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_login_user` (`user_id`,`created_at`),
  KEY `idx_login_admin` (`admin_id`,`created_at`),
  KEY `idx_login_ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` INT UNSIGNED NULL,
  `user_id` INT UNSIGNED NULL,
  `action` VARCHAR(90) NOT NULL,
  `description` VARCHAR(1000) NULL,
  `ip` VARCHAR(45) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_activity_admin` (`admin_id`,`created_at`),
  KEY `idx_activity_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `security_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(60) NOT NULL,
  `severity` ENUM('info','warning','critical') NOT NULL DEFAULT 'warning',
  `message` VARCHAR(500) NULL,
  `ip` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `admin_id` INT UNSIGNED NULL,
  `user_id` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sec_type` (`type`,`created_at`),
  KEY `idx_sec_ip` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payment_settings` (
  `id` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `gateway` VARCHAR(40) NOT NULL DEFAULT 'zarinpal',
  `merchant_id` VARCHAR(80) NULL,
  `sandbox` TINYINT(1) NOT NULL DEFAULT 0,
  `callback_url` VARCHAR(255) NULL,
  `description_template` VARCHAR(255) NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sms_settings` (
  `id` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `provider` VARCHAR(40) NOT NULL DEFAULT 'melipayamak',
  `username` VARCHAR(120) NULL,
  `password` VARCHAR(190) NULL,
  `sender` VARCHAR(60) NULL,
  `otp_pattern_id` VARCHAR(60) NULL,
  `otp_ttl_minutes` INT UNSIGNED NOT NULL DEFAULT 10,
  `otp_max_attempts` INT UNSIGNED NOT NULL DEFAULT 5,
  `resend_cooldown_seconds` INT UNSIGNED NOT NULL DEFAULT 120,
  `ip_daily_limit` INT UNSIGNED NOT NULL DEFAULT 20,
  `phone_daily_limit` INT UNSIGNED NOT NULL DEFAULT 8,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `test_mode` TINYINT(1) NOT NULL DEFAULT 0,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `faqs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `question` VARCHAR(255) NOT NULL,
  `answer` MEDIUMTEXT NOT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'general',
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_faq_status` (`status`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(160) NOT NULL,
  `title` VARCHAR(190) NOT NULL,
  `body` MEDIUMTEXT NULL,
  `meta_title` VARCHAR(180) NULL,
  `meta_description` VARCHAR(300) NULL,
  `status` ENUM('published','draft') NOT NULL DEFAULT 'published',
  `updated_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_page_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `mobile` VARCHAR(20) NULL,
  `email` VARCHAR(160) NULL,
  `subject` VARCHAR(190) NULL,
  `body` MEDIUMTEXT NOT NULL,
  `ip` VARCHAR(45) NULL,
  `status` ENUM('new','read','replied','archived') NOT NULL DEFAULT 'new',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_contact_status` (`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `backups` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(190) NOT NULL,
  `size` INT UNSIGNED NOT NULL DEFAULT 0,
  `type` ENUM('database','files','full') NOT NULL DEFAULT 'database',
  `created_by` INT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_backup_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;