-- 迁移：对账单两层结算状态机（2026-06-12，按用户口述业务逻辑）
-- 上游对账单（上游公司主动发来：产品/数量/渠道/结算日期）：
--   pending(待上游结算) → settled(上游已结算) → frozen(已锁入下游结算) → cleared(已结清)
-- 下游对账单（我们主动发起的总单）：draft(待结算) → settled(已结算)
-- 规则：发起下游结算时系统筛选「settled 且未冻结」的上游单 → 确认后生成下游总单并冻结上游单
--      （downstream_statement_id 挂接，天然防重复筛选）；下游结算完成 → 同步上游单 cleared，这批货结清。
-- 外键 ON DELETE RESTRICT。
CREATE TABLE IF NOT EXISTS downstream_statements (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  statement_code text, supplier_party_id uuid REFERENCES parties(id), project_id uuid REFERENCES projects(id),
  statement_date date, settle_date date, total_quantity numeric, total_amount numeric,
  status text DEFAULT 'draft', notes text,
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS upstream_statements (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  statement_code text, customer_party_id uuid REFERENCES parties(id), project_id uuid REFERENCES projects(id),
  statement_date date, settle_date date, total_quantity numeric, total_amount numeric,
  status text DEFAULT 'pending',
  downstream_statement_id uuid REFERENCES downstream_statements(id) ON DELETE RESTRICT,
  source_file_id uuid REFERENCES source_files(id), notes text,
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS upstream_statement_lines (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  statement_id uuid NOT NULL REFERENCES upstream_statements(id) ON DELETE RESTRICT,
  line_no integer, product_id uuid REFERENCES products(id), product_name text,
  channel text, quantity numeric, unit text, unit_price numeric, amount numeric, notes text,
  created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON downstream_statements, upstream_statements, upstream_statement_lines TO ledger_app;
CREATE INDEX IF NOT EXISTS idx_us_status ON upstream_statements(status);
CREATE INDEX IF NOT EXISTS idx_us_ds ON upstream_statements(downstream_statement_id);
CREATE INDEX IF NOT EXISTS idx_usl_stmt ON upstream_statement_lines(statement_id);
