-- Phase 2: 腾讯云 / 订阅型套餐价格模型

CREATE TABLE IF NOT EXISTS subscription_plan (
    id              BIGSERIAL PRIMARY KEY,
    provider_id     BIGINT NOT NULL REFERENCES model_provider(id) ON DELETE CASCADE,
    operator_id     BIGINT REFERENCES operator(id) ON DELETE SET NULL,
    plan_family     TEXT NOT NULL CHECK (plan_family IN ('token_plan', 'coding_plan')),
    plan_code       TEXT NOT NULL,
    plan_name       TEXT NOT NULL,
    tier            TEXT NOT NULL,
    billing_cycle   TEXT NOT NULL DEFAULT 'monthly',
    currency        TEXT NOT NULL DEFAULT 'CNY',
    list_price      REAL NOT NULL CHECK (list_price >= 0),
    price_unit      TEXT NOT NULL,
    quota_value     BIGINT,
    quota_unit      TEXT,
    context_window  INTEGER,
    plan_scope      TEXT,
    model_scope     TEXT NOT NULL DEFAULT '[]',
    source_url      TEXT NOT NULL,
    published_at    TIMESTAMP,
    effective_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    notes           TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      TEXT DEFAULT 'system',
    updated_by      TEXT DEFAULT 'system',
    UNIQUE (provider_id, plan_code, effective_date),
    CONSTRAINT chk_subscription_plan_currency CHECK (currency IN ('CNY', 'USD', 'EUR')),
    CONSTRAINT chk_subscription_plan_quota_non_negative CHECK (quota_value IS NULL OR quota_value >= 0),
    CONSTRAINT chk_subscription_plan_context_non_negative CHECK (context_window IS NULL OR context_window >= 0)
);

CREATE INDEX IF NOT EXISTS idx_subscription_plan_provider_id ON subscription_plan(provider_id);
CREATE INDEX IF NOT EXISTS idx_subscription_plan_operator_id ON subscription_plan(operator_id);
CREATE INDEX IF NOT EXISTS idx_subscription_plan_family ON subscription_plan(plan_family);
CREATE INDEX IF NOT EXISTS idx_subscription_plan_effective_date ON subscription_plan(effective_date);

COMMENT ON TABLE subscription_plan IS '订阅型套餐价格信息（如腾讯云 Token Plan / Coding Plan）';