-- =====================================================================
--  02 — UPGRADE: turns a database made with 01_install.sql into the CURRENT schema
--
--  Every definition below is copied from the real database (the backup
--  backup_2026-09-21_05-32-33.sql). Run it in phpMyAdmin → SQL tab after 01_install.sql.
--  Safe to run on a database that already has some/all of it: every statement first checks
--  (CREATE TABLE IF NOT EXISTS / ADD COLUMN, INDEX, KEY only when missing / INSERT IGNORE).
--  Only structure plus a few default rows (INSERT IGNORE) and the free-test/card carry-over of step 3 — nothing of yours is overwritten.
-- =====================================================================
SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
--  1) Tables that 01_install.sql does not have
-- ---------------------------------------------------------------------

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;

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;

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;

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;

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;

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;

-- ---------------------------------------------------------------------
--  2) Columns / indexes / keys added to the tables of 01_install.sql
-- ---------------------------------------------------------------------

-- users.blocked_bot
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `users` ADD COLUMN `blocked_bot` tinyint(1) NOT NULL DEFAULT 0 AFTER `is_banned`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'blocked_bot');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- users.screen_message_id
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `users` ADD COLUMN `screen_message_id` bigint(20) DEFAULT NULL AFTER `blocked_bot`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'screen_message_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- users.menu_message_id
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `users` ADD COLUMN `menu_message_id` bigint(20) DEFAULT NULL AFTER `screen_message_id`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'menu_message_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- users.free_test_used_count
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `users` ADD COLUMN `free_test_used_count` int(11) NOT NULL DEFAULT 0 AFTER `used_free_test`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'free_test_used_count');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- categories.server_id
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `categories` ADD COLUMN `server_id` int(10) unsigned DEFAULT NULL AFTER `pg_group_ids`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'categories' AND COLUMN_NAME = 'server_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- orders.server_id
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `orders` ADD COLUMN `server_id` int(10) unsigned DEFAULT NULL AFTER `plan_id`', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'server_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- orders.reject_reason
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `orders` ADD COLUMN `reject_reason` varchar(255) DEFAULT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'reject_reason');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- wallet_charges.reject_reason
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `wallet_charges` ADD COLUMN `reject_reason` varchar(255) DEFAULT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'wallet_charges' AND COLUMN_NAME = 'reject_reason');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- orders index server_id
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `orders` ADD KEY `server_id` (`server_id`)', 'SELECT 1')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND INDEX_NAME = 'server_id');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- orders foreign key orders_ibfk_3
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `orders` ADD CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON DELETE SET NULL', 'SELECT 1')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND CONSTRAINT_NAME = 'orders_ibfk_3' AND CONSTRAINT_TYPE = 'FOREIGN KEY');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- service_names index idx_name
SET @s = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE `service_names` ADD KEY `idx_name` (`name`)', 'SELECT 1')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'service_names' AND INDEX_NAME = 'idx_name');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- orders.pay_method gets 'linked' («🔗 اتصال اشتراک»)
SET @s = (SELECT IF(COUNT(*) = 1,
    'ALTER TABLE `orders` MODIFY `pay_method` enum(''wallet'',''card'',''linked'') NOT NULL DEFAULT ''wallet''',
    'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'pay_method'
    AND COLUMN_TYPE NOT LIKE '%linked%');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------------------------------------------------------------------
--  3) Data
-- ---------------------------------------------------------------------
-- the old yes/no flag «used_free_test» became a counter (free_test_used_count) — carry the old value over
UPDATE `users` SET `free_test_used_count` = 1 WHERE `used_free_test` = 1 AND `free_test_used_count` = 0;

-- Default settings that migrations 08 and 13 added (INSERT IGNORE = keeps whatever you already set)
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('tariffs_text', 'تعرفه‌های ما به‌زودی اینجا اعلام می‌شود.'),
    ('free_test_mb', '1024'),
    ('free_test_hours', '24'),
    ('free_test_max_count', '1');

-- Carry the single card of the old settings (card_number / card_holder) over as the first row of payment_cards
-- (only when the table is still empty) — same as migration_12
SET @old_number = (SELECT `setting_value` FROM `settings` WHERE `setting_key` = 'card_number' LIMIT 1);
SET @old_holder = (SELECT `setting_value` FROM `settings` WHERE `setting_key` = 'card_holder' LIMIT 1);
INSERT INTO `payment_cards` (`card_number`, `card_holder`, `enabled`, `sort_order`)
SELECT @old_number, COALESCE(@old_holder, ''), 1, 0
WHERE @old_number IS NOT NULL AND @old_number <> '' AND @old_number <> '6037-XXXX-XXXX-XXXX'
  AND NOT EXISTS (SELECT 1 FROM `payment_cards`);

-- Main-menu buttons added after the first install (INSERT IGNORE: never overwrites a label you edited)
INSERT IGNORE INTO `menu_buttons` (`button_key`, `label`, `enabled`, `sort_order`) VALUES
    ('charge',    'کیف پول 💰',        1, 2),
    ('link_sub',  '🔗 اتصال اشتراک',   1, 3),
    ('about',     '🌟 درباره سازنده',  1, 9),
    ('tariffs',   'تعرفه‌ها 💵',       0, 50);