BEGIN; CREATE SCHEMA IF NOT EXISTS purchases; ALTER TABLE purchases.purchase_requests ADD COLUMN IF NOT EXISTS request_no varchar(80), ADD COLUMN IF NOT EXISTS requester_name varchar(180), ADD COLUMN IF NOT EXISTS department_name varchar(180), ADD COLUMN IF NOT EXISTS warehouse_name varchar(180), ADD COLUMN IF NOT EXISTS required_date date, ADD COLUMN IF NOT EXISTS priority varchar(40) NOT NULL DEFAULT 'normal', ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'draft', ADD COLUMN IF NOT EXISTS approval_status varchar(40) NOT NULL DEFAULT 'not_required', ADD COLUMN IF NOT EXISTS submitted_at timestamp without time zone, ADD COLUMN IF NOT EXISTS submitted_by uuid, ADD COLUMN IF NOT EXISTS submitted_by_name varchar(180), ADD COLUMN IF NOT EXISTS approved_at timestamp without time zone, ADD COLUMN IF NOT EXISTS approved_by uuid, ADD COLUMN IF NOT EXISTS approved_by_name varchar(180), ADD COLUMN IF NOT EXISTS rejected_at timestamp without time zone, ADD COLUMN IF NOT EXISTS rejected_by uuid, ADD COLUMN IF NOT EXISTS rejected_by_name varchar(180), ADD COLUMN IF NOT EXISTS rejection_reason text, ADD COLUMN IF NOT EXISTS converted_purchase_order_id bigint, ADD COLUMN IF NOT EXISTS converted_purchase_order_no varchar(80), ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE purchases.purchase_request_items ADD COLUMN IF NOT EXISTS purchase_request_id bigint, ADD COLUMN IF NOT EXISTS item_sku varchar(80), ADD COLUMN IF NOT EXISTS item_name varchar(180), ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40), ADD COLUMN IF NOT EXISTS requested_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS approved_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS current_stock_snapshot numeric(18,4), ADD COLUMN IF NOT EXISTS reorder_level_snapshot numeric(18,4), ADD COLUMN IF NOT EXISTS estimated_unit_cost numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS estimated_line_total numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE purchases.purchase_orders ADD COLUMN IF NOT EXISTS purchase_order_no varchar(80), ADD COLUMN IF NOT EXISTS po_number varchar(80), ADD COLUMN IF NOT EXISTS supplier_id bigint, ADD COLUMN IF NOT EXISTS supplier_code varchar(80), ADD COLUMN IF NOT EXISTS supplier_name varchar(180), ADD COLUMN IF NOT EXISTS supplier_email varchar(180), ADD COLUMN IF NOT EXISTS supplier_rating numeric(8,2), ADD COLUMN IF NOT EXISTS supplier_performance_label varchar(40), ADD COLUMN IF NOT EXISTS supplier_on_time_delivery_rate numeric(8,2), ADD COLUMN IF NOT EXISTS supplier_avg_lead_days numeric(8,2), ADD COLUMN IF NOT EXISTS supplier_last_order_amount numeric(18,2), ADD COLUMN IF NOT EXISTS supplier_last_order_no varchar(80), ADD COLUMN IF NOT EXISTS warehouse_id bigint, ADD COLUMN IF NOT EXISTS warehouse_name varchar(180), ADD COLUMN IF NOT EXISTS order_date date, ADD COLUMN IF NOT EXISTS expected_delivery_date date, ADD COLUMN IF NOT EXISTS required_date date, ADD COLUMN IF NOT EXISTS currency_code varchar(10) NOT NULL DEFAULT 'GHS', ADD COLUMN IF NOT EXISTS exchange_rate numeric(18,6) NOT NULL DEFAULT 1, ADD COLUMN IF NOT EXISTS subtotal_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS discount_percent numeric(8,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS discount_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS tax_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS shipping_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS total_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'draft', ADD COLUMN IF NOT EXISTS approval_status varchar(40) NOT NULL DEFAULT 'not_required', ADD COLUMN IF NOT EXISTS requires_approval boolean NOT NULL DEFAULT false, ADD COLUMN IF NOT EXISTS approval_threshold_amount numeric(18,2), ADD COLUMN IF NOT EXISTS approval_priority varchar(40) NOT NULL DEFAULT 'normal', ADD COLUMN IF NOT EXISTS submitted_at timestamp without time zone, ADD COLUMN IF NOT EXISTS submitted_by uuid, ADD COLUMN IF NOT EXISTS submitted_by_name varchar(180), ADD COLUMN IF NOT EXISTS approved_at timestamp without time zone, ADD COLUMN IF NOT EXISTS approved_by uuid, ADD COLUMN IF NOT EXISTS approved_by_name varchar(180), ADD COLUMN IF NOT EXISTS rejected_at timestamp without time zone, ADD COLUMN IF NOT EXISTS rejected_by uuid, ADD COLUMN IF NOT EXISTS rejected_by_name varchar(180), ADD COLUMN IF NOT EXISTS rejection_reason text, ADD COLUMN IF NOT EXISTS receipt_status varchar(40) NOT NULL DEFAULT 'not_received', ADD COLUMN IF NOT EXISTS receipt_progress integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS shortlanded_item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS overage_item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS discrepancy_status varchar(40) NOT NULL DEFAULT 'none', ADD COLUMN IF NOT EXISTS budget_allocated_amount numeric(18,2), ADD COLUMN IF NOT EXISTS budget_used_amount numeric(18,2), ADD COLUMN IF NOT EXISTS inventory_impact jsonb NOT NULL DEFAULT '[]'::jsonb, ADD COLUMN IF NOT EXISTS supplier_history jsonb NOT NULL DEFAULT '[]'::jsonb, ADD COLUMN IF NOT EXISTS ai_suggestions jsonb NOT NULL DEFAULT '[]'::jsonb, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS internal_notes text, ADD COLUMN IF NOT EXISTS is_active boolean NOT NULL DEFAULT true, ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT false, ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE purchases.purchase_order_items ADD COLUMN IF NOT EXISTS purchase_order_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_sku varchar(80), ADD COLUMN IF NOT EXISTS item_name varchar(180), ADD COLUMN IF NOT EXISTS description text, ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40), ADD COLUMN IF NOT EXISTS ordered_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS unit_price numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS discount_percent numeric(8,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS discount_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS tax_rate numeric(8,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS tax_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS line_total numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS current_stock_snapshot numeric(18,4), ADD COLUMN IF NOT EXISTS reorder_level_snapshot numeric(18,4), ADD COLUMN IF NOT EXISTS received_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS accepted_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS rejected_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS shortland_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS overage_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS variance_status varchar(40) NOT NULL DEFAULT 'none', ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE purchases.goods_receipts ADD COLUMN IF NOT EXISTS goods_receipt_no varchar(80), ADD COLUMN IF NOT EXISTS grn_number varchar(80), ADD COLUMN IF NOT EXISTS purchase_order_id bigint, ADD COLUMN IF NOT EXISTS purchase_order_no varchar(80), ADD COLUMN IF NOT EXISTS supplier_id bigint, ADD COLUMN IF NOT EXISTS supplier_name varchar(180), ADD COLUMN IF NOT EXISTS warehouse_id bigint, ADD COLUMN IF NOT EXISTS warehouse_name varchar(180), ADD COLUMN IF NOT EXISTS receipt_date date, ADD COLUMN IF NOT EXISTS received_at timestamp without time zone, ADD COLUMN IF NOT EXISTS received_by uuid, ADD COLUMN IF NOT EXISTS received_by_name varchar(180), ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'draft', ADD COLUMN IF NOT EXISTS inspection_status varchar(40) NOT NULL DEFAULT 'pending', ADD COLUMN IF NOT EXISTS putaway_status varchar(40) NOT NULL DEFAULT 'pending', ADD COLUMN IF NOT EXISTS discrepancy_status varchar(40) NOT NULL DEFAULT 'none', ADD COLUMN IF NOT EXISTS expected_item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS received_item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS shortlanded_item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS overage_item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS total_received_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS carrier_name varchar(120), ADD COLUMN IF NOT EXISTS tracking_reference varchar(120), ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT false, ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE purchases.goods_receipt_items ADD COLUMN IF NOT EXISTS goods_receipt_id bigint, ADD COLUMN IF NOT EXISTS purchase_order_item_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_sku varchar(80), ADD COLUMN IF NOT EXISTS item_name varchar(180), ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40), ADD COLUMN IF NOT EXISTS expected_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS received_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS accepted_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS rejected_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS shortland_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS overage_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS condition_status varchar(40) NOT NULL DEFAULT 'good', ADD COLUMN IF NOT EXISTS variance_status varchar(40) NOT NULL DEFAULT 'none', ADD COLUMN IF NOT EXISTS inspection_notes text, ADD COLUMN IF NOT EXISTS putaway_location varchar(120), ADD COLUMN IF NOT EXISTS unit_price numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS line_total numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE purchases.purchase_returns ADD COLUMN IF NOT EXISTS purchase_return_no varchar(80), ADD COLUMN IF NOT EXISTS return_number varchar(80), ADD COLUMN IF NOT EXISTS purchase_order_id bigint, ADD COLUMN IF NOT EXISTS purchase_order_no varchar(80), ADD COLUMN IF NOT EXISTS goods_receipt_id bigint, ADD COLUMN IF NOT EXISTS goods_receipt_no varchar(80), ADD COLUMN IF NOT EXISTS supplier_id bigint, ADD COLUMN IF NOT EXISTS supplier_name varchar(180), ADD COLUMN IF NOT EXISTS return_date date, ADD COLUMN IF NOT EXISTS reason text, ADD COLUMN IF NOT EXISTS currency_code varchar(10) NOT NULL DEFAULT 'GHS', ADD COLUMN IF NOT EXISTS subtotal_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS tax_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS total_amount numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS item_count integer NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'draft', ADD COLUMN IF NOT EXISTS approval_status varchar(40) NOT NULL DEFAULT 'pending', ADD COLUMN IF NOT EXISTS shipment_status varchar(40) NOT NULL DEFAULT 'not_shipped', ADD COLUMN IF NOT EXISTS credit_status varchar(40) NOT NULL DEFAULT 'pending', ADD COLUMN IF NOT EXISTS credit_note_no varchar(80), ADD COLUMN IF NOT EXISTS approved_at timestamp without time zone, ADD COLUMN IF NOT EXISTS approved_by uuid, ADD COLUMN IF NOT EXISTS approved_by_name varchar(180), ADD COLUMN IF NOT EXISTS rejected_at timestamp without time zone, ADD COLUMN IF NOT EXISTS rejected_by uuid, ADD COLUMN IF NOT EXISTS rejected_by_name varchar(180), ADD COLUMN IF NOT EXISTS rejection_reason text, ADD COLUMN IF NOT EXISTS shipped_at timestamp without time zone, ADD COLUMN IF NOT EXISTS carrier_name varchar(120), ADD COLUMN IF NOT EXISTS tracking_reference varchar(120), ADD COLUMN IF NOT EXISTS received_by_supplier_at timestamp without time zone, ADD COLUMN IF NOT EXISTS credited_at timestamp without time zone, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS is_active boolean NOT NULL DEFAULT true, ADD COLUMN IF NOT EXISTS is_deleted boolean NOT NULL DEFAULT false, ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; ALTER TABLE purchases.purchase_return_items ADD COLUMN IF NOT EXISTS purchase_return_id bigint, ADD COLUMN IF NOT EXISTS goods_receipt_item_id bigint, ADD COLUMN IF NOT EXISTS purchase_order_item_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_sku varchar(80), ADD COLUMN IF NOT EXISTS item_name varchar(180), ADD COLUMN IF NOT EXISTS unit_of_measure varchar(40), ADD COLUMN IF NOT EXISTS grn_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS return_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS approved_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS credited_quantity numeric(18,4) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS unit_value numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS line_total numeric(18,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS condition_status varchar(40) NOT NULL DEFAULT 'damaged', ADD COLUMN IF NOT EXISTS reason text, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; CREATE TABLE IF NOT EXISTS purchases.purchase_approval_requests ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), request_no varchar(80) NOT NULL, purchase_order_id bigint REFERENCES purchases.purchase_orders(id) ON DELETE CASCADE, purchase_order_no varchar(80) NOT NULL, supplier_id bigint, supplier_name varchar(180) NOT NULL, warehouse_name varchar(180), amount numeric(18,2) NOT NULL DEFAULT 0, currency_code varchar(10) NOT NULL DEFAULT 'GHS', approval_type varchar(60) NOT NULL DEFAULT 'amount_threshold', status varchar(40) NOT NULL DEFAULT 'pending', priority varchar(40) NOT NULL DEFAULT 'normal', age_days integer NOT NULL DEFAULT 0, submitted_by uuid, submitted_by_name varchar(180), submitted_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, due_at timestamp without time zone, approved_at timestamp without time zone, approved_by uuid, approved_by_name varchar(180), rejected_at timestamp without time zone, rejected_by uuid, rejected_by_name varchar(180), rejection_reason text, supplier_rating numeric(8,2), supplier_on_time_delivery_rate numeric(8,2), supplier_avg_lead_days numeric(8,2), last_order_amount numeric(18,2), budget_allocated_amount numeric(18,2), budget_used_amount numeric(18,2), inventory_impact jsonb NOT NULL DEFAULT '[]'::jsonb, supplier_history jsonb NOT NULL DEFAULT '[]'::jsonb, ai_suggestions jsonb NOT NULL DEFAULT '[]'::jsonb, notes text, is_deleted boolean NOT NULL DEFAULT false, deleted_at timestamp without time zone, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS purchases.goods_receipt_events ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), event_no varchar(80) NOT NULL, goods_receipt_id bigint REFERENCES purchases.goods_receipts(id) ON DELETE CASCADE, goods_receipt_no varchar(80), purchase_order_id bigint REFERENCES purchases.purchase_orders(id) ON DELETE SET NULL, purchase_order_no varchar(80), event_type varchar(60) NOT NULL, from_status varchar(40), to_status varchar(40), event_title varchar(180) NOT NULL, event_details text, occurred_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, occurred_by uuid, occurred_by_name varchar(180), metadata jsonb NOT NULL DEFAULT '{}'::jsonb ); CREATE TABLE IF NOT EXISTS purchases.purchase_discrepancy_cases ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), case_no varchar(80) NOT NULL, discrepancy_type varchar(40) NOT NULL, purchase_order_id bigint REFERENCES purchases.purchase_orders(id) ON DELETE SET NULL, purchase_order_no varchar(80) NOT NULL, goods_receipt_id bigint REFERENCES purchases.goods_receipts(id) ON DELETE SET NULL, goods_receipt_no varchar(80), supplier_id bigint, supplier_name varchar(180) NOT NULL, item_sku varchar(80), item_name varchar(180), ordered_quantity numeric(18,4) NOT NULL DEFAULT 0, received_quantity numeric(18,4) NOT NULL DEFAULT 0, variance_quantity numeric(18,4) NOT NULL DEFAULT 0, unit_value numeric(18,2) NOT NULL DEFAULT 0, variance_value numeric(18,2) NOT NULL DEFAULT 0, status varchar(40) NOT NULL DEFAULT 'pending', resolution_action varchar(60), resolution_notes text, detected_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, approved_at timestamp without time zone, approved_by uuid, approved_by_name varchar(180), resolved_at timestamp without time zone, resolved_by uuid, resolved_by_name varchar(180), rejected_at timestamp without time zone, rejected_by uuid, rejected_by_name varchar(180), rejection_reason text, supplier_performance_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb, ai_suggestions jsonb NOT NULL DEFAULT '[]'::jsonb, is_deleted boolean NOT NULL DEFAULT false, deleted_at timestamp without time zone, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS purchases.purchase_discrepancy_items ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), discrepancy_case_id bigint NOT NULL REFERENCES purchases.purchase_discrepancy_cases(id) ON DELETE CASCADE, purchase_order_item_id bigint REFERENCES purchases.purchase_order_items(id) ON DELETE SET NULL, goods_receipt_item_id bigint REFERENCES purchases.goods_receipt_items(id) ON DELETE SET NULL, item_sku varchar(80) NOT NULL, item_name varchar(180) NOT NULL, ordered_quantity numeric(18,4) NOT NULL DEFAULT 0, received_quantity numeric(18,4) NOT NULL DEFAULT 0, variance_quantity numeric(18,4) NOT NULL DEFAULT 0, unit_value numeric(18,2) NOT NULL DEFAULT 0, variance_value numeric(18,2) NOT NULL DEFAULT 0, condition_status varchar(40), notes text, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS purchases.purchase_return_events ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), event_no varchar(80) NOT NULL, purchase_return_id bigint NOT NULL REFERENCES purchases.purchase_returns(id) ON DELETE CASCADE, purchase_return_no varchar(80), event_type varchar(60) NOT NULL, from_status varchar(40), to_status varchar(40), event_title varchar(180) NOT NULL, event_details text, occurred_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, occurred_by uuid, occurred_by_name varchar(180), metadata jsonb NOT NULL DEFAULT '{}'::jsonb ); CREATE TABLE IF NOT EXISTS purchases.supplier_performance_snapshots ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), supplier_id bigint, supplier_code varchar(80), supplier_name varchar(180) NOT NULL, period_start date NOT NULL, period_end date NOT NULL, total_purchase_orders integer NOT NULL DEFAULT 0, total_purchase_value numeric(18,2) NOT NULL DEFAULT 0, on_time_delivery_percent numeric(8,2) NOT NULL DEFAULT 0, average_delay_days numeric(8,2) NOT NULL DEFAULT 0, average_delivery_days numeric(8,2) NOT NULL DEFAULT 0, shortlanded_percent numeric(8,2) NOT NULL DEFAULT 0, overage_percent numeric(8,2) NOT NULL DEFAULT 0, fill_rate_percent numeric(8,2) NOT NULL DEFAULT 0, score numeric(8,2) NOT NULL DEFAULT 0, score_grade varchar(10), trend varchar(40) NOT NULL DEFAULT 'stable', delivery_trend jsonb NOT NULL DEFAULT '[]'::jsonb, variance_trend jsonb NOT NULL DEFAULT '[]'::jsonb, ai_insights jsonb NOT NULL DEFAULT '[]'::jsonb, metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS purchases.supplier_performance_records ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), supplier_performance_snapshot_id bigint REFERENCES purchases.supplier_performance_snapshots(id) ON DELETE CASCADE, supplier_id bigint, supplier_name varchar(180) NOT NULL, purchase_order_id bigint REFERENCES purchases.purchase_orders(id) ON DELETE SET NULL, purchase_order_no varchar(80) NOT NULL, record_date date NOT NULL, on_time_delivery boolean NOT NULL DEFAULT true, delay_days numeric(8,2) NOT NULL DEFAULT 0, shortlanded_count integer NOT NULL DEFAULT 0, overage_count integer NOT NULL DEFAULT 0, purchase_value numeric(18,2) NOT NULL DEFAULT 0, notes text, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS purchases.purchase_ai_insights ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), insight_no varchar(80) NOT NULL, insight_type varchar(40) NOT NULL DEFAULT 'insight', module_area varchar(80) NOT NULL, entity_type varchar(40), entity_id bigint, title varchar(180) NOT NULL, description text NOT NULL, confidence_percent numeric(8,2), confidence_label varchar(40), impact_label varchar(120), recommendation text, status varchar(40) NOT NULL DEFAULT 'active', created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS purchases.purchase_document_dispatches ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), dispatch_no varchar(80) NOT NULL, document_type varchar(40) NOT NULL, document_id bigint NOT NULL, document_no varchar(80) NOT NULL, supplier_id bigint, supplier_name varchar(180), recipient_email varchar(180), subject varchar(180), message text, dispatch_channel varchar(40) NOT NULL DEFAULT 'email', status varchar(40) NOT NULL DEFAULT 'draft', sent_at timestamp without time zone, sent_by uuid, sent_by_name varchar(180), file_reference text, metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS purchases.purchase_audit_trail ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES platform.tenants(id), entity_type varchar(40) NOT NULL, entity_id bigint, entity_no varchar(80), action varchar(80) NOT NULL, field_name varchar(120), old_value text, new_value text, performed_by uuid, performed_by_name varchar(180), role_name varchar(120), ip_address inet, notes text, metadata jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_orders_no ON purchases.purchase_orders (tenant_id, purchase_order_no) WHERE purchase_order_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_requests_no ON purchases.purchase_requests (tenant_id, request_no) WHERE request_no IS NOT NULL; CREATE UNIQUE INDEX IF NOT EXISTS uq_goods_receipts_no ON purchases.goods_receipts (tenant_id, goods_receipt_no) WHERE goods_receipt_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_returns_no ON purchases.purchase_returns (tenant_id, purchase_return_no) WHERE purchase_return_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_approval_request_no ON purchases.purchase_approval_requests (tenant_id, request_no) WHERE COALESCE(is_deleted, false) = false; CREATE UNIQUE INDEX IF NOT EXISTS uq_goods_receipt_event_no ON purchases.goods_receipt_events (tenant_id, event_no); CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_discrepancy_case_no ON purchases.purchase_discrepancy_cases (tenant_id, case_no) WHERE COALESCE(is_deleted, false) = false; CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_return_event_no ON purchases.purchase_return_events (tenant_id, event_no); CREATE UNIQUE INDEX IF NOT EXISTS uq_supplier_performance_snapshot ON purchases.supplier_performance_snapshots (tenant_id, supplier_name, period_start, period_end); CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_ai_insight_no ON purchases.purchase_ai_insights (tenant_id, insight_no); CREATE UNIQUE INDEX IF NOT EXISTS uq_purchase_document_dispatch_no ON purchases.purchase_document_dispatches (tenant_id, dispatch_no); CREATE INDEX IF NOT EXISTS idx_purchase_orders_status ON purchases.purchase_orders (tenant_id, status, approval_status, receipt_status) WHERE COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS idx_purchase_orders_supplier ON purchases.purchase_orders (tenant_id, supplier_name, expected_delivery_date) WHERE COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS idx_goods_receipts_po ON purchases.goods_receipts (tenant_id, purchase_order_id, receipt_date) WHERE COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS idx_purchase_discrepancy_queue ON purchases.purchase_discrepancy_cases (tenant_id, discrepancy_type, status, detected_at DESC) WHERE COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS idx_purchase_returns_status ON purchases.purchase_returns (tenant_id, status, approval_status, credit_status) WHERE COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS idx_purchase_audit_entity ON purchases.purchase_audit_trail (tenant_id, entity_type, entity_id, created_at DESC); DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cutehorse_app') THEN GRANT USAGE ON SCHEMA purchases TO cutehorse_app; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA purchases TO cutehorse_app; GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA purchases TO cutehorse_app; ALTER DEFAULT PRIVILEGES IN SCHEMA purchases GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO cutehorse_app; ALTER DEFAULT PRIVILEGES IN SCHEMA purchases GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO cutehorse_app; END IF; END $$; COMMIT;