-- ============================================
-- Steam 2FA Distribution System - Database Schema
-- Version: 1.0.0
-- Compatible: MySQL 5.7+ / MariaDB 10.2+
-- Hosting: Shared Hosting (cPanel)
-- ============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- 1. SYSTEM CONFIGURATION
-- ============================================

CREATE TABLE IF NOT EXISTS `sys_config` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `config_key` VARCHAR(100) UNIQUE NOT NULL,
    `config_value` TEXT,
    `description` VARCHAR(255),
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `sys_config` (`config_key`, `config_value`, `description`) VALUES
('bot_token',              '',        'Telegram Bot Token'),
('admin_telegram_id',     '',        'Admin Telegram User ID'),
('system_status',         'active',  'active or maintenance'),
('daily_code_limit',      '5',       'Max codes per account per 24h'),
('queue_enabled',         '1',       'Enable queue system (1/0)'),
('static_account_user',   '',        'Download account username'),
('static_account_pass',   '',        'Download account password'),
('welcome_message',       'Welcome! Send your Order ID to activate.', 'Bot welcome message'),
('admin_notify_new_user', '1',       'Notify admin on new user activation'),
('admin_notify_override', '1',       'Notify admin on override request');

-- ============================================
-- 2. ORDERS (Activation Keys)
-- ============================================

CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `order_number` VARCHAR(50) UNIQUE NOT NULL,

    `is_used`           TINYINT(1) DEFAULT 0,
    `used_by_user_id`   BIGINT NULL,
    `used_by_username`  VARCHAR(100),
    `used_by_name`      VARCHAR(200),

    `notes`      TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `used_at`    TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,

    INDEX `idx_number`  (`order_number`),
    INDEX `idx_used`    (`is_used`),
    INDEX `idx_user`    (`used_by_user_id`),
    INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. USERS
-- ============================================

