-- 乐跑出站代理池 + 全局配置 + lepao_account.assigned_proxy_id
-- MySQL：请使用有 DDL 权限的账号执行；重复执行会使用 INFORMATION_SCHEMA 跳过已存在对象。

CREATE TABLE IF NOT EXISTS lepao_proxy_pool (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    host VARCHAR(128) NOT NULL,
    port INT UNSIGNED NOT NULL,
    scheme VARCHAR(16) NOT NULL DEFAULT 'http',
    is_active TINYINT NOT NULL DEFAULT 1,
    latency_ms INT UNSIGNED DEFAULT NULL,
    last_check_at BIGINT DEFAULT NULL,
    last_error VARCHAR(255) DEFAULT NULL,
    source VARCHAR(32) NOT NULL DEFAULT 'manual',
    remark VARCHAR(255) DEFAULT NULL,
    created_at BIGINT NOT NULL,
    updated_at BIGINT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_host_port (host, port),
    KEY idx_is_active (is_active),
    KEY idx_last_check_at (last_check_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lepao_proxy_global (
    id TINYINT NOT NULL,
    random_proxy_enabled TINYINT NOT NULL DEFAULT 0,
    import_url VARCHAR(1024) NOT NULL,
    probe_target_url VARCHAR(1024) NOT NULL DEFAULT 'https://www.baidu.com',
    check_timeout_ms INT UNSIGNED NOT NULL DEFAULT 8000,
    check_concurrency INT UNSIGNED NOT NULL DEFAULT 10,
    created_at BIGINT NOT NULL,
    updated_at BIGINT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO lepao_proxy_global (
    id,
    random_proxy_enabled,
    import_url,
    probe_target_url,
    check_timeout_ms,
    check_concurrency,
    created_at,
    updated_at
)
SELECT
    1,
    0,
    'http://api.89ip.cn/tqdl.html?api=1&num=60&port=&address=%E9%87%8D%E5%BA%86&isp=',
    'https://www.baidu.com',
    8000,
    10,
    UNIX_TIMESTAMP() * 1000,
    UNIX_TIMESTAMP() * 1000
WHERE NOT EXISTS (
    SELECT 1 FROM lepao_proxy_global WHERE id = 1
);

-- 下面三条为“首次执行”版本，兼容 MySQL 5.7/8.0。
-- 若提示已存在（重复列/索引/外键），可忽略对应报错继续执行后续语句。
ALTER TABLE lepao_account
    ADD COLUMN assigned_proxy_id BIGINT UNSIGNED NULL;

ALTER TABLE lepao_account
    ADD INDEX idx_assigned_proxy_id (assigned_proxy_id);

ALTER TABLE lepao_account
    ADD CONSTRAINT fk_lepao_account_proxy_id
    FOREIGN KEY (assigned_proxy_id)
    REFERENCES lepao_proxy_pool(id)
    ON DELETE SET NULL
    ON UPDATE RESTRICT;