-- Phase 2: region_pricing 扩展来源区分与免费额度元数据

ALTER TABLE region_pricing
    ADD COLUMN IF NOT EXISTS source_type TEXT NOT NULL DEFAULT 'official',
    ADD COLUMN IF NOT EXISTS free_quota TEXT,
    ADD COLUMN IF NOT EXISTS free_limitations TEXT NOT NULL DEFAULT '[]',
    ADD COLUMN IF NOT EXISTS rate_limit TEXT NOT NULL DEFAULT '{}';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'chk_region_pricing_source_type'
    ) THEN
        ALTER TABLE region_pricing
            ADD CONSTRAINT chk_region_pricing_source_type
            CHECK (source_type IN ('official', 'reseller', 'free_tier'));
    END IF;
END
$$;

UPDATE region_pricing rp
SET
    source_type = CASE
        WHEN rp.is_free THEN 'free_tier'
        WHEN lower(coalesce(o.name, '')) IN (
            'openrouter',
            'siliconflow',
            'together ai',
            'groq',
            'baidu qianfan',
            'alibaba bailian',
            'tencent cloud',
            'huawei cloud'
        ) THEN 'reseller'
        ELSE 'official'
    END,
    free_quota = CASE
        WHEN rp.is_free AND coalesce(rp.free_quota, '') = '' THEN 'Imported free-tier pricing entry'
        ELSE rp.free_quota
    END,
    free_limitations = CASE
        WHEN coalesce(rp.free_limitations, '') = '' THEN '[]'
        ELSE rp.free_limitations
    END,
    rate_limit = CASE
        WHEN coalesce(rp.rate_limit, '') = '' THEN '{}'
        ELSE rp.rate_limit
    END
FROM operator o
WHERE rp.operator_id = o.id;

UPDATE region_pricing
SET
    source_type = CASE
        WHEN is_free THEN 'free_tier'
        ELSE source_type
    END,
    free_quota = CASE
        WHEN is_free AND coalesce(free_quota, '') = '' THEN 'Imported free-tier pricing entry'
        ELSE free_quota
    END,
    free_limitations = CASE
        WHEN coalesce(free_limitations, '') = '' THEN '[]'
        ELSE free_limitations
    END,
    rate_limit = CASE
        WHEN coalesce(rate_limit, '') = '' THEN '{}'
        ELSE rate_limit
    END
WHERE operator_id IS NULL;

CREATE INDEX IF NOT EXISTS idx_region_pricing_source_type ON region_pricing(source_type);