CREATE TABLE IF NOT EXISTS config_vars (
    var_key VARCHAR(128) PRIMARY KEY,
    var_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ── Telegram accounts (multi-account support) ───────────────────────────────
CREATE TABLE IF NOT EXISTS tg_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    phone VARCHAR(32) DEFAULT '',
    session_string TEXT,
    is_primary BOOLEAN DEFAULT FALSE,
    is_authorized BOOLEAN DEFAULT FALSE,
    status VARCHAR(32) DEFAULT 'pending',
    qr_token BINARY(16) DEFAULT NULL,
    qr_expires_at TIMESTAMP NULL DEFAULT NULL,
    phone_code_hash VARCHAR(128) DEFAULT '',
    pending_session TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ── Bot tokens for forwarding ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bot_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token_hash VARCHAR(64) NOT NULL UNIQUE,
    token_encrypted TEXT NOT NULL,
    bot_name VARCHAR(256) DEFAULT '',
    bot_username VARCHAR(128) DEFAULT '',
    is_active BOOLEAN DEFAULT TRUE,
    verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS channel_pairs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source_chat_id BIGINT NOT NULL,
    dest_chat_id BIGINT NOT NULL,
    source_title VARCHAR(512),
    dest_title VARCHAR(512),
    enabled BOOLEAN DEFAULT TRUE,
    include_text BOOLEAN DEFAULT TRUE,
    include_media BOOLEAN DEFAULT TRUE,
    skip_standalone_media BOOLEAN DEFAULT FALSE,
    forward_as_link BOOLEAN DEFAULT FALSE,
    price_augment BOOLEAN DEFAULT FALSE,
    forward_via_bot BOOLEAN DEFAULT FALSE,
    augment_symbol VARCHAR(32) DEFAULT NULL,
    filter_type ENUM('none','text_only','media_only') DEFAULT 'none',
    llm_enabled BOOLEAN DEFAULT FALSE,
    account_id INT DEFAULT NULL,
    bot_token_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_pair (source_chat_id, dest_chat_id)
);

CREATE TABLE IF NOT EXISTS message_map (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source_chat_id BIGINT NOT NULL,
    source_msg_id BIGINT NOT NULL,
    dest_chat_id BIGINT NOT NULL,
    dest_msg_id BIGINT NOT NULL,
    via_bot BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_msg (source_chat_id, source_msg_id, dest_chat_id),
    KEY idx_lookup (source_chat_id, dest_chat_id, source_msg_id),
    KEY idx_created (created_at)
);

CREATE TABLE IF NOT EXISTS auth_state (
    id INT PRIMARY KEY CHECK (id = 1),
    phone VARCHAR(32),
    code_hash VARCHAR(128),
    status VARCHAR(32) DEFAULT 'pending',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ctrader_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    user_id BIGINT DEFAULT NULL,
    broker_name VARCHAR(128) DEFAULT '',
    broker_title VARCHAR(128) DEFAULT '',
    account_number BIGINT DEFAULT NULL,
    is_live BOOLEAN DEFAULT FALSE,
    deposit_currency VARCHAR(8) DEFAULT '',
    balance_cents BIGINT DEFAULT NULL,
    money_digits INT DEFAULT 2,
    leverage INT DEFAULT NULL,
    account_type VARCHAR(32) DEFAULT '',
    account_status VARCHAR(32) DEFAULT '',
    encrypted_payload TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_account (account_id)
);

CREATE TABLE IF NOT EXISTS signal_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    source_chat_id BIGINT NOT NULL,
    source_message_id BIGINT NOT NULL,
    signal_text TEXT,
    signal_date INT,
    reply_to_message_id BIGINT,
    received_at_ms BIGINT NOT NULL,
    price_fetched_at_ms BIGINT,
    symbol VARCHAR(32) NOT NULL,
    symbol_id BIGINT,
    bid DECIMAL(18, 5),
    ask DECIMAL(18, 5),
    spread DECIMAL(18, 5),
    tick_timestamp_ms BIGINT,
    price_available BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_signal (source_chat_id, source_message_id),
    KEY idx_created (created_at),
    KEY idx_source_msg (source_chat_id, source_message_id)
);