/**
 * 星环OPC中心 — 数据库表结构定义
 */

export const OPC_TABLES = {
  companies: `
    CREATE TABLE IF NOT EXISTS opc_companies (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      industry TEXT NOT NULL DEFAULT '',
      owner_name TEXT NOT NULL,
      owner_contact TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'pending',
      registered_capital REAL NOT NULL DEFAULT 0,
      description TEXT NOT NULL DEFAULT '',
      onboarding_data TEXT DEFAULT '{}',
      onboarding_stage TEXT DEFAULT '',
      onboarding_completed INTEGER DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `,

  employees: `
    CREATE TABLE IF NOT EXISTS opc_employees (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      name TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'general',
      skills TEXT NOT NULL DEFAULT '[]',
      status TEXT NOT NULL DEFAULT 'active',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  transactions: `
    CREATE TABLE IF NOT EXISTS opc_transactions (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      type TEXT NOT NULL,
      category TEXT NOT NULL DEFAULT 'other',
      amount REAL NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      counterparty TEXT NOT NULL DEFAULT '',
      transaction_date TEXT NOT NULL DEFAULT (date('now')),
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  contacts: `
    CREATE TABLE IF NOT EXISTS opc_contacts (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      name TEXT NOT NULL,
      phone TEXT NOT NULL DEFAULT '',
      email TEXT NOT NULL DEFAULT '',
      company_name TEXT NOT NULL DEFAULT '',
      tags TEXT NOT NULL DEFAULT '[]',
      notes TEXT NOT NULL DEFAULT '',
      last_contact_date TEXT NOT NULL DEFAULT (date('now')),
      pipeline_stage TEXT NOT NULL DEFAULT 'lead',
      follow_up_date TEXT NOT NULL DEFAULT '',
      deal_value REAL NOT NULL DEFAULT 0,
      source TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE(company_id, name),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  contact_interactions: `
    CREATE TABLE IF NOT EXISTS opc_contact_interactions (
      id TEXT PRIMARY KEY,
      contact_id TEXT NOT NULL,
      company_id TEXT NOT NULL,
      interaction_type TEXT NOT NULL DEFAULT 'note',
      content TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (contact_id) REFERENCES opc_contacts(id)
    )
  `,
  // ── Phase 2 表 ────────────────────────────────────────────

  invoices: `
    CREATE TABLE IF NOT EXISTS opc_invoices (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      invoice_number TEXT NOT NULL DEFAULT '',
      type TEXT NOT NULL DEFAULT 'sales',
      counterparty TEXT NOT NULL DEFAULT '',
      amount REAL NOT NULL DEFAULT 0,
      tax_rate REAL NOT NULL DEFAULT 0,
      tax_amount REAL NOT NULL DEFAULT 0,
      total_amount REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'draft',
      issue_date TEXT NOT NULL DEFAULT (date('now')),
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  tax_filings: `
    CREATE TABLE IF NOT EXISTS opc_tax_filings (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      period TEXT NOT NULL,
      tax_type TEXT NOT NULL DEFAULT 'vat',
      revenue REAL NOT NULL DEFAULT 0,
      deductible REAL NOT NULL DEFAULT 0,
      tax_amount REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'pending',
      due_date TEXT NOT NULL DEFAULT '',
      filed_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  contracts: `
    CREATE TABLE IF NOT EXISTS opc_contracts (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      counterparty TEXT NOT NULL DEFAULT '',
      contract_type TEXT NOT NULL DEFAULT '',
      direction TEXT NOT NULL DEFAULT 'sales',
      amount REAL NOT NULL DEFAULT 0,
      start_date TEXT NOT NULL DEFAULT '',
      end_date TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'draft',
      key_terms TEXT NOT NULL DEFAULT '',
      risk_notes TEXT NOT NULL DEFAULT '',
      reminder_date TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  hr_records: `
    CREATE TABLE IF NOT EXISTS opc_hr_records (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      employee_name TEXT NOT NULL,
      position TEXT NOT NULL DEFAULT '',
      salary REAL NOT NULL DEFAULT 0,
      social_insurance REAL NOT NULL DEFAULT 0,
      housing_fund REAL NOT NULL DEFAULT 0,
      start_date TEXT NOT NULL DEFAULT '',
      end_date TEXT NOT NULL DEFAULT '',
      contract_type TEXT NOT NULL DEFAULT 'full_time',
      status TEXT NOT NULL DEFAULT 'active',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  media_content: `
    CREATE TABLE IF NOT EXISTS opc_media_content (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      platform TEXT NOT NULL DEFAULT '',
      content_type TEXT NOT NULL DEFAULT 'article',
      content TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'draft',
      scheduled_date TEXT NOT NULL DEFAULT '',
      published_date TEXT NOT NULL DEFAULT '',
      tags TEXT NOT NULL DEFAULT '[]',
      metrics TEXT NOT NULL DEFAULT '{}',
      reviewer TEXT NOT NULL DEFAULT '',
      review_notes TEXT NOT NULL DEFAULT '',
      approved_at TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  projects: `
    CREATE TABLE IF NOT EXISTS opc_projects (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'planning',
      start_date TEXT NOT NULL DEFAULT '',
      end_date TEXT NOT NULL DEFAULT '',
      budget REAL NOT NULL DEFAULT 0,
      spent REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  tasks: `
    CREATE TABLE IF NOT EXISTS opc_tasks (
      id TEXT PRIMARY KEY,
      project_id TEXT NOT NULL,
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      assignee TEXT NOT NULL DEFAULT '',
      priority TEXT NOT NULL DEFAULT 'medium',
      status TEXT NOT NULL DEFAULT 'todo',
      due_date TEXT NOT NULL DEFAULT '',
      hours_estimated REAL NOT NULL DEFAULT 0,
      hours_actual REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (project_id) REFERENCES opc_projects(id),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── Phase 3 表 ────────────────────────────────────────────

  investment_rounds: `
    CREATE TABLE IF NOT EXISTS opc_investment_rounds (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      round_name TEXT NOT NULL DEFAULT '',
      amount REAL NOT NULL DEFAULT 0,
      valuation_pre REAL NOT NULL DEFAULT 0,
      valuation_post REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'planning',
      lead_investor TEXT NOT NULL DEFAULT '',
      close_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  investors: `
    CREATE TABLE IF NOT EXISTS opc_investors (
      id TEXT PRIMARY KEY,
      round_id TEXT NOT NULL,
      company_id TEXT NOT NULL,
      name TEXT NOT NULL,
      type TEXT NOT NULL DEFAULT 'individual',
      amount REAL NOT NULL DEFAULT 0,
      equity_percent REAL NOT NULL DEFAULT 0,
      contact TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (round_id) REFERENCES opc_investment_rounds(id),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  services: `
    CREATE TABLE IF NOT EXISTS opc_services (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      name TEXT NOT NULL,
      category TEXT NOT NULL DEFAULT '',
      provider TEXT NOT NULL DEFAULT '',
      unit_price REAL NOT NULL DEFAULT 0,
      billing_cycle TEXT NOT NULL DEFAULT 'monthly',
      status TEXT NOT NULL DEFAULT 'active',
      description TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  procurement_orders: `
    CREATE TABLE IF NOT EXISTS opc_procurement_orders (
      id TEXT PRIMARY KEY,
      service_id TEXT NOT NULL DEFAULT '',
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      amount REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'pending',
      order_date TEXT NOT NULL DEFAULT (date('now')),
      delivery_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  milestones: `
    CREATE TABLE IF NOT EXISTS opc_milestones (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      category TEXT NOT NULL DEFAULT 'business',
      target_date TEXT NOT NULL DEFAULT '',
      completed_date TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'pending',
      description TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  lifecycle_events: `
    CREATE TABLE IF NOT EXISTS opc_lifecycle_events (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      event_type TEXT NOT NULL DEFAULT '',
      title TEXT NOT NULL,
      event_date TEXT NOT NULL DEFAULT (date('now')),
      impact TEXT NOT NULL DEFAULT '',
      description TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  metrics: `
    CREATE TABLE IF NOT EXISTS opc_metrics (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      name TEXT NOT NULL,
      value REAL NOT NULL DEFAULT 0,
      unit TEXT NOT NULL DEFAULT '',
      category TEXT NOT NULL DEFAULT '',
      recorded_at TEXT NOT NULL DEFAULT (datetime('now')),
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  alerts: `
    CREATE TABLE IF NOT EXISTS opc_alerts (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      severity TEXT NOT NULL DEFAULT 'info',
      category TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'active',
      message TEXT NOT NULL DEFAULT '',
      resolved_at TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  tool_config: `
    CREATE TABLE IF NOT EXISTS opc_tool_config (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL DEFAULT ''
    )
  `,

  // ── 资金闭环关键业务表 ─────────────────────────────────────

  acquisition_cases: `
    CREATE TABLE IF NOT EXISTS opc_acquisition_cases (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      acquirer_id TEXT NOT NULL DEFAULT '',
      case_type TEXT NOT NULL DEFAULT 'acquisition',
      status TEXT NOT NULL DEFAULT 'evaluating',
      trigger_reason TEXT NOT NULL DEFAULT '',
      acquisition_price REAL NOT NULL DEFAULT 0,
      loss_amount REAL NOT NULL DEFAULT 0,
      tax_deduction REAL NOT NULL DEFAULT 0,
      initiated_date TEXT NOT NULL DEFAULT (date('now')),
      closed_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  asset_packages: `
    CREATE TABLE IF NOT EXISTS opc_asset_packages (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'assembling',
      total_valuation REAL NOT NULL DEFAULT 0,
      company_count INTEGER NOT NULL DEFAULT 0,
      sci_tech_certified INTEGER NOT NULL DEFAULT 0,
      assembled_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
  `,

  asset_package_items: `
    CREATE TABLE IF NOT EXISTS opc_asset_package_items (
      id TEXT PRIMARY KEY,
      package_id TEXT NOT NULL,
      company_id TEXT NOT NULL,
      acquisition_case_id TEXT NOT NULL DEFAULT '',
      valuation REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (package_id) REFERENCES opc_asset_packages(id),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  ct_transfers: `
    CREATE TABLE IF NOT EXISTS opc_ct_transfers (
      id TEXT PRIMARY KEY,
      package_id TEXT NOT NULL,
      ct_company TEXT NOT NULL DEFAULT '',
      transfer_price REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'negotiating',
      sci_loan_target REAL NOT NULL DEFAULT 0,
      sci_loan_actual REAL NOT NULL DEFAULT 0,
      transfer_date TEXT NOT NULL DEFAULT '',
      loan_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (package_id) REFERENCES opc_asset_packages(id)
    )
  `,

  financing_fees: `
    CREATE TABLE IF NOT EXISTS opc_financing_fees (
      id TEXT PRIMARY KEY,
      transfer_id TEXT NOT NULL,
      fee_rate REAL NOT NULL DEFAULT 0,
      fee_amount REAL NOT NULL DEFAULT 0,
      base_amount REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'pending',
      invoiced INTEGER NOT NULL DEFAULT 0,
      paid_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (transfer_id) REFERENCES opc_ct_transfers(id)
    )
  `,

  // ── AI 员工岗位配置表 ─────────────────────────────────────

  staff_config: `
    CREATE TABLE IF NOT EXISTS opc_staff_config (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      role TEXT NOT NULL,
      role_name TEXT NOT NULL DEFAULT '',
      enabled INTEGER NOT NULL DEFAULT 1,
      system_prompt TEXT NOT NULL DEFAULT '',
      skills TEXT NOT NULL DEFAULT '[]',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE(company_id, role),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── AI 员工任务追踪表 ───────────────────────────────────────────

  staff_tasks: `
    CREATE TABLE IF NOT EXISTS opc_staff_tasks (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      staff_role TEXT NOT NULL,
      title TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'pending',
      priority TEXT NOT NULL DEFAULT 'normal',
      task_type TEXT NOT NULL DEFAULT 'manual',
      schedule TEXT NOT NULL DEFAULT 'on_demand',
      result_summary TEXT NOT NULL DEFAULT '',
      result_data TEXT NOT NULL DEFAULT '{}',
      session_key TEXT NOT NULL DEFAULT '',
      assigned_at TEXT NOT NULL DEFAULT (datetime('now')),
      started_at TEXT NOT NULL DEFAULT '',
      completed_at TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── 主动智能系统表 ─────────────────────────────────────────────

  insights: `
    CREATE TABLE IF NOT EXISTS opc_insights (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      insight_type TEXT NOT NULL DEFAULT 'data_gap',
      category TEXT NOT NULL DEFAULT 'ops',
      priority INTEGER NOT NULL DEFAULT 50,
      title TEXT NOT NULL,
      message TEXT NOT NULL DEFAULT '',
      action_hint TEXT NOT NULL DEFAULT '',
      staff_role TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'active',
      expires_at TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  celebrations: `
    CREATE TABLE IF NOT EXISTS opc_celebrations (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      celebration_type TEXT NOT NULL,
      title TEXT NOT NULL,
      message TEXT NOT NULL DEFAULT '',
      metric_value REAL NOT NULL DEFAULT 0,
      shown INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  company_stage: `
    CREATE TABLE IF NOT EXISTS opc_company_stage (
      company_id TEXT PRIMARY KEY,
      stage TEXT NOT NULL DEFAULT 'idea',
      stage_label TEXT NOT NULL DEFAULT '构想阶段',
      confidence REAL NOT NULL DEFAULT 0,
      factors_json TEXT NOT NULL DEFAULT '{}',
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  briefings: `
    CREATE TABLE IF NOT EXISTS opc_briefings (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      briefing_date TEXT NOT NULL DEFAULT (date('now')),
      stage TEXT NOT NULL DEFAULT '',
      health_score REAL NOT NULL DEFAULT 0,
      summary_json TEXT NOT NULL DEFAULT '{}',
      insights_json TEXT NOT NULL DEFAULT '[]',
      next_steps_json TEXT NOT NULL DEFAULT '[]',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE(company_id, briefing_date),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── 文档生成表 ─────────────────────────────────────────────────

  documents: `
    CREATE TABLE IF NOT EXISTS opc_documents (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      doc_type TEXT NOT NULL DEFAULT 'contract',
      title TEXT NOT NULL,
      template_key TEXT NOT NULL DEFAULT '',
      content TEXT NOT NULL DEFAULT '',
      variables TEXT NOT NULL DEFAULT '{}',
      version INTEGER NOT NULL DEFAULT 1,
      status TEXT NOT NULL DEFAULT 'draft',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── 发票明细行表 ───────────────────────────────────────────────

  invoice_items: `
    CREATE TABLE IF NOT EXISTS opc_invoice_items (
      id TEXT PRIMARY KEY,
      invoice_id TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      quantity REAL NOT NULL DEFAULT 1,
      unit_price REAL NOT NULL DEFAULT 0,
      amount REAL NOT NULL DEFAULT 0,
      tax_rate REAL NOT NULL DEFAULT 0,
      tax_amount REAL NOT NULL DEFAULT 0,
      sort_order INTEGER NOT NULL DEFAULT 0,
      FOREIGN KEY (invoice_id) REFERENCES opc_invoices(id)
    )
  `,

  // ── OPB 画布表 ───────────────────────────────────────────────

  opb_canvas: `
    CREATE TABLE IF NOT EXISTS opc_opb_canvas (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL UNIQUE,
      track TEXT NOT NULL DEFAULT '',
      target_customer TEXT NOT NULL DEFAULT '',
      pain_point TEXT NOT NULL DEFAULT '',
      solution TEXT NOT NULL DEFAULT '',
      unique_value TEXT NOT NULL DEFAULT '',
      channels TEXT NOT NULL DEFAULT '',
      revenue_model TEXT NOT NULL DEFAULT '',
      cost_structure TEXT NOT NULL DEFAULT '',
      key_resources TEXT NOT NULL DEFAULT '',
      key_activities TEXT NOT NULL DEFAULT '',
      key_partners TEXT NOT NULL DEFAULT '',
      unfair_advantage TEXT NOT NULL DEFAULT '',
      metrics TEXT NOT NULL DEFAULT '',
      non_compete TEXT NOT NULL DEFAULT '',
      scaling_strategy TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── 财务期间表 ─────────────────────────────────────────────────

  financial_periods: `
    CREATE TABLE IF NOT EXISTS opc_financial_periods (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      period_type TEXT NOT NULL,
      start_date TEXT NOT NULL,
      end_date TEXT NOT NULL,
      revenue REAL NOT NULL DEFAULT 0,
      cost REAL NOT NULL DEFAULT 0,
      profit REAL NOT NULL DEFAULT 0,
      cash_flow REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      UNIQUE(company_id, period_type, start_date),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── 付款记录表 ─────────────────────────────────────────────────

  payments: `
    CREATE TABLE IF NOT EXISTS opc_payments (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      direction TEXT NOT NULL DEFAULT 'receivable',
      counterparty TEXT NOT NULL DEFAULT '',
      amount REAL NOT NULL DEFAULT 0,
      paid_amount REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'pending',
      due_date TEXT NOT NULL DEFAULT '',
      paid_date TEXT NOT NULL DEFAULT '',
      invoice_id TEXT NOT NULL DEFAULT '',
      contract_id TEXT NOT NULL DEFAULT '',
      category TEXT NOT NULL DEFAULT '',
      payment_method TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── 报价单表 ─────────────────────────────────────────────────

  quotations: `
    CREATE TABLE IF NOT EXISTS opc_quotations (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      contact_id TEXT NOT NULL DEFAULT '',
      quotation_number TEXT NOT NULL,
      title TEXT NOT NULL,
      total_amount REAL NOT NULL DEFAULT 0,
      valid_until TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'draft',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id),
      FOREIGN KEY (contact_id) REFERENCES opc_contacts(id)
    )
  `,

  quotation_items: `
    CREATE TABLE IF NOT EXISTS opc_quotation_items (
      id TEXT PRIMARY KEY,
      quotation_id TEXT NOT NULL,
      description TEXT NOT NULL,
      quantity REAL NOT NULL DEFAULT 1,
      unit_price REAL NOT NULL DEFAULT 0,
      total_price REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (quotation_id) REFERENCES opc_quotations(id)
    )
  `,

  contract_milestones: `
    CREATE TABLE IF NOT EXISTS opc_contract_milestones (
      id TEXT PRIMARY KEY,
      contract_id TEXT NOT NULL,
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      due_date TEXT NOT NULL DEFAULT '',
      amount REAL NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT 'pending',
      completed_date TEXT NOT NULL DEFAULT '',
      notes TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (contract_id) REFERENCES opc_contracts(id),
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,

  // ── 待办事项表 ─────────────────────────────────────────────────

  todos: `
    CREATE TABLE IF NOT EXISTS opc_todos (
      id TEXT PRIMARY KEY,
      company_id TEXT NOT NULL,
      title TEXT NOT NULL,
      description TEXT NOT NULL DEFAULT '',
      priority TEXT NOT NULL DEFAULT 'normal',
      status TEXT NOT NULL DEFAULT 'pending',
      due_date TEXT NOT NULL DEFAULT '',
      related_type TEXT NOT NULL DEFAULT '',
      related_id TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      completed_at TEXT NOT NULL DEFAULT '',
      FOREIGN KEY (company_id) REFERENCES opc_companies(id)
    )
  `,
} as const;

export const OPC_INDEXES = [
  "CREATE INDEX IF NOT EXISTS idx_employees_company ON opc_employees(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_transactions_company ON opc_transactions(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_transactions_date ON opc_transactions(transaction_date)",
  "CREATE INDEX IF NOT EXISTS idx_transactions_type ON opc_transactions(type)",
  "CREATE INDEX IF NOT EXISTS idx_contacts_company ON opc_contacts(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_companies_status ON opc_companies(status)",
  // Phase 2
  "CREATE INDEX IF NOT EXISTS idx_invoices_company ON opc_invoices(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_invoices_status ON opc_invoices(status)",
  "CREATE INDEX IF NOT EXISTS idx_tax_filings_company ON opc_tax_filings(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_tax_filings_period ON opc_tax_filings(period)",
  "CREATE INDEX IF NOT EXISTS idx_contracts_company ON opc_contracts(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_contracts_status ON opc_contracts(status)",
  "CREATE INDEX IF NOT EXISTS idx_hr_records_company ON opc_hr_records(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_media_content_company ON opc_media_content(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_media_content_status ON opc_media_content(status)",
  "CREATE INDEX IF NOT EXISTS idx_projects_company ON opc_projects(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_tasks_project ON opc_tasks(project_id)",
  "CREATE INDEX IF NOT EXISTS idx_tasks_company ON opc_tasks(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_tasks_status ON opc_tasks(status)",
  // Phase 3
  "CREATE INDEX IF NOT EXISTS idx_investment_rounds_company ON opc_investment_rounds(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_investment_rounds_status ON opc_investment_rounds(status)",
  "CREATE INDEX IF NOT EXISTS idx_investors_round ON opc_investors(round_id)",
  "CREATE INDEX IF NOT EXISTS idx_investors_company ON opc_investors(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_services_company ON opc_services(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_services_status ON opc_services(status)",
  "CREATE INDEX IF NOT EXISTS idx_procurement_orders_company ON opc_procurement_orders(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_procurement_orders_status ON opc_procurement_orders(status)",
  "CREATE INDEX IF NOT EXISTS idx_milestones_company ON opc_milestones(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_milestones_status ON opc_milestones(status)",
  "CREATE INDEX IF NOT EXISTS idx_lifecycle_events_company ON opc_lifecycle_events(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_lifecycle_events_date ON opc_lifecycle_events(event_date)",
  "CREATE INDEX IF NOT EXISTS idx_metrics_company ON opc_metrics(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_alerts_company ON opc_alerts(company_id)",
  // 资金闭环表索引
  "CREATE INDEX IF NOT EXISTS idx_acquisition_cases_company ON opc_acquisition_cases(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_acquisition_cases_status ON opc_acquisition_cases(status)",
  "CREATE INDEX IF NOT EXISTS idx_asset_packages_status ON opc_asset_packages(status)",
  "CREATE INDEX IF NOT EXISTS idx_asset_package_items_package ON opc_asset_package_items(package_id)",
  "CREATE INDEX IF NOT EXISTS idx_asset_package_items_company ON opc_asset_package_items(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_ct_transfers_package ON opc_ct_transfers(package_id)",
  "CREATE INDEX IF NOT EXISTS idx_ct_transfers_status ON opc_ct_transfers(status)",
  "CREATE INDEX IF NOT EXISTS idx_financing_fees_transfer ON opc_financing_fees(transfer_id)",
  "CREATE INDEX IF NOT EXISTS idx_financing_fees_status ON opc_financing_fees(status)",
  "CREATE INDEX IF NOT EXISTS idx_staff_config_company ON opc_staff_config(company_id)",
  // AI 员工任务
  "CREATE INDEX IF NOT EXISTS idx_staff_tasks_company ON opc_staff_tasks(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_staff_tasks_role ON opc_staff_tasks(staff_role)",
  "CREATE INDEX IF NOT EXISTS idx_staff_tasks_status ON opc_staff_tasks(status)",
  // session_key 索引由 migration v9 创建（旧库此时列尚不存在）
  // CRM（新表索引可在此创建，contacts 列索引由 migration v10 创建，旧库此时列尚不存在）
  "CREATE INDEX IF NOT EXISTS idx_interactions_contact ON opc_contact_interactions(contact_id)",
  // Documents
  "CREATE INDEX IF NOT EXISTS idx_documents_company ON opc_documents(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_documents_type ON opc_documents(doc_type)",
  // Invoice Items
  "CREATE INDEX IF NOT EXISTS idx_invoice_items_invoice ON opc_invoice_items(invoice_id)",
  // OPB Canvas
  "CREATE INDEX IF NOT EXISTS idx_opb_canvas_company ON opc_opb_canvas(company_id)",
  // 主动智能系统
  "CREATE INDEX IF NOT EXISTS idx_insights_company ON opc_insights(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_insights_status ON opc_insights(status)",
  "CREATE INDEX IF NOT EXISTS idx_insights_type ON opc_insights(insight_type)",
  "CREATE INDEX IF NOT EXISTS idx_insights_expires ON opc_insights(expires_at)",
  "CREATE INDEX IF NOT EXISTS idx_celebrations_company ON opc_celebrations(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_celebrations_shown ON opc_celebrations(shown)",
  "CREATE INDEX IF NOT EXISTS idx_briefings_company_date ON opc_briefings(company_id, briefing_date)",
  // 财务期间表索引
  "CREATE INDEX IF NOT EXISTS idx_financial_periods_company ON opc_financial_periods(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_financial_periods_dates ON opc_financial_periods(start_date, end_date)",
  // 付款记录表索引
  "CREATE INDEX IF NOT EXISTS idx_payments_company ON opc_payments(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_payments_direction ON opc_payments(direction)",
  "CREATE INDEX IF NOT EXISTS idx_payments_status ON opc_payments(status)",
  "CREATE INDEX IF NOT EXISTS idx_payments_due_date ON opc_payments(due_date)",
  "CREATE INDEX IF NOT EXISTS idx_payments_company_status ON opc_payments(company_id, status)",
  "CREATE INDEX IF NOT EXISTS idx_payments_company_direction ON opc_payments(company_id, direction)",
  // 报价单和里程碑索引
  "CREATE INDEX IF NOT EXISTS idx_quotations_company ON opc_quotations(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_quotations_status ON opc_quotations(status)",
  "CREATE INDEX IF NOT EXISTS idx_quotations_contact ON opc_quotations(contact_id)",
  "CREATE INDEX IF NOT EXISTS idx_quotation_items_quotation ON opc_quotation_items(quotation_id)",
  "CREATE INDEX IF NOT EXISTS idx_contract_milestones_contract ON opc_contract_milestones(contract_id)",
  "CREATE INDEX IF NOT EXISTS idx_contract_milestones_status ON opc_contract_milestones(status)",
  "CREATE INDEX IF NOT EXISTS idx_contract_milestones_due_date ON opc_contract_milestones(due_date)",
  "CREATE INDEX IF NOT EXISTS idx_contracts_quotation ON opc_contracts(quotation_id)",
  "CREATE INDEX IF NOT EXISTS idx_contracts_signed_date ON opc_contracts(signed_date)",
  "CREATE INDEX IF NOT EXISTS idx_payments_milestone ON opc_payments(milestone_id)",
  "CREATE INDEX IF NOT EXISTS idx_payments_risk_level ON opc_payments(risk_level)",
  "CREATE INDEX IF NOT EXISTS idx_payments_overdue ON opc_payments(overdue_days)",
  // 待办事项表索引
  "CREATE INDEX IF NOT EXISTS idx_todos_company ON opc_todos(company_id)",
  "CREATE INDEX IF NOT EXISTS idx_todos_status ON opc_todos(status)",
  "CREATE INDEX IF NOT EXISTS idx_todos_priority ON opc_todos(priority)",
  "CREATE INDEX IF NOT EXISTS idx_todos_due_date ON opc_todos(due_date)",
  "CREATE INDEX IF NOT EXISTS idx_todos_company_status ON opc_todos(company_id, status)",
  "CREATE INDEX IF NOT EXISTS idx_todos_company_priority ON opc_todos(company_id, priority)",
];