-- 通用货品台账系统 PostgreSQL schema v2: structured reserved fields + minimal backend/write-layer support
-- Purpose: promote common workbook fields into typed columns instead of relying only on raw JSON payloads.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ========== 主数据扩展 ==========
ALTER TABLE parties
    ADD COLUMN IF NOT EXISTS tax_no TEXT,
    ADD COLUMN IF NOT EXISTS address TEXT,
    ADD COLUMN IF NOT EXISTS contact_person TEXT,
    ADD COLUMN IF NOT EXISTS phone_masked TEXT,
    ADD COLUMN IF NOT EXISTS email_masked TEXT,
    ADD COLUMN IF NOT EXISTS bank_account_masked TEXT,
    ADD COLUMN IF NOT EXISTS source_status TEXT;

ALTER TABLE projects
    ADD COLUMN IF NOT EXISTS start_date DATE,
    ADD COLUMN IF NOT EXISTS end_date DATE,
    ADD COLUMN IF NOT EXISTS external_project_no TEXT;

ALTER TABLE products
    ADD COLUMN IF NOT EXISTS brand TEXT,
    ADD COLUMN IF NOT EXISTS series TEXT,
    ADD COLUMN IF NOT EXISTS model TEXT,
    ADD COLUMN IF NOT EXISTS color_style TEXT,
    ADD COLUMN IF NOT EXISTS dimensions TEXT,
    ADD COLUMN IF NOT EXISTS weight TEXT,
    ADD COLUMN IF NOT EXISTS package_spec TEXT,
    ADD COLUMN IF NOT EXISTS tax_rate NUMERIC(8,4),
    ADD COLUMN IF NOT EXISTS list_price NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS cost_price NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS product_status_note TEXT;

ALTER TABLE source_files
    ADD COLUMN IF NOT EXISTS source_platform TEXT,
    ADD COLUMN IF NOT EXISTS uploader TEXT,
    ADD COLUMN IF NOT EXISTS received_status TEXT,
    ADD COLUMN IF NOT EXISTS original_source_path TEXT,
    ADD COLUMN IF NOT EXISTS file_exists BOOLEAN,
    ADD COLUMN IF NOT EXISTS parsed_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS parser_version TEXT;

CREATE TABLE IF NOT EXISTS file_receipts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_file_id UUID REFERENCES source_files(id) ON DELETE SET NULL,
    receipt_code TEXT UNIQUE,
    source_platform TEXT,
    sender_name TEXT,
    received_at TIMESTAMPTZ,
    source_cache_path TEXT,
    original_source_path TEXT,
    receive_status TEXT,
    file_exists BOOLEAN,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ========== 单据/订单扩展 ==========
ALTER TABLE business_documents
    ADD COLUMN IF NOT EXISTS document_no_alt TEXT,
    ADD COLUMN IF NOT EXISTS contract_no TEXT,
    ADD COLUMN IF NOT EXISTS external_document_no TEXT,
    ADD COLUMN IF NOT EXISTS signature_date DATE,
    ADD COLUMN IF NOT EXISTS effective_start_date DATE,
    ADD COLUMN IF NOT EXISTS effective_end_date DATE,
    ADD COLUMN IF NOT EXISTS currency TEXT DEFAULT 'CNY',
    ADD COLUMN IF NOT EXISTS total_amount NUMERIC(18,2),
    ADD COLUMN IF NOT EXISTS tax_rate NUMERIC(8,4),
    ADD COLUMN IF NOT EXISTS payment_terms TEXT,
    ADD COLUMN IF NOT EXISTS delivery_terms TEXT,
    ADD COLUMN IF NOT EXISTS raw_own_party_name TEXT,
    ADD COLUMN IF NOT EXISTS raw_counterparty_name TEXT;

ALTER TABLE purchase_orders
    ADD COLUMN IF NOT EXISTS external_contract_no TEXT,
    ADD COLUMN IF NOT EXISTS supplier_contract_no_on_order TEXT,
    ADD COLUMN IF NOT EXISTS customer_contract_no_on_order TEXT,
    ADD COLUMN IF NOT EXISTS expected_arrival_start DATE,
    ADD COLUMN IF NOT EXISTS expected_arrival_end DATE,
    ADD COLUMN IF NOT EXISTS expected_arrival_text TEXT,
    ADD COLUMN IF NOT EXISTS delivery_address TEXT,
    ADD COLUMN IF NOT EXISTS receiver_name TEXT,
    ADD COLUMN IF NOT EXISTS receiver_phone_masked TEXT,
    ADD COLUMN IF NOT EXISTS logistics_company TEXT,
    ADD COLUMN IF NOT EXISTS tracking_no TEXT,
    ADD COLUMN IF NOT EXISTS payment_status TEXT,
    ADD COLUMN IF NOT EXISTS fulfillment_status TEXT;

