SET client_encoding = 'UTF8';

-- ============================================================
-- 云海财务系统 - PostgreSQL 完整表结构
-- ============================================================

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(255),
  role VARCHAR(50),
  avatar TEXT,
  passport TEXT,
  driver_license TEXT,
  password_hash VARCHAR(255),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255),
  remark TEXT,
  contact_person VARCHAR(255),
  phone VARCHAR(255),
  email VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS projects (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  project_code VARCHAR(255),
  customer_id INTEGER REFERENCES customers(id),
  project_manager_id INTEGER REFERENCES users(id),
  contract_amount NUMERIC,
  start_date DATE,
  end_date DATE,
  description TEXT,
  status VARCHAR(50) DEFAULT 'planning',
  location VARCHAR(255),
  type_template_id INTEGER,
  current_phase VARCHAR(50),
  phase_progress NUMERIC(5,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS contacts (
  id SERIAL PRIMARY KEY,
  entity_id INTEGER NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  name VARCHAR(255),
  position VARCHAR(255),
  phone VARCHAR(255),
  is_primary INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS suppliers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT,
  contact_person VARCHAR(255),
  phone VARCHAR(255),
  email VARCHAR(255),
  product_category VARCHAR(255),
  supplier_source VARCHAR(100),
  supply_category TEXT,
  country TEXT,
  remark TEXT,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS supplier_payment_infos (
  id SERIAL PRIMARY KEY,
  supplier_id INTEGER REFERENCES suppliers(id) ON DELETE CASCADE,
  account_name TEXT,
  bank_account TEXT,
  bank_name TEXT,
  qr_code TEXT,
  is_primary INTEGER DEFAULT 0,
  is_default BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subcontractors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT,
  contact_person VARCHAR(255),
  phone VARCHAR(255),
  email VARCHAR(255),
  specialty TEXT,
  category TEXT,
  country TEXT,
  remark TEXT,
  score INTEGER DEFAULT 100,
  initial_score INTEGER DEFAULT 100,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subcontractor_payment_infos (
  id SERIAL PRIMARY KEY,
  subcontractor_id INTEGER REFERENCES subcontractors(id) ON DELETE CASCADE,
  account_name TEXT,
  bank_name TEXT,
  bank_account TEXT,
  qr_code TEXT,
  is_primary INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customer_payment_infos (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
  account_name TEXT,
  bank_name TEXT,
  bank_account TEXT,
  qr_code TEXT,
  is_primary INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS product_categories (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  parent_id INTEGER REFERENCES product_categories(id)
);

CREATE TABLE IF NOT EXISTS products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  model TEXT,
  category_id INTEGER REFERENCES product_categories(id),
  category_name TEXT,
  unit TEXT DEFAULT '件',
  cost_price REAL,
  price REAL DEFAULT 0,
  brand TEXT,
  specification TEXT,
  source TEXT DEFAULT '老挝',
  remark TEXT,
  stock_quantity REAL DEFAULT 0,
  stock_warning REAL DEFAULT 0,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS exchange_rates (
  id SERIAL PRIMARY KEY,
  currency_code TEXT NOT NULL,
  to_currency_code TEXT NOT NULL,
  rate REAL NOT NULL,
  effective_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS advances (
  id SERIAL PRIMARY KEY,
  applicant_id INTEGER REFERENCES users(id),
  project_id INTEGER REFERENCES projects(id),
  amount REAL NOT NULL DEFAULT 0,
  currency TEXT DEFAULT 'CNY',
  amount_cny REAL DEFAULT 0,
  reason TEXT,
  advance_date DATE,
  advance_code TEXT UNIQUE,
  status TEXT DEFAULT 'pending',
  applicant TEXT,
  attachments TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS reimbursements (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  project_id INTEGER REFERENCES projects(id),
  amount REAL NOT NULL DEFAULT 0,
  currency TEXT DEFAULT 'CNY',
  amount_cny REAL DEFAULT 0,
  reason TEXT,
  reimbursement_date DATE,
  reimbursement_code TEXT UNIQUE,
  status TEXT DEFAULT 'pending',
  applicant TEXT,
  expense_type TEXT,
  detail_items TEXT,
  attachments TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_requests (
  id SERIAL PRIMARY KEY,
  request_code TEXT UNIQUE,
  applicant TEXT,
  payment_date DATE,
  payee TEXT,
  bank_account TEXT,
  bank_name TEXT,
  amount REAL DEFAULT 0,
  currency TEXT DEFAULT 'CNY',
  reason TEXT,
  status TEXT DEFAULT 'pending',
  detail_items TEXT,
  attachments TEXT,
  payee_type TEXT,
  payee_id INTEGER,
  expense_type TEXT DEFAULT 'company',
  expense_category TEXT,
  project_id INTEGER REFERENCES projects(id),
  purchase_request_id INTEGER,
  purchase_order_id INTEGER,
  payment_type TEXT DEFAULT 'company',
  logistics_company_id INTEGER,
  logistics_document_url TEXT,
  driver_phone TEXT,
  cargo_weight REAL,
  transport_distance REAL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS verifications (
  id SERIAL PRIMARY KEY,
  advance_id INTEGER REFERENCES advances(id),
  amount REAL DEFAULT 0,
  currency TEXT DEFAULT 'CNY',
  reason TEXT,
  verification_date DATE,
  verification_code TEXT UNIQUE,
  status TEXT DEFAULT 'pending',
  applicant TEXT,
  advance_code TEXT,
  advance_amount REAL,
  detail_items TEXT,
  attachments TEXT,
  expense_type TEXT,
  project_id INTEGER REFERENCES projects(id),
  settlement TEXT,
  settlement_amount REAL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS executions (
  id SERIAL PRIMARY KEY,
  apply_id INTEGER,
  apply_type TEXT,
  action TEXT,
  execute_method TEXT,
  voucher_no TEXT,
  remark TEXT,
  reject_reason TEXT,
  voucher_files TEXT,
  operator TEXT,
  operator_role TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS purchase_requests (
  id SERIAL PRIMARY KEY,
  request_code TEXT UNIQUE NOT NULL,
  project_id INTEGER NOT NULL REFERENCES projects(id),
  applicant TEXT,
  request_date DATE,
  supplier_id INTEGER REFERENCES suppliers(id),
  supplier_name TEXT,
  expense_category TEXT,
  purchase_type TEXT DEFAULT 'inventory',
  total_amount REAL NOT NULL DEFAULT 0,
  currency TEXT DEFAULT 'CNY',
  status TEXT DEFAULT 'pending',
  remark TEXT,
  remarks TEXT,
  brief_description TEXT,
  attachments TEXT,
  expected_date DATE,
  execute_date DATE,
  execute_method TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS purchase_request_items (
  id SERIAL PRIMARY KEY,
  purchase_request_id INTEGER NOT NULL REFERENCES purchase_requests(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id),
  product_name TEXT NOT NULL,
  specification TEXT,
  unit TEXT,
  quantity REAL NOT NULL DEFAULT 0,
  unit_price REAL NOT NULL DEFAULT 0,
  total_price REAL NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS purchase_orders (
  id SERIAL PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  purchase_request_id INTEGER REFERENCES purchase_requests(id),
  project_id INTEGER REFERENCES projects(id),
  supplier_id INTEGER REFERENCES suppliers(id),
  supplier_country TEXT DEFAULT 'Laos',
  currency TEXT DEFAULT 'CNY',
  status TEXT DEFAULT 'draft',
  estimated_amount REAL DEFAULT 0,
  paid_amount REAL DEFAULT 0,
  contract_url TEXT,
  quotation_url TEXT,
  actual_delivery_date DATE,
  expected_delivery_date DATE,
  receiving_status VARCHAR(20) DEFAULT 'pending',
  remark TEXT,
  created_by TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS purchase_order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES purchase_orders(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id),
  product_name TEXT,
  specification TEXT,
  unit TEXT,
  quantity REAL DEFAULT 0,
  unit_price REAL DEFAULT 0,
  total_price REAL DEFAULT 0,
  received_quantity REAL DEFAULT 0,
  verified_quantity REAL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_plans (
  id SERIAL PRIMARY KEY,
  purchase_order_id INTEGER REFERENCES purchase_orders(id),
  project_id INTEGER REFERENCES projects(id),
  plan_name TEXT,
  stage TEXT,
  planned_date DATE,
  planned_amount REAL,
  planned_percentage REAL,
  actual_amount REAL DEFAULT 0,
  actual_date DATE,
  payment_request_id INTEGER,
  reminder_days INTEGER DEFAULT 3,
  amount REAL DEFAULT 0,
  due_date DATE,
  status TEXT DEFAULT 'pending',
  remark TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_nodes (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id),
  name TEXT,
  amount REAL DEFAULT 0,
  due_date DATE,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payment_records (
  id SERIAL PRIMARY KEY,
  node_id INTEGER REFERENCES payment_nodes(id),
  amount REAL DEFAULT 0,
  payment_date DATE,
  method TEXT,
  status TEXT DEFAULT 'completed',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory_records (
  id SERIAL PRIMARY KEY,
  record_type TEXT NOT NULL,
  project_id INTEGER REFERENCES projects(id),
  purchase_request_id INTEGER REFERENCES purchase_requests(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity REAL NOT NULL DEFAULT 0,
  unit_price REAL,
  total_amount REAL,
  record_date DATE NOT NULL,
  operator TEXT,
  remark TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS logistics_companies (
  id SERIAL PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  address TEXT,
  phone TEXT,
  email TEXT,
  quotation_description TEXT,
  status TEXT DEFAULT 'active',
  remark TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS logistics_company_contacts (
  id SERIAL PRIMARY KEY,
  logistics_company_id INTEGER REFERENCES logistics_companies(id) ON DELETE CASCADE,
  name TEXT,
  phone TEXT,
  position TEXT,
  is_primary INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS logistics_company_bank_accounts (
  id SERIAL PRIMARY KEY,
  logistics_company_id INTEGER REFERENCES logistics_companies(id) ON DELETE CASCADE,
  account_name TEXT,
  bank_name TEXT,
  account_number TEXT,
  is_default INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS construction_logs (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  log_date DATE NOT NULL,
  weather TEXT,
  recorded_by TEXT,
  work_content TEXT,
  next_plan TEXT,
  issues TEXT,
  photos TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS project_type_templates (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  phases JSONB NOT NULL DEFAULT '[]',
  is_system BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS project_phases (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  template_key VARCHAR(50),
  phase_name VARCHAR(100) NOT NULL,
  phase_order INTEGER NOT NULL,
  phase_type VARCHAR(20) DEFAULT 'serial',
  depends_on INTEGER[],
  status VARCHAR(20) DEFAULT 'pending',
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  completed_by INTEGER REFERENCES users(id),
  remark TEXT,
  photos TEXT[],
  sub_items JSONB DEFAULT '[]',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS budget_projects (
  id SERIAL PRIMARY KEY,
  budget_code TEXT,
  name TEXT NOT NULL,
  customer_id INTEGER REFERENCES customers(id),
  business_manager_id INTEGER REFERENCES users(id),
  location TEXT,
  survey_date DATE,
  intermediary_name TEXT,
  intermediary_fee_type TEXT DEFAULT 'fixed',
  intermediary_fee REAL DEFAULT 0,
  customer_requirements TEXT,
  survey_notes TEXT,
  status TEXT DEFAULT 'draft',
  total_amount REAL DEFAULT 0,
  currency TEXT DEFAULT 'CNY',
  remark TEXT,
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS budget_quotations (
  id SERIAL PRIMARY KEY,
  budget_project_id INTEGER REFERENCES budget_projects(id) ON DELETE CASCADE,
  version INTEGER DEFAULT 1,
  quotation_date DATE,
  amount REAL DEFAULT 0,
  currency TEXT DEFAULT 'CNY',
  file_url TEXT,
  notes TEXT,
  status TEXT DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS budget_attachments (
  id SERIAL PRIMARY KEY,
  budget_project_id INTEGER REFERENCES budget_projects(id) ON DELETE CASCADE,
  file_url TEXT,
  file_type TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS project_contracts (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  contract_no TEXT,
  contract_amount REAL,
  sign_date DATE,
  file_url TEXT,
  remark TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS project_milestones (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  milestone_name TEXT,
  amount REAL DEFAULT 0,
  due_date DATE,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS project_receipts (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id),
  milestone_id INTEGER REFERENCES project_milestones(id),
  amount REAL DEFAULT 0,
  receipt_date DATE,
  financial_record_id INTEGER,
  remark TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subcontractor_events (
  id SERIAL PRIMARY KEY,
  subcontractor_id INTEGER REFERENCES subcontractors(id),
  project_id INTEGER REFERENCES projects(id),
  event_type VARCHAR(10),
  score_change INTEGER,
  event_date DATE,
  title VARCHAR(200),
  description TEXT,
  created_by INTEGER REFERENCES users(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 财务分类与账本（与 migrations 006/007 一致）
CREATE TABLE IF NOT EXISTS expense_categories (
  id SERIAL PRIMARY KEY,
  category_level1 TEXT NOT NULL,
  category_level2 TEXT NOT NULL,
  label TEXT NOT NULL,
  description TEXT,
  sort_order INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(category_level1, category_level2)
);

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_products_category ON products(category_id);
CREATE INDEX IF NOT EXISTS idx_purchase_requests_project ON purchase_requests(project_id);
CREATE INDEX IF NOT EXISTS idx_financial_records_project ON financial_records(project_id);
CREATE INDEX IF NOT EXISTS idx_expense_categories_level1 ON expense_categories(category_level1);

-- 兼容已存在的旧 projects 表，补充缺失字段
ALTER TABLE projects ADD COLUMN IF NOT EXISTS start_date DATE;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS end_date DATE;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS type_template_id INTEGER;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS current_phase VARCHAR(50);
ALTER TABLE projects ADD COLUMN IF NOT EXISTS phase_progress NUMERIC(5,2) DEFAULT 0;

-- 兼容已存在库：补充路由依赖字段
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
UPDATE users SET is_active = true WHERE is_active IS NULL;

ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS budget_code TEXT;
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS business_manager_id INTEGER REFERENCES users(id);
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS location TEXT;
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS survey_date DATE;
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS intermediary_name TEXT;
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS intermediary_fee_type TEXT DEFAULT 'fixed';
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS intermediary_fee REAL DEFAULT 0;
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS customer_requirements TEXT;
ALTER TABLE budget_projects ADD COLUMN IF NOT EXISTS survey_notes TEXT;

ALTER TABLE reimbursements ADD COLUMN IF NOT EXISTS approval_remark TEXT;