-- 公司财务报销/走账系统：一期 PostgreSQL 表结构
-- 目标：先把报销闭环、附件/发票、草稿、正式入库事实表落地。
-- 说明：使用 TEXT 主键，由后端生成可读前缀 ID，便于机器人调试和人工核对。

CREATE TABLE IF NOT EXISTS subject_type (
  id TEXT PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  can_own_project BOOLEAN NOT NULL DEFAULT false,
  can_receive_payment BOOLEAN NOT NULL DEFAULT false,
  can_make_payment BOOLEAN NOT NULL DEFAULT false,
  need_tax_info BOOLEAN NOT NULL DEFAULT false,
  is_active BOOLEAN NOT NULL DEFAULT true,
  sort_order INTEGER NOT NULL DEFAULT 0,
  remark TEXT
);

CREATE TABLE IF NOT EXISTS subject (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  short_name TEXT,
  subject_type_id TEXT NOT NULL REFERENCES subject_type(id),
  tax_name TEXT,
  tax_no TEXT,
  bank_info TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  remark TEXT,
  aliases JSONB NOT NULL DEFAULT '[]'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS source_category (
  id TEXT PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  default_direction TEXT,
  affects_person_ledger BOOLEAN NOT NULL DEFAULT false,
  affects_project_cost BOOLEAN NOT NULL DEFAULT false,
  affects_company_cash BOOLEAN NOT NULL DEFAULT false,
  remark TEXT
);

CREATE TABLE IF NOT EXISTS expense_category (
  id TEXT PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  parent_id TEXT REFERENCES expense_category(id),
  is_active BOOLEAN NOT NULL DEFAULT true,
  sort_order INTEGER NOT NULL DEFAULT 0,
  remark TEXT
);

CREATE TABLE IF NOT EXISTS project (
  id TEXT PRIMARY KEY,
  owner_subject_id TEXT NOT NULL REFERENCES subject(id),
  name TEXT NOT NULL,
  project_code TEXT UNIQUE,
  project_type TEXT NOT NULL DEFAULT 'BUSINESS',
  status TEXT NOT NULL DEFAULT 'ACTIVE',
  is_default_base_operation BOOLEAN NOT NULL DEFAULT false,
  parent_project_id TEXT REFERENCES project(id),
  aliases JSONB NOT NULL DEFAULT '[]'::jsonb,
  start_date DATE,
  end_date DATE,
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS cash_account (
  id TEXT PRIMARY KEY,
  owner_subject_id TEXT NOT NULL REFERENCES subject(id),
  name TEXT NOT NULL,
  account_type TEXT NOT NULL DEFAULT 'other',
  account_no_masked TEXT,
  opening_balance NUMERIC(18,2) NOT NULL DEFAULT 0,
  actual_balance NUMERIC(18,2),
  is_active BOOLEAN NOT NULL DEFAULT true,
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS attachment (
  id TEXT PRIMARY KEY,
  target_type TEXT,
  target_id TEXT,
  file_name TEXT NOT NULL,
  file_path TEXT,
  file_token TEXT,
  attachment_type TEXT,
  storage_provider TEXT,
  storage_bucket TEXT,
  storage_region TEXT,
  storage_key TEXT,
  content_type TEXT,
  file_size BIGINT,
  sha256 TEXT,
  etag TEXT,
  uploaded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  remark TEXT
);
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS storage_provider TEXT;
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS storage_bucket TEXT;
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS storage_region TEXT;
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS storage_key TEXT;
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS content_type TEXT;
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS file_size BIGINT;
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS sha256 TEXT;
ALTER TABLE attachment ADD COLUMN IF NOT EXISTS etag TEXT;
CREATE INDEX IF NOT EXISTS idx_attachment_target ON attachment(target_type, target_id);
CREATE INDEX IF NOT EXISTS idx_attachment_storage_key ON attachment(storage_provider, storage_key);

CREATE TABLE IF NOT EXISTS invoice (
  id TEXT PRIMARY KEY,
  invoice_no TEXT,
  invoice_type TEXT,
  buyer_subject_id TEXT REFERENCES subject(id),
  seller_subject_id TEXT REFERENCES subject(id),
  amount_total NUMERIC(18,2) NOT NULL DEFAULT 0,
  tax_amount NUMERIC(18,2),
  issued_at DATE,
  status TEXT NOT NULL DEFAULT 'PENDING_INVOICE',
  file_attachment_id TEXT REFERENCES attachment(id),
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_invoice_no ON invoice(invoice_no);

CREATE TABLE IF NOT EXISTS invoice_link (
  id TEXT PRIMARY KEY,
  invoice_id TEXT NOT NULL REFERENCES invoice(id) ON DELETE CASCADE,
  target_type TEXT NOT NULL,
  target_id TEXT NOT NULL,
  amount NUMERIC(18,2),
  remark TEXT
);
CREATE INDEX IF NOT EXISTS idx_invoice_link_target ON invoice_link(target_type, target_id);

CREATE TABLE IF NOT EXISTS intake_draft (
  id TEXT PRIMARY KEY,
  intent TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'DRAFT',
  normalized_payload JSONB NOT NULL,
  validation JSONB,
  preview JSONB,
  preview_text TEXT,
  human_summary TEXT,
  risk_flags JSONB,
  created_records JSONB,
  created_by_agent TEXT,
  operator_subject_id TEXT REFERENCES subject(id),
  approval_instance_id TEXT,
  idempotency_key TEXT UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_intake_draft_intent_status ON intake_draft(intent, status);
CREATE INDEX IF NOT EXISTS idx_intake_draft_operator ON intake_draft(operator_subject_id);


CREATE TABLE IF NOT EXISTS approval_callback_event (
  id TEXT PRIMARY KEY,
  provider TEXT NOT NULL DEFAULT 'feishu',
  event_key TEXT UNIQUE NOT NULL,
  approval_instance_id TEXT,
  event_type TEXT,
  status TEXT,
  raw_event JSONB,
  received_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_approval_callback_instance ON approval_callback_event(approval_instance_id);

CREATE TABLE IF NOT EXISTS reimbursement_order (
  id TEXT PRIMARY KEY,
  order_no TEXT UNIQUE NOT NULL,
  applicant_subject_id TEXT NOT NULL REFERENCES subject(id),
  payee_subject_id TEXT REFERENCES subject(id),
  default_owner_subject_id TEXT REFERENCES subject(id),
  status TEXT NOT NULL DEFAULT 'DRAFT',
  total_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
  submitted_at TIMESTAMPTZ,
  approved_at TIMESTAMPTZ,
  source_draft_id TEXT REFERENCES intake_draft(id),
  approval_provider TEXT,
  approval_code TEXT,
  approval_instance_id TEXT,
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE reimbursement_order ADD COLUMN IF NOT EXISTS approval_provider TEXT;
ALTER TABLE reimbursement_order ADD COLUMN IF NOT EXISTS approval_code TEXT;
ALTER TABLE reimbursement_order ADD COLUMN IF NOT EXISTS approval_instance_id TEXT;
ALTER TABLE reimbursement_order ADD COLUMN IF NOT EXISTS paid_at TIMESTAMPTZ;
CREATE INDEX IF NOT EXISTS idx_reim_applicant ON reimbursement_order(applicant_subject_id);
CREATE INDEX IF NOT EXISTS idx_reim_status ON reimbursement_order(status);
CREATE INDEX IF NOT EXISTS idx_reim_approval_instance ON reimbursement_order(approval_instance_id);

CREATE TABLE IF NOT EXISTS reimbursement_item (
  id TEXT PRIMARY KEY,
  reimbursement_order_id TEXT NOT NULL REFERENCES reimbursement_order(id) ON DELETE CASCADE,
  project_id TEXT NOT NULL REFERENCES project(id),
  owner_subject_id TEXT NOT NULL REFERENCES subject(id),
  expense_category_id TEXT REFERENCES expense_category(id),
  source_category_id TEXT REFERENCES source_category(id),
  advancer_subject_id TEXT REFERENCES subject(id),
  amount NUMERIC(18,2) NOT NULL,
  occurred_at DATE,
  invoice_status TEXT NOT NULL DEFAULT 'NO_INVOICE',
  description TEXT,
  payable_id TEXT,
  personal_ledger_effect BOOLEAN NOT NULL DEFAULT false,
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_reim_item_order ON reimbursement_item(reimbursement_order_id);
CREATE INDEX IF NOT EXISTS idx_reim_item_project ON reimbursement_item(project_id);
CREATE INDEX IF NOT EXISTS idx_reim_item_owner ON reimbursement_item(owner_subject_id);

CREATE TABLE IF NOT EXISTS fund_transaction (
  id TEXT PRIMARY KEY,
  transaction_no TEXT UNIQUE NOT NULL,
  direction TEXT NOT NULL,
  amount NUMERIC(18,2) NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  payer_subject_id TEXT REFERENCES subject(id),
  payer_cash_account_id TEXT REFERENCES cash_account(id),
  receiver_subject_id TEXT REFERENCES subject(id),
  receiver_cash_account_id TEXT REFERENCES cash_account(id),
  counterparty_subject_id TEXT REFERENCES subject(id),
  source_category_id TEXT REFERENCES source_category(id),
  status TEXT NOT NULL DEFAULT 'PENDING',
  proof_attachment_id TEXT REFERENCES attachment(id),
  original_transaction_id TEXT REFERENCES fund_transaction(id),
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_fund_direction_occurred ON fund_transaction(direction, occurred_at);
CREATE INDEX IF NOT EXISTS idx_fund_status ON fund_transaction(status);

CREATE TABLE IF NOT EXISTS reimbursement_payment (
  id TEXT PRIMARY KEY,
  reimbursement_order_id TEXT NOT NULL REFERENCES reimbursement_order(id) ON DELETE CASCADE,
  fund_transaction_id TEXT NOT NULL REFERENCES fund_transaction(id),
  amount NUMERIC(18,2) NOT NULL,
  paid_at TIMESTAMPTZ,
  operator_subject_id TEXT REFERENCES subject(id),
  idempotency_key TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  remark TEXT
);
ALTER TABLE reimbursement_payment ADD COLUMN IF NOT EXISTS operator_subject_id TEXT REFERENCES subject(id);
ALTER TABLE reimbursement_payment ADD COLUMN IF NOT EXISTS idempotency_key TEXT;
ALTER TABLE reimbursement_payment ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT now();
CREATE INDEX IF NOT EXISTS idx_reim_payment_order ON reimbursement_payment(reimbursement_order_id);
CREATE INDEX IF NOT EXISTS idx_reim_payment_fund ON reimbursement_payment(fund_transaction_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_reim_payment_idempotency ON reimbursement_payment(idempotency_key) WHERE idempotency_key IS NOT NULL;

-- 合同/订单：一期用统一 commercial_order 表承接（docs/一期业务事实模型 4.2）。
CREATE TABLE IF NOT EXISTS commercial_order (
  id TEXT PRIMARY KEY,
  order_no TEXT UNIQUE NOT NULL,
  external_no TEXT,
  project_id TEXT NOT NULL REFERENCES project(id),
  owner_subject_id TEXT NOT NULL REFERENCES subject(id),
  order_type TEXT NOT NULL DEFAULT 'contract',
  name TEXT NOT NULL,
  customer_subject_id TEXT REFERENCES subject(id),
  signing_subject_id TEXT REFERENCES subject(id),
  amount_total NUMERIC(18,2) NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'CNY',
  status TEXT NOT NULL DEFAULT 'SIGNED',
  signed_at DATE,
  source_draft_id TEXT REFERENCES intake_draft(id),
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_commercial_order_project ON commercial_order(project_id);
CREATE INDEX IF NOT EXISTS idx_commercial_order_owner ON commercial_order(owner_subject_id);
CREATE INDEX IF NOT EXISTS idx_commercial_order_status ON commercial_order(status);

-- 应收/收入计划：记录“应该收多少钱”，实收由核销表加和派生（docs/一期业务事实模型 5.1/5.3）。
CREATE TABLE IF NOT EXISTS receivable (
  id TEXT PRIMARY KEY,
  receivable_no TEXT UNIQUE NOT NULL,
  project_id TEXT NOT NULL REFERENCES project(id),
  commercial_order_id TEXT REFERENCES commercial_order(id),
  owner_subject_id TEXT NOT NULL REFERENCES subject(id),
  expected_receiver_subject_id TEXT REFERENCES subject(id),
  name TEXT NOT NULL,
  amount NUMERIC(18,2) NOT NULL,
  expected_date DATE,
  status TEXT NOT NULL DEFAULT 'CONFIRMED',
  source_category_id TEXT REFERENCES source_category(id),
  source_draft_id TEXT REFERENCES intake_draft(id),
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_receivable_project ON receivable(project_id);
CREATE INDEX IF NOT EXISTS idx_receivable_order ON receivable(commercial_order_id);
CREATE INDEX IF NOT EXISTS idx_receivable_status ON receivable(status);

CREATE TABLE IF NOT EXISTS receivable_settlement (
  id TEXT PRIMARY KEY,
  receivable_id TEXT NOT NULL REFERENCES receivable(id) ON DELETE CASCADE,
  fund_transaction_id TEXT NOT NULL REFERENCES fund_transaction(id),
  amount NUMERIC(18,2) NOT NULL,
  settled_at TIMESTAMPTZ,
  operator_subject_id TEXT REFERENCES subject(id),
  idempotency_key TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  remark TEXT
);
CREATE INDEX IF NOT EXISTS idx_recv_settlement_receivable ON receivable_settlement(receivable_id);
CREATE INDEX IF NOT EXISTS idx_recv_settlement_fund ON receivable_settlement(fund_transaction_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_recv_settlement_idempotency ON receivable_settlement(idempotency_key) WHERE idempotency_key IS NOT NULL;

-- 应付/费用义务：记录“已形成付款义务”，实付由核销表加和派生（docs/一期业务事实模型 6.1/6.3）。
CREATE TABLE IF NOT EXISTS payable (
  id TEXT PRIMARY KEY,
  payable_no TEXT UNIQUE NOT NULL,
  project_id TEXT NOT NULL REFERENCES project(id),
  commercial_order_id TEXT REFERENCES commercial_order(id),
  owner_subject_id TEXT NOT NULL REFERENCES subject(id),
  paying_subject_id TEXT REFERENCES subject(id),
  counterparty_subject_id TEXT REFERENCES subject(id),
  expense_category_id TEXT REFERENCES expense_category(id),
  source_category_id TEXT REFERENCES source_category(id),
  name TEXT NOT NULL,
  amount NUMERIC(18,2) NOT NULL,
  incurred_at DATE,
  due_date DATE,
  status TEXT NOT NULL DEFAULT 'CONFIRMED',
  invoice_status TEXT NOT NULL DEFAULT 'NO_INVOICE',
  source_draft_id TEXT REFERENCES intake_draft(id),
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_payable_project ON payable(project_id);
CREATE INDEX IF NOT EXISTS idx_payable_status ON payable(status);

CREATE TABLE IF NOT EXISTS payable_settlement (
  id TEXT PRIMARY KEY,
  payable_id TEXT NOT NULL REFERENCES payable(id) ON DELETE CASCADE,
  fund_transaction_id TEXT NOT NULL REFERENCES fund_transaction(id),
  amount NUMERIC(18,2) NOT NULL,
  settled_at TIMESTAMPTZ,
  operator_subject_id TEXT REFERENCES subject(id),
  idempotency_key TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  remark TEXT
);
CREATE INDEX IF NOT EXISTS idx_pay_settlement_payable ON payable_settlement(payable_id);
CREATE INDEX IF NOT EXISTS idx_pay_settlement_fund ON payable_settlement(fund_transaction_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_pay_settlement_idempotency ON payable_settlement(idempotency_key) WHERE idempotency_key IS NOT NULL;

-- 内部账户：个人账/公共账（需求文档 finance-system-requirements-20260612）。
-- 个人账/公共账是按公司主体维护的内部权益账户：应收分账流入、报销/工资/税费支出流出，余额由分录加和派生。
CREATE TABLE IF NOT EXISTS internal_account (
  id TEXT PRIMARY KEY,
  account_type TEXT NOT NULL,
  company_subject_id TEXT NOT NULL REFERENCES subject(id),
  holder_subject_id TEXT REFERENCES subject(id),
  name TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS uniq_public_account_per_company ON internal_account(company_subject_id) WHERE account_type = 'PUBLIC';
CREATE UNIQUE INDEX IF NOT EXISTS uniq_personal_account_per_holder ON internal_account(company_subject_id, holder_subject_id) WHERE account_type = 'PERSONAL';

CREATE TABLE IF NOT EXISTS internal_account_entry (
  id TEXT PRIMARY KEY,
  account_id TEXT NOT NULL REFERENCES internal_account(id),
  direction TEXT NOT NULL,
  amount NUMERIC(18,2) NOT NULL,
  entry_type TEXT NOT NULL,
  source_type TEXT,
  source_id TEXT,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  operator_subject_id TEXT REFERENCES subject(id),
  idempotency_key TEXT,
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_internal_entry_account ON internal_account_entry(account_id, occurred_at);
CREATE INDEX IF NOT EXISTS idx_internal_entry_source ON internal_account_entry(source_type, source_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_internal_entry_idempotency ON internal_account_entry(idempotency_key) WHERE idempotency_key IS NOT NULL;

-- 固定月度工资配置（需求 1.3/3.4/5.4/7.4）：按月生成出账分录和应付记录。
CREATE TABLE IF NOT EXISTS recurring_expense (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  payee_subject_id TEXT REFERENCES subject(id),
  payee_name TEXT,
  company_subject_id TEXT NOT NULL REFERENCES subject(id),
  funding_account_id TEXT NOT NULL REFERENCES internal_account(id),
  monthly_amount NUMERIC(18,2) NOT NULL,
  period TEXT NOT NULL DEFAULT 'MONTHLY',
  start_month TEXT NOT NULL,
  end_month TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS recurring_expense_run (
  id TEXT PRIMARY KEY,
  recurring_expense_id TEXT NOT NULL REFERENCES recurring_expense(id) ON DELETE CASCADE,
  month TEXT NOT NULL,
  entry_id TEXT REFERENCES internal_account_entry(id),
  payable_id TEXT REFERENCES payable(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(recurring_expense_id, month)
);

-- 额外支出登记（需求 1.4/7.5）：增值税、企业所得税等非合同支出，统一进公司公共账。
CREATE TABLE IF NOT EXISTS extra_expense (
  id TEXT PRIMARY KEY,
  company_subject_id TEXT NOT NULL REFERENCES subject(id),
  expense_type TEXT NOT NULL,
  name TEXT NOT NULL,
  amount NUMERIC(18,2) NOT NULL,
  occurred_at DATE,
  cash_account_id TEXT REFERENCES cash_account(id),
  entry_id TEXT REFERENCES internal_account_entry(id),
  registrar_subject_id TEXT REFERENCES subject(id),
  remark TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_extra_expense_company ON extra_expense(company_subject_id, occurred_at);

-- 合同台账增强字段（需求 6.2/6.5）。
ALTER TABLE commercial_order ADD COLUMN IF NOT EXISTS responsible_subject_id TEXT REFERENCES subject(id);
ALTER TABLE commercial_order ADD COLUMN IF NOT EXISTS responsible_name TEXT;
ALTER TABLE commercial_order ADD COLUMN IF NOT EXISTS important_notes TEXT;
ALTER TABLE commercial_order ADD COLUMN IF NOT EXISTS amount_note TEXT;

-- 应收应付付款条件说明（需求 5.2/5.3）。
ALTER TABLE receivable ADD COLUMN IF NOT EXISTS payment_condition TEXT;
ALTER TABLE payable ADD COLUMN IF NOT EXISTS payment_condition TEXT;

-- 报销来源与去向（需求 2.1/2.2）。
ALTER TABLE reimbursement_order ADD COLUMN IF NOT EXISTS funding_internal_account_id TEXT REFERENCES internal_account(id);
ALTER TABLE reimbursement_order ADD COLUMN IF NOT EXISTS payout_route TEXT;

CREATE TABLE IF NOT EXISTS audit_log (
  id TEXT PRIMARY KEY,
  target_type TEXT NOT NULL,
  target_id TEXT NOT NULL,
  action TEXT NOT NULL,
  before_json JSONB,
  after_json JSONB,
  operator_subject_id TEXT REFERENCES subject(id),
  operated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  remark TEXT
);
CREATE INDEX IF NOT EXISTS idx_audit_target ON audit_log(target_type, target_id);

DROP VIEW IF EXISTS reimbursement_order_summary;
CREATE VIEW reimbursement_order_summary AS
SELECT
  ro.*,
  COALESCE(SUM(rp.amount), 0)::NUMERIC(18,2) AS paid_amount,
  (ro.total_amount - COALESCE(SUM(rp.amount), 0))::NUMERIC(18,2) AS unpaid_amount
FROM reimbursement_order ro
LEFT JOIN reimbursement_payment rp ON rp.reimbursement_order_id = ro.id
GROUP BY ro.id;

-- 资金流水核销汇总：合并报销付款、应收核销、应付核销三类分配。
DROP VIEW IF EXISTS fund_transaction_allocation_summary;
CREATE VIEW fund_transaction_allocation_summary AS
SELECT
  ft.*,
  COALESCE(alloc.allocated, 0)::NUMERIC(18,2) AS allocated_amount,
  (ft.amount - COALESCE(alloc.allocated, 0))::NUMERIC(18,2) AS unallocated_amount
FROM fund_transaction ft
LEFT JOIN (
  SELECT fund_transaction_id, SUM(amount) AS allocated FROM (
    SELECT fund_transaction_id, amount FROM reimbursement_payment
    UNION ALL
    SELECT fund_transaction_id, amount FROM receivable_settlement
    UNION ALL
    SELECT fund_transaction_id, amount FROM payable_settlement
  ) all_alloc
  GROUP BY fund_transaction_id
) alloc ON alloc.fund_transaction_id = ft.id;

DROP VIEW IF EXISTS receivable_summary;
CREATE VIEW receivable_summary AS
SELECT
  r.*,
  COALESCE(SUM(rs.amount), 0)::NUMERIC(18,2) AS received_amount,
  (r.amount - COALESCE(SUM(rs.amount), 0))::NUMERIC(18,2) AS outstanding_amount
FROM receivable r
LEFT JOIN receivable_settlement rs ON rs.receivable_id = r.id
GROUP BY r.id;

DROP VIEW IF EXISTS payable_summary;
CREATE VIEW payable_summary AS
SELECT
  p.*,
  COALESCE(SUM(ps.amount), 0)::NUMERIC(18,2) AS paid_amount,
  (p.amount - COALESCE(SUM(ps.amount), 0))::NUMERIC(18,2) AS outstanding_amount
FROM payable p
LEFT JOIN payable_settlement ps ON ps.payable_id = p.id
GROUP BY p.id;

-- 合同回款进度：合同金额、关联应收、已收金额。
DROP VIEW IF EXISTS commercial_order_summary;
CREATE VIEW commercial_order_summary AS
SELECT
  co.*,
  COALESCE(recv.receivable_amount, 0)::NUMERIC(18,2) AS receivable_amount,
  COALESCE(recv.received_amount, 0)::NUMERIC(18,2) AS received_amount
FROM commercial_order co
LEFT JOIN (
  SELECT r.commercial_order_id,
         SUM(r.amount) AS receivable_amount,
         COALESCE(SUM(rs.received), 0) AS received_amount
  FROM receivable r
  LEFT JOIN (
    SELECT receivable_id, SUM(amount) AS received FROM receivable_settlement GROUP BY receivable_id
  ) rs ON rs.receivable_id = r.id
  WHERE r.status <> 'VOID'
  GROUP BY r.commercial_order_id
) recv ON recv.commercial_order_id = co.id;

-- 内部账户余额：分录 IN − OUT。
DROP VIEW IF EXISTS internal_account_balance_view;
CREATE VIEW internal_account_balance_view AS
SELECT
  ia.*,
  COALESCE(SUM(CASE WHEN e.direction = 'IN' THEN e.amount ELSE 0 END), 0)::NUMERIC(18,2) AS in_amount,
  COALESCE(SUM(CASE WHEN e.direction = 'OUT' THEN e.amount ELSE 0 END), 0)::NUMERIC(18,2) AS out_amount,
  COALESCE(SUM(CASE WHEN e.direction = 'IN' THEN e.amount ELSE -e.amount END), 0)::NUMERIC(18,2) AS balance,
  COUNT(e.id)::int AS entry_count
FROM internal_account ia
LEFT JOIN internal_account_entry e ON e.account_id = ia.id
GROUP BY ia.id;

-- 资金账户余额：期初 + 流入 − 流出（REVERSED 流水不计）。
DROP VIEW IF EXISTS cash_account_balance_view;
CREATE VIEW cash_account_balance_view AS
SELECT
  ca.*,
  COALESCE(inflow.total, 0)::NUMERIC(18,2) AS inflow_amount,
  COALESCE(outflow.total, 0)::NUMERIC(18,2) AS outflow_amount,
  (ca.opening_balance + COALESCE(inflow.total, 0) - COALESCE(outflow.total, 0))::NUMERIC(18,2) AS computed_balance
FROM cash_account ca
LEFT JOIN (
  SELECT receiver_cash_account_id AS account_id, SUM(amount) AS total
  FROM fund_transaction WHERE direction = 'IN' AND status <> 'REVERSED' AND receiver_cash_account_id IS NOT NULL
  GROUP BY receiver_cash_account_id
) inflow ON inflow.account_id = ca.id
LEFT JOIN (
  SELECT payer_cash_account_id AS account_id, SUM(amount) AS total
  FROM fund_transaction WHERE direction = 'OUT' AND status <> 'REVERSED' AND payer_cash_account_id IS NOT NULL
  GROUP BY payer_cash_account_id
) outflow ON outflow.account_id = ca.id;
