-- Matrix VPN bot schema (MySQL / InnoDB / utf8mb4)

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
    username VARCHAR(64) NULL,
    first_name VARCHAR(128) NULL,
    coins INT UNSIGNED NOT NULL DEFAULT 0,
    referred_by BIGINT UNSIGNED NULL,          -- telegram_id of inviter
    referral_rewarded TINYINT(1) NOT NULL DEFAULT 0,
    is_verified_join TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS force_join_channels (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    chat_ref VARCHAR(191) NOT NULL,            -- @username or numeric chat id
    title VARCHAR(191) NULL,
    invite_link VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_chat_ref (chat_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS configs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    protocol ENUM('vless','vmess','trojan','ss') NOT NULL,
    raw_original TEXT NOT NULL,
    rebranded TEXT NOT NULL,
    capacity INT UNSIGNED NOT NULL DEFAULT 10,
    used_count INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_active_protocol (is_active, protocol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS config_deliveries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    config_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    delivered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (config_id) REFERENCES configs(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS gift_codes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,
    coins INT UNSIGNED NOT NULL,
    is_used TINYINT(1) NOT NULL DEFAULT 0,
    used_by INT UNSIGNED NULL,
    used_at DATETIME NULL,
    batch_id VARCHAR(64) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-user conversation state for multi-step admin/user forms
CREATE TABLE IF NOT EXISTS states (
    telegram_id BIGINT UNSIGNED PRIMARY KEY,
    state VARCHAR(64) NOT NULL,
    payload TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(64) PRIMARY KEY,
    `value` VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default force-join channel
INSERT IGNORE INTO force_join_channels (chat_ref, title) VALUES ('@MtrxNews', 'MtrxNews');