CREATE TABLE IF NOT EXISTS `users` (
    `id`          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `telegram_id` BIGINT UNIQUE NOT NULL,

    `username`   VARCHAR(100),
    `first_name` VARCHAR(100),
    `last_name`  VARCHAR(100),

    -- Activation
    `is_active`          TINYINT(1) DEFAULT 0,
    `activation_order`   INT UNSIGNED NULL,
    `activated_at`       TIMESTAMP NULL,

    -- Download confirmation
    `download_confirmed`    TINYINT(1) DEFAULT 0,
    `download_confirmed_at` TIMESTAMP NULL,

    -- Account assignment
    `assigned_account_id` INT UNSIGNED NULL,
    `assigned_at`         TIMESTAMP NULL,

    -- Code status
    `has_received_code`  TINYINT(1) DEFAULT 0,
    `code_received_at`   TIMESTAMP NULL,
    `total_codes_given`  INT UNSIGNED DEFAULT 0,

    -- Queue
    `in_queue`      TINYINT(1) DEFAULT 0,
    `queue_pos`     INT UNSIGNED DEFAULT 0,
    `queue_entered` TIMESTAMP NULL,

    -- Ban
    `is_banned`  TINYINT(1) DEFAULT 0,
    `ban_reason` TEXT,
    `banned_at`  TIMESTAMP NULL,

    `last_seen`  TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (`activation_order`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
    INDEX `idx_telegram`  (`telegram_id`),
    INDEX `idx_active`    (`is_active`),
    INDEX `idx_account`   (`assigned_account_id`),
    INDEX `idx_queue`     (`in_queue`, `queue_pos`),
    INDEX `idx_banned`    (`is_banned`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. STEAM ACCOUNT POOL
-- ============================================

CREATE TABLE IF NOT EXISTS `steam_accounts` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    `account_name`   VARCHAR(100) UNIQUE NOT NULL,
    `shared_secret`  VARCHAR(255) NOT NULL,
    `account_pass`   VARCHAR(255),

    `is_active`   TINYINT(1) DEFAULT 1,
    `is_assigned` TINYINT(1) DEFAULT 0,
    `assigned_to` INT UNSIGNED NULL,
    `assigned_at` TIMESTAMP NULL,

    -- Daily limit tracking
    `daily_limit`      INT UNSIGNED DEFAULT 5,
    `codes_today`      INT UNSIGNED DEFAULT 0,
    `first_code_at`    TIMESTAMP NULL,
    `last_code_at`     TIMESTAMP NULL,
    `total_codes_ever` INT UNSIGNED DEFAULT 0,
    `manual_overrides` INT UNSIGNED DEFAULT 0,

    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_name`     (`account_name`),
    INDEX `idx_active`   (`is_active`),
    INDEX `idx_assigned` (`is_assigned`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. CODE REQUESTS LOG
-- ============================================

CREATE TABLE IF NOT EXISTS `code_requests` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    `user_id`     INT UNSIGNED NOT NULL,
    `account_id`  INT UNSIGNED NOT NULL,
    `code`        VARCHAR(10)  NOT NULL,

    `is_override`       TINYINT(1) DEFAULT 0,
    `override_approved` TINYINT(1) DEFAULT 0,

    `requested_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ip_address`   VARCHAR(45),

    FOREIGN KEY (`user_id`)    REFERENCES `users`(`id`)          ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `steam_accounts`(`id`) ON DELETE CASCADE,
    INDEX `idx_user`    (`user_id`),
    INDEX `idx_account` (`account_id`),
    INDEX `idx_time`    (`requested_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. MANUAL OVERRIDE REQUESTS
-- ============================================

CREATE TABLE IF NOT EXISTS `override_requests` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    `user_id`    INT UNSIGNED NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,

    `reason`      TEXT NOT NULL,
    `status`      ENUM('pending','approved','rejected') DEFAULT 'pending',
    `admin_notes` TEXT,
    `admin_id`    INT UNSIGNED NULL,

    `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `processed_at` TIMESTAMP NULL,

    FOREIGN KEY (`user_id`)    REFERENCES `users`(`id`)          ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `steam_accounts`(`id`) ON DELETE CASCADE,
    INDEX `idx_user`   (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_time`   (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. QUEUE
-- ============================================

CREATE TABLE IF NOT EXISTS `user_queue` (
    `id`       INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `user_id`  INT UNSIGNED NOT NULL UNIQUE,

    `position`       INT UNSIGNED NOT NULL,
    `estimated_at`   TIMESTAMP NULL,
    `status`         ENUM('waiting','ready','served','cancelled') DEFAULT 'waiting',

    `entered_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ready_at`   TIMESTAMP NULL,
    `served_at`  TIMESTAMP NULL,

    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_pos`    (`position`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 8. DOWNLOAD CONFIRMATIONS
-- ============================================

CREATE TABLE IF NOT EXISTS `download_confirmations` (
    `id`           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `user_id`      INT UNSIGNED NOT NULL UNIQUE,
    `confirmed`    TINYINT(1) DEFAULT 0,
    `confirmed_at` TIMESTAMP NULL,
    `ip_address`   VARCHAR(45),

    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 9. ADMINS
-- ============================================

CREATE TABLE IF NOT EXISTS `admins` (
    `id`            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `username`      VARCHAR(50) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `telegram_id`   BIGINT,
    `is_active`     TINYINT(1) DEFAULT 1,
    `last_login`    TIMESTAMP NULL,
    `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX `idx_username`   (`username`),
    INDEX `idx_telegram`   (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default admin: username=admin / password=password
-- IMPORTANT: Use /public/install.php to set a proper password after import!
INSERT IGNORE INTO `admins` (`username`, `password_hash`, `is_active`)
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);

-- ============================================
-- 10. ADMIN SESSIONS
-- ============================================

CREATE TABLE IF NOT EXISTS `admin_sessions` (
    `id`            VARCHAR(128) PRIMARY KEY,
    `admin_id`      INT UNSIGNED NOT NULL,
    `ip_address`    VARCHAR(45),
    `user_agent`    TEXT,
    `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at`    TIMESTAMP NOT NULL,
    `last_activity` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON DELETE CASCADE,
    INDEX `idx_admin`   (`admin_id`),
    INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 11. SYSTEM LOGS
-- ============================================

CREATE TABLE IF NOT EXISTS `sys_logs` (
    `id`         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `level`      ENUM('debug','info','warning','error','critical') DEFAULT 'info',
    `category`   VARCHAR(50),
    `message`    TEXT,
    `context`    JSON,
    `user_id`    BIGINT NULL,
    `ip`         VARCHAR(45),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX `idx_level`    (`level`),
    INDEX `idx_category` (`category`),
    INDEX `idx_time`     (`created_at`),
    INDEX `idx_user`     (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;