-- 创建统一财务账本表
CREATE TABLE IF NOT EXISTS financial_records (
  id SERIAL PRIMARY KEY,
  record_code TEXT UNIQUE NOT NULL,

  txn_type TEXT NOT NULL CHECK (txn_type IN ('income', 'expense')),
  category_level1 TEXT NOT NULL,
  category_level2 TEXT NOT NULL,

  project_id INTEGER REFERENCES projects(id),
  user_id INTEGER REFERENCES users(id),
  user_name TEXT,

  amount_original REAL NOT NULL DEFAULT 0,
  currency TEXT NOT NULL DEFAULT 'CNY',
  exchange_rate REAL NOT NULL DEFAULT 1,
  amount_cny REAL NOT NULL DEFAULT 0,

  record_date DATE NOT NULL,

  counterparty_name TEXT,
  counterparty_type TEXT,
  counterparty_id INTEGER,

  source TEXT NOT NULL DEFAULT 'manual',
  source_id INTEGER,
  source_code TEXT,

  description TEXT,
  voucher_no TEXT,
  attachments TEXT,

  status TEXT NOT NULL DEFAULT 'confirmed' CHECK (status IN ('confirmed', 'pending', 'voided')),

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_financial_records_project ON financial_records(project_id);
CREATE INDEX IF NOT EXISTS idx_financial_records_user ON financial_records(user_id);
CREATE INDEX IF NOT EXISTS idx_financial_records_date ON financial_records(record_date);
CREATE INDEX IF NOT EXISTS idx_financial_records_type ON financial_records(txn_type, category_level1, category_level2);
CREATE INDEX IF NOT EXISTS idx_financial_records_source ON financial_records(source, source_id);
CREATE INDEX IF NOT EXISTS idx_financial_records_status ON financial_records(status);