-- =============================================================================
-- Migration 044: external market source / offer normalization
-- MOMO PRO / PChome revenue growth automation
-- 2026-06-15 Taipei
-- =============================================================================
-- Notes:
--   Additive only. This migration does not drop, truncate, or rewrite existing
--   competitor_prices / competitor_price_history / market_* tables.
-- =============================================================================

CREATE TABLE IF NOT EXISTS external_market_sources (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(80) NOT NULL UNIQUE,
    display_name VARCHAR(160) NOT NULL,
    platform_code VARCHAR(80) NOT NULL,
    source_kind VARCHAR(60) NOT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'paused',
    enabled BOOLEAN NOT NULL DEFAULT FALSE,
    write_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    allowed_input_methods_json TEXT,
    quality_policy_json TEXT,
    plain_note TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_external_market_sources_code
    ON external_market_sources (code);
CREATE INDEX IF NOT EXISTS idx_external_market_sources_platform_code
    ON external_market_sources (platform_code);
CREATE INDEX IF NOT EXISTS idx_external_market_sources_source_kind
    ON external_market_sources (source_kind);
CREATE INDEX IF NOT EXISTS idx_external_market_sources_status
    ON external_market_sources (status, enabled);

CREATE TABLE IF NOT EXISTS external_offers (
    id BIGSERIAL PRIMARY KEY,
    source_code VARCHAR(80) NOT NULL REFERENCES external_market_sources(code),
    platform_code VARCHAR(80) NOT NULL,
    source_product_id VARCHAR(220) NOT NULL,
    source_offer_key VARCHAR(260) NOT NULL,
    title TEXT NOT NULL,
    brand VARCHAR(180),
    category_text VARCHAR(320),
    product_url TEXT,
    image_url TEXT,
    price DOUBLE PRECISION,
    original_price DOUBLE PRECISION,
    currency VARCHAR(12) NOT NULL DEFAULT 'TWD',
    stock_status VARCHAR(80),
    sold_count INTEGER,
    rating DOUBLE PRECISION,
    review_count INTEGER,
    observed_at TIMESTAMP NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMP,
    ingestion_method VARCHAR(60) NOT NULL,
    connector_key VARCHAR(120),
    pchome_product_id VARCHAR(120),
    momo_sku VARCHAR(80),
    match_status VARCHAR(40) NOT NULL DEFAULT 'unmatched',
    quality_score DOUBLE PRECISION NOT NULL DEFAULT 0,
    data_quality_status VARCHAR(40) NOT NULL DEFAULT 'needs_review',
    quality_notes_json TEXT,
    raw_payload_json TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_external_offer_source_product_observed
        UNIQUE (source_code, source_product_id, observed_at, ingestion_method)
);

CREATE INDEX IF NOT EXISTS idx_external_offers_source_code
    ON external_offers (source_code);
CREATE INDEX IF NOT EXISTS idx_external_offers_platform_code
    ON external_offers (platform_code);
CREATE INDEX IF NOT EXISTS idx_external_offers_source_product_id
    ON external_offers (source_product_id);
CREATE INDEX IF NOT EXISTS idx_external_offers_brand
    ON external_offers (brand);
CREATE INDEX IF NOT EXISTS idx_external_offers_category_text
    ON external_offers (category_text);
CREATE INDEX IF NOT EXISTS idx_external_offers_stock_status
    ON external_offers (stock_status);
CREATE INDEX IF NOT EXISTS idx_external_offers_observed_at
    ON external_offers (observed_at);
CREATE INDEX IF NOT EXISTS idx_external_offers_expires_at
    ON external_offers (expires_at);
CREATE INDEX IF NOT EXISTS idx_external_offers_ingestion_method
    ON external_offers (ingestion_method);
CREATE INDEX IF NOT EXISTS idx_external_offers_connector_key
    ON external_offers (connector_key);
CREATE INDEX IF NOT EXISTS idx_external_offers_pchome_product_id
    ON external_offers (pchome_product_id);
CREATE INDEX IF NOT EXISTS idx_external_offers_momo_sku
    ON external_offers (momo_sku);
CREATE INDEX IF NOT EXISTS idx_external_offers_match_status
    ON external_offers (match_status);
CREATE INDEX IF NOT EXISTS idx_external_offers_data_quality_status
    ON external_offers (data_quality_status);
CREATE INDEX IF NOT EXISTS idx_external_offers_source_seen
    ON external_offers (source_code, observed_at);
CREATE INDEX IF NOT EXISTS idx_external_offers_platform_product
    ON external_offers (platform_code, source_product_id);
CREATE INDEX IF NOT EXISTS idx_external_offers_pchome_product
    ON external_offers (pchome_product_id, source_code);
CREATE INDEX IF NOT EXISTS idx_external_offers_match_quality
    ON external_offers (match_status, data_quality_status, quality_score);

INSERT INTO external_market_sources (
    code,
    display_name,
    platform_code,
    source_kind,
    status,
    enabled,
    write_enabled,
    allowed_input_methods_json,
    quality_policy_json,
    plain_note
) VALUES
    (
        'momo_reference',
        'MOMO 外部價格參考',
        'momo',
        'legacy_bridge',
        'active',
        TRUE,
        FALSE,
        '["legacy_competitor_cache","manual_csv","provider_api"]',
        '{"minimum_match_status":"verified","minimum_quality_score":76}',
        '目前只採用已確認同款的 MOMO 參考價。'
    ),
    (
        'shopee',
        '蝦皮',
        'shopee',
        'connector_contract',
        'paused',
        FALSE,
        FALSE,
        '["official_api","provider_api","manual_csv"]',
        '{"minimum_match_status":"verified","manual_review_required":true}',
        '先暫停，不進告警；未來可接官方 API、供應商資料或手動 CSV。'
    ),
    (
        'coupang',
        '酷澎',
        'coupang',
        'connector_contract',
        'paused',
        FALSE,
        FALSE,
        '["official_api","provider_api","manual_csv"]',
        '{"minimum_match_status":"verified","manual_review_required":true}',
        '先暫停，不進告警；未來可接官方 API、供應商資料或手動 CSV。'
    )
ON CONFLICT (code) DO UPDATE SET
    display_name = EXCLUDED.display_name,
    platform_code = EXCLUDED.platform_code,
    source_kind = EXCLUDED.source_kind,
    status = EXCLUDED.status,
    enabled = EXCLUDED.enabled,
    allowed_input_methods_json = EXCLUDED.allowed_input_methods_json,
    quality_policy_json = EXCLUDED.quality_policy_json,
    plain_note = EXCLUDED.plain_note,
    updated_at = NOW();

GRANT ALL PRIVILEGES ON external_market_sources TO momo;
GRANT ALL PRIVILEGES ON external_offers TO momo;
GRANT USAGE, SELECT ON SEQUENCE external_market_sources_id_seq TO momo;
GRANT USAGE, SELECT ON SEQUENCE external_offers_id_seq TO momo;

DO $$
BEGIN
    RAISE NOTICE 'Migration 044 complete: external market source and offer normalization is ready';
END $$;