-- 代理池管理系统数据库初始化脚本

-- 创建枚举类型
CREATE TYPE proxy_protocol AS ENUM ('http', 'https', 'socks5');
CREATE TYPE proxy_status AS ENUM ('unknown', 'alive', 'dead');

-- 代理主表
CREATE TABLE proxies (
    id            uuid PRIMARY KEY,

    protocol      proxy_protocol NOT NULL,
    host          text NOT NULL,
    port          int NOT NULL CHECK (port > 0 AND port < 65536),
    username      text NOT NULL DEFAULT '',
    password      text NOT NULL DEFAULT '',

    "group"       text NOT NULL DEFAULT 'default',
    tags          text[] NOT NULL DEFAULT ARRAY[]::text[],

    status        proxy_status NOT NULL DEFAULT 'unknown',
    score         int NOT NULL DEFAULT 0,
    latency_ms    bigint NOT NULL DEFAULT 0,
    last_check_at timestamptz,

    fail_count    int NOT NULL DEFAULT 0,
    success_count int NOT NULL DEFAULT 0,

    disabled      boolean NOT NULL DEFAULT false,

    created_at    timestamptz NOT NULL DEFAULT now(),
    updated_at    timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT uq_proxy UNIQUE (protocol, host, port, username)
);

-- 常用索引
CREATE INDEX idx_proxies_group_status_disabled
    ON proxies ("group", status, disabled);

-- tags 查询索引
CREATE INDEX idx_proxies_tags_gin
    ON proxies USING gin (tags);
 
-- 可用代理热点查询索引
CREATE INDEX idx_proxies_alive_fast
    ON proxies ("group", disabled, score DESC, last_check_at DESC)
    WHERE status = 'alive';

-- updated_at 自动维护触发器
CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_touch_updated_at
BEFORE UPDATE ON proxies
FOR EACH ROW
EXECUTE FUNCTION touch_updated_at();

-- RR 游标表
CREATE TABLE rr_cursors (
    k text PRIMARY KEY,
    v bigint NOT NULL DEFAULT 0,
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- 租约表
CREATE TABLE proxy_leases (
    lease_id   text PRIMARY KEY,
    proxy_id   uuid NOT NULL REFERENCES proxies(id),
    expire_at  timestamptz NOT NULL,

    site       text NOT NULL DEFAULT '',
    "group"    text NOT NULL DEFAULT 'default',

    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_leases_expire ON proxy_leases(expire_at);

-- 测试日志表
CREATE TABLE proxy_test_logs (
    id          bigserial PRIMARY KEY,
    proxy_id    uuid NOT NULL REFERENCES proxies(id),
    site        text NOT NULL,
    ok          boolean NOT NULL,
    latency_ms  bigint NOT NULL,
    error_text  text NOT NULL DEFAULT '',
    checked_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_test_logs_proxy_time ON proxy_test_logs(proxy_id, checked_at DESC);