-- =============================================================================
-- Migration 046: PChome authorized sales acquisition controlled-apply receipts
-- Additive only. No product, price, sales, or historical rows are rewritten.
-- =============================================================================

CREATE TABLE IF NOT EXISTS pchome_sales_acquisition_receipts (
    receipt_id VARCHAR(64) PRIMARY KEY,
    trace_id VARCHAR(64) NOT NULL,
    span_id VARCHAR(16) NOT NULL,
    run_id VARCHAR(64) NOT NULL,
    work_item_id VARCHAR(100) NOT NULL,
    status VARCHAR(40) NOT NULL,
    source_type VARCHAR(40) NOT NULL,
    source_fingerprint VARCHAR(64),
    source_ref_hash VARCHAR(64),
    source_file_name VARCHAR(500),
    risk_level VARCHAR(20) NOT NULL DEFAULT 'medium',
    policy_decision VARCHAR(40) NOT NULL,
    decision VARCHAR(80) NOT NULL,
    import_job_id INTEGER,
    rows_imported INTEGER NOT NULL DEFAULT 0,
    before_freshness_json TEXT,
    stage_receipts_json TEXT,
    verifier_json TEXT,
    error_kind VARCHAR(100),
    public_message TEXT,
    started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

ALTER TABLE pchome_sales_acquisition_receipts
    ADD COLUMN IF NOT EXISTS span_id VARCHAR(16);
UPDATE pchome_sales_acquisition_receipts
SET span_id = SUBSTRING(MD5(receipt_id), 1, 16)
WHERE span_id IS NULL;
ALTER TABLE pchome_sales_acquisition_receipts
    ALTER COLUMN span_id SET NOT NULL;

CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_trace ON pchome_sales_acquisition_receipts (trace_id);
CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_span ON pchome_sales_acquisition_receipts (span_id);
CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_run ON pchome_sales_acquisition_receipts (run_id);
CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_work_item ON pchome_sales_acquisition_receipts (work_item_id);
CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_status ON pchome_sales_acquisition_receipts (status);
CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_source ON pchome_sales_acquisition_receipts (source_type);
CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_fingerprint ON pchome_sales_acquisition_receipts (source_fingerprint);
CREATE INDEX IF NOT EXISTS idx_pchome_sales_acq_job ON pchome_sales_acquisition_receipts (import_job_id);

GRANT ALL PRIVILEGES ON pchome_sales_acquisition_receipts TO momo;

DO $$
BEGIN
    RAISE NOTICE 'Migration 046 complete: PChome sales acquisition receipts are ready';
END $$;
