-- Phase 1 PostgreSQL migration
-- 三张核心表：models、model_prices、report_runs
-- 对应 fetch_openrouter.go 采集器输出字段和日报生成器需求

-- models：模型基础信息表
CREATE TABLE IF NOT EXISTS models (
    id                  BIGSERIAL PRIMARY KEY,
    source              TEXT        NOT NULL DEFAULT 'openrouter',
    external_id         TEXT        NOT NULL UNIQUE,
    name                TEXT,
    description         TEXT,
    context_length      INTEGER,
    capabilities        JSONB,                        -- JSONB 数组存储
    created_at_source   BIGINT,                      -- OpenRouter created 字段（Unix 时间戳）
    is_free             BOOLEAN     NOT NULL DEFAULT FALSE,
    status              TEXT        NOT NULL DEFAULT 'active', -- active / deprecated / discontinued
    raw_payload         JSONB,                       -- 完整原始 JSON
    created_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_models_external_id ON models(external_id);
CREATE INDEX IF NOT EXISTS idx_models_source ON models(source);
CREATE INDEX IF NOT EXISTS idx_models_status ON models(status);
CREATE INDEX IF NOT EXISTS idx_models_is_free ON models(is_free);

-- model_prices：模型定价表
CREATE TABLE IF NOT EXISTS model_prices (
    id                    BIGSERIAL PRIMARY KEY,
    model_id              BIGINT     NOT NULL REFERENCES models(id) ON DELETE CASCADE,
    source                TEXT       NOT NULL DEFAULT 'openrouter',
    currency              TEXT       NOT NULL DEFAULT 'USD',
    input_price_per_mtok  REAL,
    output_price_per_mtok REAL,
    effective_date        DATE,
    source_url            TEXT,
    created_at            TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(model_id, source, currency, effective_date)
);

CREATE INDEX IF NOT EXISTS idx_prices_model_id ON model_prices(model_id);
CREATE INDEX IF NOT EXISTS idx_prices_source ON model_prices(source);
CREATE INDEX IF NOT EXISTS idx_prices_currency ON model_prices(currency);

-- report_runs：日报运行记录表
CREATE TABLE IF NOT EXISTS report_runs (
    id              BIGSERIAL PRIMARY KEY,
    source          TEXT       NOT NULL DEFAULT 'openrouter',
    report_date     DATE       NOT NULL,
    status          TEXT       NOT NULL DEFAULT 'pending', -- pending / generated / failed
    summary_md      TEXT,
    output_path     TEXT,
    error_message   TEXT,
    created_at      TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_reports_source ON report_runs(source);
CREATE INDEX IF NOT EXISTS idx_reports_report_date ON report_runs(report_date);
CREATE INDEX IF NOT EXISTS idx_reports_status ON report_runs(status);