-- ClientFlow v4 migration 002: customer/document/shipment indexes.
CREATE INDEX IF NOT EXISTS idx_customers_tax_id
ON customers(tax_id)
WHERE tax_id IS NOT NULL AND tax_id <> '';

CREATE INDEX IF NOT EXISTS idx_customers_jasmin_party_key
ON customers(jasmin_customer_party_key)
WHERE jasmin_customer_party_key IS NOT NULL AND jasmin_customer_party_key <> '';

CREATE INDEX IF NOT EXISTS idx_commercial_documents_opportunity_kind
ON commercial_documents(opportunity_id, document_kind, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_commercial_documents_customer_kind
ON commercial_documents(customer_id, document_kind, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_commercial_documents_external
ON commercial_documents(system, external_id)
WHERE external_id IS NOT NULL AND external_id <> '';

CREATE INDEX IF NOT EXISTS idx_shipments_opportunity_status
ON shipments(opportunity_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_shipments_external_reference
ON shipments(system, external_reference)
WHERE external_reference IS NOT NULL AND external_reference <> '';