-- 迁移：独立发货单表（发货 vs 签收 交叉核对架构）
-- 时间：2026-06-12
-- 设计：发货是我方动作（dispatch_notes），签收是对方动作（shipment_signoffs.dispatch_note_id 关联），
--       核对 = 两表按采购单交叉对比发货量/签收量。外键 ON DELETE RESTRICT（不再级联）。
-- 发货单支持从采购单一键生成（ops/dispatch-from-order）和按真实版式导出 Excel（ops/dispatch-export）。
CREATE TABLE IF NOT EXISTS dispatch_notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dispatch_code text,
  purchase_order_id uuid NOT NULL REFERENCES purchase_orders(id) ON DELETE RESTRICT,
  project_id uuid REFERENCES projects(id),
  shipper_party_id uuid REFERENCES parties(id),
  receiver_party_id uuid REFERENCES parties(id),
  ship_date date, settlement_method text,
  logistics_company text, tracking_no text, delivery_address text, receiver_name text,
  contact_name text, contact_phone_masked text, purchaser_name text,
  product_manager_name text, header_title text, purpose text,
  status text DEFAULT 'draft', notes text,
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS dispatch_note_lines (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dispatch_note_id uuid NOT NULL REFERENCES dispatch_notes(id) ON DELETE RESTRICT,
  line_no integer, product_id uuid REFERENCES products(id),
  source_order_line_id uuid REFERENCES order_lines(id),
  product_name text, quantity numeric, unit text, notes text,
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
);
ALTER TABLE shipment_signoffs ADD COLUMN IF NOT EXISTS dispatch_note_id uuid REFERENCES dispatch_notes(id);
GRANT SELECT, INSERT, UPDATE, DELETE ON dispatch_notes, dispatch_note_lines, shipment_signoffs TO ledger_app;
CREATE INDEX IF NOT EXISTS idx_dispatch_po ON dispatch_notes(purchase_order_id);
CREATE INDEX IF NOT EXISTS idx_dispatch_lines_note ON dispatch_note_lines(dispatch_note_id);
