-- Migration for existing tg-forwarder databases created before the multi-pair/filter update.
-- Run this after the code upgrade (it is idempotent and safe to re-run).

-- pending_session holds the StringSession captured right after send_code_request,
-- so sign_in can be completed even if the Passenger worker recycled between the
-- two requests (the temp client lives in memory otherwise).
ALTER TABLE tg_accounts
    ADD COLUMN IF NOT EXISTS pending_session TEXT;

ALTER TABLE channel_pairs
    ADD COLUMN IF NOT EXISTS enabled BOOLEAN DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS include_text BOOLEAN DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS include_media BOOLEAN DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS skip_standalone_media BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS forward_as_link BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS price_augment BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS forward_via_bot BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS augment_symbol VARCHAR(32) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS filter_type ENUM('none','text_only','media_only') DEFAULT 'none',
    ADD COLUMN IF NOT EXISTS llm_enabled BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS account_id INT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS bot_token_id INT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Migrate old is_active flag into the new enabled column, then drop it.
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
                   WHERE table_schema = DATABASE()
                   AND table_name = 'channel_pairs'
                   AND column_name = 'is_active');
SET @sql = IF(@col_exists > 0, 'UPDATE channel_pairs SET enabled = is_active WHERE is_active IS NOT NULL', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ALTER TABLE channel_pairs DROP COLUMN IF EXISTS is_active;

ALTER TABLE message_map
    ADD COLUMN IF NOT EXISTS via_bot BOOLEAN DEFAULT FALSE,
    ADD INDEX IF NOT EXISTS idx_lookup (source_chat_id, dest_chat_id, source_msg_id),
    ADD INDEX IF NOT EXISTS idx_created (created_at);

DROP TABLE IF EXISTS auth_state;
CREATE TABLE 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 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)
);

-- ── cTrader encrypted token vault ───────────────────────────────────────────
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 '',
    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)
);

ALTER TABLE ctrader_accounts
    ADD COLUMN IF NOT EXISTS balance_cents BIGINT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS money_digits INT DEFAULT 2,
    ADD COLUMN IF NOT EXISTS leverage INT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS account_type VARCHAR(32) DEFAULT '',
    ADD COLUMN IF NOT EXISTS account_status VARCHAR(32) DEFAULT '';