-- =====================================================================
--  03 — FULL CURRENT SCHEMA of the IranJrVPN bot database
--
--  Taken from the real database: the CREATE TABLE statements of the backup
--  backup_2026-09-21_05-32-33.sql (structure only — no rows, no passwords/tokens).
--  Use it to build the database from scratch (empty database → run this file → done).
--  01_install.sql is NOT needed in that case. Safe to re-run: IF NOT EXISTS everywhere.
--
--  After it, in the bot: set the owner (OWNER_TG_ID in config.php + open install.php once, or
--  INSERT INTO admins), then add servers / plans / cards from the admin panel.
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
--  users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tg_id` bigint(20) unsigned NOT NULL,
  `username` varchar(64) DEFAULT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `balance` bigint(20) NOT NULL DEFAULT 0,
  `ref_by` int(10) unsigned DEFAULT NULL,
  `is_banned` tinyint(1) NOT NULL DEFAULT 0,
  `blocked_bot` tinyint(1) NOT NULL DEFAULT 0,
  `screen_message_id` bigint(20) DEFAULT NULL,
  `menu_message_id` bigint(20) DEFAULT NULL,
  `used_free_test` tinyint(1) NOT NULL DEFAULT 0,
  `free_test_used_count` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `tg_id` (`tg_id`),
  KEY `ref_by` (`ref_by`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`ref_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  admins
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admins` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tg_id` bigint(20) unsigned NOT NULL,
  `role` enum('owner','manager','support') NOT NULL DEFAULT 'support',
  `added_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `tg_id` (`tg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  settings
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key` varchar(64) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  menu_buttons
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `menu_buttons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `button_key` varchar(32) NOT NULL,
  `label` varchar(64) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `button_key` (`button_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  categories
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(64) NOT NULL,
  `pg_group_ids` varchar(255) DEFAULT NULL,
  `server_id` int(10) unsigned DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  servers
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `servers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) NOT NULL,
  `auth_type` varchar(10) NOT NULL DEFAULT 'userpass',
  `name` varchar(100) NOT NULL,
  `base_url` varchar(255) NOT NULL,
  `sub_domain` varchar(255) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `api_token` varchar(500) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  durations
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `durations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `title` varchar(32) NOT NULL,
  `days` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `durations_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  plans
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `plans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `duration_id` int(10) unsigned NOT NULL,
  `gb` int(11) NOT NULL,
  `price` bigint(20) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `duration_id` (`duration_id`),
  CONSTRAINT `plans_ibfk_1` FOREIGN KEY (`duration_id`) REFERENCES `durations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  force_join_channels
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `force_join_channels` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `chat_id` varchar(64) NOT NULL,
  `title` varchar(128) DEFAULT NULL,
  `invite_link` varchar(255) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  service_names
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `service_names` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_name` (`user_id`,`name`),
  KEY `idx_name` (`name`),
  CONSTRAINT `service_names_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  orders
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `plan_id` int(10) unsigned DEFAULT NULL,
  `server_id` int(10) unsigned DEFAULT NULL,
  `service_name` varchar(40) NOT NULL,
  `amount` bigint(20) NOT NULL,
  `pay_method` enum('wallet','card','linked') NOT NULL DEFAULT 'wallet',
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `receipt_file_id` varchar(255) DEFAULT NULL,
  `sub_link` text DEFAULT NULL,
  `admin_msg_id` bigint(20) DEFAULT NULL,
  `reviewed_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `reviewed_at` datetime DEFAULT NULL,
  `reject_reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `plan_id` (`plan_id`),
  KEY `server_id` (`server_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`plan_id`) REFERENCES `plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  wallet_charges
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallet_charges` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `amount` bigint(20) NOT NULL,
  `receipt_file_id` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `admin_msg_id` bigint(20) DEFAULT NULL,
  `reviewed_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `reviewed_at` datetime DEFAULT NULL,
  `reject_reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `wallet_charges_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  transactions
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `type` enum('charge','purchase','referral_commission','admin_adjust') NOT NULL,
  `amount` bigint(20) NOT NULL,
  `balance_after` bigint(20) NOT NULL,
  `meta` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  user_state
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_state` (
  `tg_id` bigint(20) unsigned NOT NULL,
  `step` varchar(64) NOT NULL,
  `data` text DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`tg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  admin_activity_log
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_activity_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `admin_tg_id` bigint(20) unsigned NOT NULL,
  `action` varchar(100) NOT NULL,
  `details` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  help_items
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `help_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `root` enum('guide','download') NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `kind` enum('folder','post','link') NOT NULL,
  `title` varchar(100) NOT NULL,
  `url` varchar(500) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `idx_tree` (`root`,`parent_id`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  discount_codes
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `discount_codes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `type` enum('percent','fixed') NOT NULL DEFAULT 'percent',
  `value` int(10) unsigned NOT NULL,
  `plan_id` int(10) unsigned DEFAULT NULL,
  `max_uses` int(10) unsigned NOT NULL DEFAULT 0,
  `used_count` int(10) unsigned NOT NULL DEFAULT 0,
  `expires_at` datetime DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  discount_redemptions
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `discount_redemptions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `order_id` int(10) unsigned DEFAULT NULL,
  `amount_off` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_user` (`code_id`,`user_id`),
  KEY `idx_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ---------------------------------------------------------------------
--  payment_cards
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card_number` varchar(32) NOT NULL,
  `card_holder` varchar(100) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- Main-menu buttons (INSERT IGNORE: never overwrites a label that already exists)
INSERT IGNORE INTO `menu_buttons` (`button_key`, `label`, `enabled`, `sort_order`) VALUES
    ('buy',        'خرید اشتراک 🛒',        1, 1),
    ('charge',     'کیف پول 💰',            1, 2),
    ('link_sub',   '🔗 اتصال اشتراک',       1, 3),
    ('free_test',  'تست رایگان 🧪',         1, 4),
    ('plans_info', 'انواع اشتراک 📚',        1, 5),
    ('account',    'حساب کاربری 👤',        1, 6),
    ('referral',   'دعوت دوستان 🎁',        1, 7),
    ('support',    'پشتیبانی 💬',           1, 8),
    ('about',      '🌟 درباره سازنده',      1, 9),
    ('reseller',   'درخواست نمایندگی 🤝',   0, 50),
    ('tariffs',    'تعرفه‌ها 💵',           0, 50);