ALTER TABLE order_lines
    ADD COLUMN IF NOT EXISTS line_external_no TEXT,
    ADD COLUMN IF NOT EXISTS product_code_raw TEXT,
    ADD COLUMN IF NOT EXISTS barcode_69_raw TEXT,
    ADD COLUMN IF NOT EXISTS spec_raw TEXT,
    ADD COLUMN IF NOT EXISTS color_style TEXT,
    ADD COLUMN IF NOT EXISTS package_spec TEXT,
    ADD COLUMN IF NOT EXISTS tax_rate NUMERIC(8,4),
    ADD COLUMN IF NOT EXISTS tax_amount NUMERIC(18,2),
    ADD COLUMN IF NOT EXISTS delivery_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS received_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS returned_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS arrival_status TEXT,
    ADD COLUMN IF NOT EXISTS expected_arrival_start DATE,
    ADD COLUMN IF NOT EXISTS expected_arrival_end DATE,
    ADD COLUMN IF NOT EXISTS source_row_ref TEXT;

ALTER TABLE channel_allocations
    ADD COLUMN IF NOT EXISTS allocation_type TEXT DEFAULT 'planned',
    ADD COLUMN IF NOT EXISTS planned_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS confirmed_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS shipped_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS signed_quantity NUMERIC(18,4);

-- ========== 库存/发货扩展 ==========
ALTER TABLE inventory_accounts
    ADD COLUMN IF NOT EXISTS location_text TEXT,
    ADD COLUMN IF NOT EXISTS manager_party_id UUID REFERENCES parties(id),
    ADD COLUMN IF NOT EXISTS accounting_policy TEXT;

ALTER TABLE stock_items
    ADD COLUMN IF NOT EXISTS batch_no TEXT,
    ADD COLUMN IF NOT EXISTS production_date DATE,
    ADD COLUMN IF NOT EXISTS expiry_date DATE,
    ADD COLUMN IF NOT EXISTS storage_location TEXT,
    ADD COLUMN IF NOT EXISTS safety_stock_qty NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS locked_quantity NUMERIC(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS available_status TEXT;

ALTER TABLE stock_movements
    ADD COLUMN IF NOT EXISTS movement_no TEXT,
    ADD COLUMN IF NOT EXISTS batch_no TEXT,
    ADD COLUMN IF NOT EXISTS source_order_line_id UUID REFERENCES order_lines(id),
    ADD COLUMN IF NOT EXISTS related_movement_id UUID REFERENCES stock_movements(id),
    ADD COLUMN IF NOT EXISTS expected_return_date DATE,
    ADD COLUMN IF NOT EXISTS actual_return_date DATE,
    ADD COLUMN IF NOT EXISTS signoff_status TEXT,
    ADD COLUMN IF NOT EXISTS logistics_company TEXT,
    ADD COLUMN IF NOT EXISTS tracking_no TEXT,
    ADD COLUMN IF NOT EXISTS delivery_address TEXT,
    ADD COLUMN IF NOT EXISTS receiver_name TEXT,
    ADD COLUMN IF NOT EXISTS receiver_phone_masked TEXT;

ALTER TABLE shipment_signoffs
    ADD COLUMN IF NOT EXISTS logistics_company TEXT,
    ADD COLUMN IF NOT EXISTS tracking_no TEXT,
    ADD COLUMN IF NOT EXISTS delivery_address TEXT,
    ADD COLUMN IF NOT EXISTS receiver_name TEXT,
    ADD COLUMN IF NOT EXISTS receiver_phone_masked TEXT,
    ADD COLUMN IF NOT EXISTS expected_arrival_start DATE,
    ADD COLUMN IF NOT EXISTS expected_arrival_end DATE,
    ADD COLUMN IF NOT EXISTS signed_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS signer_name TEXT,
    ADD COLUMN IF NOT EXISTS signer_contact_masked TEXT;

ALTER TABLE shipment_signoff_lines
    ADD COLUMN IF NOT EXISTS shipped_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS signed_quantity NUMERIC(18,4),
    ADD COLUMN IF NOT EXISTS rejected_quantity NUMERIC(18,4) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS rejection_reason TEXT;

-- ========== 补货计划：承接后续补货测算表 ==========
CREATE TABLE IF NOT EXISTS replenishment_plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_code TEXT UNIQUE,
    project_id UUID REFERENCES projects(id),
    plan_name TEXT NOT NULL,
    plan_period_start DATE,
    plan_period_end DATE,
    plan_status TEXT NOT NULL DEFAULT 'draft' CHECK (plan_status IN ('draft', 'confirmed', 'executed', 'cancelled', 'superseded')),
    source_file_id UUID REFERENCES source_files(id),
    policy_version TEXT,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS replenishment_plan_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES replenishment_plans(id) ON DELETE CASCADE,
    line_no INTEGER NOT NULL DEFAULT 1,
    product_id UUID REFERENCES products(id),
    product_name TEXT NOT NULL,
    channel_party_id UUID REFERENCES parties(id),
    standard_channel TEXT,
    current_stock_qty NUMERIC(18,4),
    expected_demand_qty NUMERIC(18,4),
    planned_replenishment_qty NUMERIC(18,4),
    transfer_from_account_id UUID REFERENCES inventory_accounts(id),
    transfer_to_account_id UUID REFERENCES inventory_accounts(id),
    priority TEXT,
    reason TEXT,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (plan_id, line_no)
);

