-- Database Schema Update for Telegram Tour Bot Upgrade
-- Integrate payment gateways settings for leaders, wallet system, and bot chats management

-- 1. Add Gateway Settings to explorers table
ALTER TABLE `explorers`
ADD COLUMN `gateway_zarinpal` TINYINT DEFAULT 1,
ADD COLUMN `gateway_card` TINYINT DEFAULT 1,
ADD COLUMN `gateway_crypto` TINYINT DEFAULT 1,
ADD COLUMN `gateway_stars` TINYINT DEFAULT 1,
ADD COLUMN `gateway_wallet` TINYINT DEFAULT 1;

-- 2. Create wallets table if not exists (to support wallet balance)
CREATE TABLE IF NOT EXISTS `wallets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT NOT NULL UNIQUE,
  `available_toman` DECIMAL(15, 2) DEFAULT 0.00,
  `available_star` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Create wallet_transactions table if not exists
CREATE TABLE IF NOT EXISTS `wallet_transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT NOT NULL,
  `amount` DECIMAL(15, 2) NOT NULL,
  `currency` ENUM('toman', 'star') DEFAULT 'toman',
  `status` ENUM('pending', 'available', 'success', 'failed', 'completed') DEFAULT 'pending',
  `is_withdrawable` TINYINT DEFAULT 1,
  `description` TEXT,
  `tour_id` INT DEFAULT NULL,
  `by_admin` TINYINT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Create leader_chats table to track groups and channels the bot has been added to
CREATE TABLE IF NOT EXISTS `leader_chats` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `chat_id` BIGINT NOT NULL UNIQUE,
  `leader_id` BIGINT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `username` VARCHAR(255) DEFAULT NULL,
  `type` ENUM('group', 'channel') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
