forked from niuniu/llm-intelligence
75 lines
2.1 KiB
SQL
75 lines
2.1 KiB
SQL
-- 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);
|