-- ========== 写入请求/业务工具审计 ==========
CREATE TABLE IF NOT EXISTS api_write_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_code TEXT UNIQUE,
    actor_profile TEXT,
    tool_name TEXT NOT NULL,
    operation TEXT NOT NULL,
    idempotency_key TEXT UNIQUE,
    request_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    validation_result JSONB NOT NULL DEFAULT '{}'::jsonb,
    status TEXT NOT NULL DEFAULT 'received' CHECK (status IN ('received', 'validated', 'applied', 'rejected', 'failed')),
    target_table TEXT,
    target_id UUID,
    error_message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    applied_at TIMESTAMPTZ
);

-- ========== 回填已有数据到新结构化列 ==========
UPDATE business_documents
SET contract_no = COALESCE(contract_no, NULLIF(document_no, ''))
WHERE document_type IN ('supplier_contract', 'cooperation_agreement') AND contract_no IS NULL;

UPDATE purchase_orders
SET expected_arrival_text = COALESCE(expected_arrival_text, NULLIF(expected_arrival, ''))
WHERE expected_arrival_text IS NULL;

UPDATE order_lines
SET delivery_quantity = COALESCE(delivery_quantity, quantity),
    received_quantity = COALESCE(received_quantity, 0),
    returned_quantity = COALESCE(returned_quantity, 0)
WHERE delivery_quantity IS NULL OR received_quantity IS NULL OR returned_quantity IS NULL;

UPDATE channel_allocations
SET planned_quantity = COALESCE(planned_quantity, original_quantity),
    confirmed_quantity = COALESCE(confirmed_quantity, adjusted_quantity)
WHERE planned_quantity IS NULL OR confirmed_quantity IS NULL;

UPDATE stock_movements
SET movement_no = COALESCE(movement_no, movement_code)
WHERE movement_no IS NULL;

UPDATE shipment_signoff_lines
SET shipped_quantity = COALESCE(shipped_quantity, quantity),
    signed_quantity = COALESCE(signed_quantity, CASE WHEN sign_status = 'signed' THEN quantity ELSE 0 END)
WHERE shipped_quantity IS NULL OR signed_quantity IS NULL;

-- ========== 索引 ==========
CREATE INDEX IF NOT EXISTS idx_business_documents_contract_no ON business_documents(contract_no);
CREATE INDEX IF NOT EXISTS idx_business_documents_external_no ON business_documents(external_document_no);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_contract_arrival ON purchase_orders(external_contract_no, expected_arrival_start, expected_arrival_end);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_tracking ON purchase_orders(tracking_no);
CREATE INDEX IF NOT EXISTS idx_order_lines_barcode_raw ON order_lines(barcode_69_raw);
CREATE INDEX IF NOT EXISTS idx_stock_movements_tracking ON stock_movements(tracking_no);
CREATE INDEX IF NOT EXISTS idx_shipment_signoffs_tracking ON shipment_signoffs(tracking_no);
CREATE INDEX IF NOT EXISTS idx_file_receipts_source_file ON file_receipts(source_file_id);
CREATE INDEX IF NOT EXISTS idx_api_write_requests_status ON api_write_requests(status, created_at);

-- ========== 扩展视图 ==========
CREATE OR REPLACE VIEW v_api_summary AS
SELECT 'parties' AS metric, COUNT(*)::numeric AS value FROM parties
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'source_files', COUNT(*) FROM source_files
UNION ALL SELECT 'business_documents', COUNT(*) FROM business_documents
UNION ALL SELECT 'purchase_orders', COUNT(*) FROM purchase_orders
UNION ALL SELECT 'order_lines', COUNT(*) FROM order_lines
UNION ALL SELECT 'channel_allocations', COUNT(*) FROM channel_allocations
UNION ALL SELECT 'stock_items', COUNT(*) FROM stock_items
UNION ALL SELECT 'stock_movements', COUNT(*) FROM stock_movements
UNION ALL SELECT 'shipment_signoffs', COUNT(*) FROM shipment_signoffs
UNION ALL SELECT 'reconciliation_lines', COUNT(*) FROM reconciliation_lines
UNION ALL SELECT 'validation_issues_open', COUNT(*) FROM validation_issues WHERE status='open'
UNION ALL SELECT 'raw_import_rows', COUNT(*) FROM raw_import_rows;

CREATE OR REPLACE VIEW v_order_lines_expanded AS
SELECT
    ol.*,
    po.order_code,
    po.order_side,
    po.order_type,
    po.order_no,
    po.order_date,
    po.external_contract_no,
    po.expected_arrival_start AS order_expected_arrival_start,
    po.expected_arrival_end AS order_expected_arrival_end,
    po.expected_arrival_text AS order_expected_arrival_text,
    bd.contract_no,
    bd.document_type,
    p.standard_name,
    p.barcode_69,
    p.guobo_code
FROM order_lines ol
JOIN purchase_orders po ON po.id = ol.order_id
LEFT JOIN business_documents bd ON bd.id = po.document_id
LEFT JOIN products p ON p.id = ol.product_id;
