const fs = require('fs');
const path = require('path');
const { DatabaseSync } = require('node:sqlite');
const bcrypt = require('bcryptjs');
const { expandRuleSet } = require('./rule-catalog');

const DATA_DIR = process.env.DEPRECORE_DATA_DIR || path.join(process.cwd(), 'data');
const DB_PATH = process.env.DEPRECORE_DB_PATH || path.join(DATA_DIR, 'deprecore.sqlite');

fs.mkdirSync(DATA_DIR, { recursive: true });

const db = new DatabaseSync(DB_PATH);
db.exec('PRAGMA foreign_keys = ON;');
db.exec('PRAGMA journal_mode = WAL;');

function now() {
  return new Date().toISOString();
}

function json(value, fallback = null) {
  if (value === undefined) return fallback === null ? null : JSON.stringify(fallback);
  return JSON.stringify(value);
}

function parseJson(value, fallback = null) {
  if (value === null || value === undefined || value === '') return fallback;
  try {
    return JSON.parse(value);
  } catch {
    return fallback;
  }
}

function bind(statement, params) {
  if (Array.isArray(params)) return statement;
  return statement;
}

function args(params) {
  if (Array.isArray(params)) return params;
  if (params && Object.keys(params).length > 0) return [params];
  return [];
}

function one(sql, params = {}) {
  const statement = bind(db.prepare(sql), params);
  return statement.get(...args(params));
}

function all(sql, params = {}) {
  const statement = bind(db.prepare(sql), params);
  return statement.all(...args(params));
}

function run(sql, params = {}) {
  const statement = bind(db.prepare(sql), params);
  return statement.run(...args(params));
}

function tx(fn) {
  db.exec('BEGIN IMMEDIATE');
  try {
    const result = fn();
    db.exec('COMMIT');
    return result;
  } catch (error) {
    db.exec('ROLLBACK');
    throw error;
  }
}

function initialize() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS teams (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      description TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      team_id INTEGER REFERENCES teams(id),
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'viewer',
      status TEXT NOT NULL DEFAULT 'active',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS roles (
      key TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT,
      capabilities_json TEXT NOT NULL DEFAULT '[]',
      is_system INTEGER NOT NULL DEFAULT 0,
      locked INTEGER NOT NULL DEFAULT 0,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS user_preferences (
      user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
      preferences_json TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS user_companies (
      user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      entity_id INTEGER NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
      PRIMARY KEY (user_id, entity_id)
    );

    CREATE TABLE IF NOT EXISTS entities (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      legal_name TEXT,
      tax_id TEXT,
      federal_tax_id TEXT,
      state_tax_id TEXT,
      local_tax_id TEXT,
      fiscal_year_end_month INTEGER NOT NULL DEFAULT 12,
      base_currency TEXT NOT NULL DEFAULT 'USD',
      status TEXT NOT NULL DEFAULT 'active',
      disposed_at TEXT,
      disposed_reason TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS reference_values (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      type TEXT NOT NULL,
      name TEXT NOT NULL,
      code TEXT,
      metadata TEXT,
      UNIQUE(entity_id, type, name)
    );

    CREATE TABLE IF NOT EXISTS employees (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      workday_worker_id TEXT UNIQUE,
      employee_number TEXT,
      name TEXT NOT NULL,
      email TEXT,
      department TEXT,
      location TEXT,
      manager_name TEXT,
      status TEXT NOT NULL DEFAULT 'active',
      source TEXT NOT NULL DEFAULT 'manual',
      source_payload TEXT,
      last_synced_at TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS assets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id TEXT NOT NULL UNIQUE,
      entity_id INTEGER REFERENCES entities(id),
      template_id INTEGER,
      description TEXT NOT NULL,
      category TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'in_service',
      acquisition_cost REAL NOT NULL DEFAULT 0,
      acquired_date TEXT,
      in_service_date TEXT,
      useful_life_months INTEGER NOT NULL DEFAULT 60,
      salvage_value REAL NOT NULL DEFAULT 0,
      land_value REAL NOT NULL DEFAULT 0,
      prior_depreciation REAL NOT NULL DEFAULT 0,
      location TEXT,
      department TEXT,
      group_name TEXT,
      custodian TEXT,
      vendor TEXT,
      serial_number TEXT,
      invoice_number TEXT,
      gl_asset_account TEXT,
      gl_expense_account TEXT,
      gl_accumulated_account TEXT,
      barcode_value TEXT,
      condition TEXT,
      new_or_used TEXT DEFAULT 'new',
      listed_property INTEGER NOT NULL DEFAULT 0,
      business_use_percent REAL NOT NULL DEFAULT 100,
      investment_use_percent REAL NOT NULL DEFAULT 0,
      disposal_date TEXT,
      disposal_price REAL,
      disposal_expense REAL,
      disposal_type TEXT,
      property_type TEXT,
      notes TEXT,
      custom_fields TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS asset_books (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      book_type TEXT NOT NULL,
      active INTEGER NOT NULL DEFAULT 1,
      cost REAL,
      depreciation_method TEXT NOT NULL DEFAULT 'straight_line',
      convention TEXT NOT NULL DEFAULT 'half_year',
      useful_life_months INTEGER,
      section_179_amount REAL NOT NULL DEFAULT 0,
      bonus_percent REAL NOT NULL DEFAULT 0,
      business_use_percent REAL,
      investment_use_percent REAL,
      manual_depreciation TEXT,
      UNIQUE(asset_id, book_type)
    );

    CREATE TABLE IF NOT EXISTS asset_templates (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      name TEXT NOT NULL,
      description TEXT,
      defaults TEXT NOT NULL,
      custom_fields TEXT NOT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, name)
    );

    CREATE TABLE IF NOT EXISTS tax_rule_sets (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      jurisdiction TEXT NOT NULL,
      name TEXT NOT NULL,
      effective_date TEXT NOT NULL,
      version TEXT NOT NULL,
      rules_json TEXT NOT NULL,
      active INTEGER NOT NULL DEFAULT 1,
      source_note TEXT,
      created_at TEXT NOT NULL,
      UNIQUE(jurisdiction, version)
    );

    CREATE TABLE IF NOT EXISTS leases (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER REFERENCES assets(id) ON DELETE CASCADE,
      lessor TEXT,
      contract_value REAL NOT NULL DEFAULT 0,
      start_date TEXT,
      end_date TEXT,
      discount_rate REAL NOT NULL DEFAULT 0,
      payment_frequency TEXT NOT NULL DEFAULT 'monthly',
      notes TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS warranties (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      provider TEXT,
      phone TEXT,
      start_date TEXT,
      expiration_date TEXT,
      warranty_limit TEXT,
      actual_usage TEXT,
      coverage_details TEXT,
      document_name TEXT,
      document_base64 TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS asset_files (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      file_name TEXT NOT NULL,
      mime_type TEXT NOT NULL,
      size INTEGER NOT NULL,
      content_base64 TEXT NOT NULL,
      uploaded_by INTEGER REFERENCES users(id),
      uploaded_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS tasks (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER REFERENCES assets(id) ON DELETE CASCADE,
      title TEXT NOT NULL,
      type TEXT NOT NULL DEFAULT 'maintenance',
      status TEXT NOT NULL DEFAULT 'open',
      due_date TEXT,
      assigned_to INTEGER REFERENCES users(id),
      notes TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS checkouts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      checked_out_to TEXT NOT NULL,
      destination TEXT,
      due_date TEXT,
      checked_out_at TEXT NOT NULL,
      checked_in_at TEXT,
      notes TEXT
    );

    CREATE TABLE IF NOT EXISTS asset_assignments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      employee_id INTEGER REFERENCES employees(id),
      department TEXT,
      location TEXT,
      status TEXT NOT NULL DEFAULT 'assigned',
      assigned_at TEXT NOT NULL,
      released_at TEXT,
      assigned_by INTEGER REFERENCES users(id),
      release_reason TEXT,
      notes TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS workday_connections (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      tenant TEXT,
      base_url TEXT,
      token_url TEXT,
      client_id TEXT,
      client_secret TEXT,
      workers_path TEXT NOT NULL DEFAULT '/workers',
      enabled INTEGER NOT NULL DEFAULT 0,
      sync_enabled INTEGER NOT NULL DEFAULT 0,
      sync_interval_hours INTEGER NOT NULL DEFAULT 24,
      last_sync_at TEXT,
      last_sync_status TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS saved_reports (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      name TEXT NOT NULL,
      report_type TEXT NOT NULL,
      options_json TEXT NOT NULL,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS dashboard_layouts (
      user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
      layout_json TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    -- §280F passenger-automobile annual depreciation caps (IRS Rev. Proc., indexed yearly).
    CREATE TABLE IF NOT EXISTS section_280f_limits (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      pis_year INTEGER NOT NULL UNIQUE,
      year1_no_bonus REAL NOT NULL DEFAULT 0,
      year1_bonus REAL NOT NULL DEFAULT 0,
      year2 REAL NOT NULL DEFAULT 0,
      year3 REAL NOT NULL DEFAULT 0,
      year4plus REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    -- Short tax years (less than 12 months) prorate that year's MACRS depreciation, per company.
    CREATE TABLE IF NOT EXISTS short_tax_years (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      tax_year INTEGER NOT NULL,
      months REAL NOT NULL DEFAULT 12,
      note TEXT,
      updated_by INTEGER REFERENCES users(id),
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, tax_year)
    );

    -- §179 taxable-income limitation: per company+book+year entered income and computed carryover.
    CREATE TABLE IF NOT EXISTS section_179_carryover (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      book_code TEXT NOT NULL DEFAULT 'FEDERAL',
      tax_year INTEGER NOT NULL,
      taxable_income REAL,
      carryover_to_next REAL NOT NULL DEFAULT 0,
      updated_by INTEGER REFERENCES users(id),
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, book_code, tax_year)
    );

    CREATE TABLE IF NOT EXISTS app_settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS asset_notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      body TEXT NOT NULL,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS disposals (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      disposal_date TEXT NOT NULL,
      method TEXT NOT NULL DEFAULT 'sale',
      property_type TEXT,
      partial INTEGER NOT NULL DEFAULT 0,
      disposed_cost REAL NOT NULL DEFAULT 0,
      disposal_price REAL NOT NULL DEFAULT 0,
      disposal_expense REAL NOT NULL DEFAULT 0,
      accumulated_depreciation REAL NOT NULL DEFAULT 0,
      net_book_value REAL NOT NULL DEFAULT 0,
      gain_loss REAL NOT NULL DEFAULT 0,
      book_type TEXT NOT NULL DEFAULT 'GAAP',
      notes TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS asset_adjustments (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      adjustment_date TEXT NOT NULL,
      type TEXT NOT NULL DEFAULT 'impairment',
      amount REAL NOT NULL DEFAULT 0,
      book_type TEXT NOT NULL DEFAULT 'GAAP',
      reason TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS books (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      code TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      book_type TEXT NOT NULL DEFAULT 'tax',
      enabled INTEGER NOT NULL DEFAULT 1,
      is_primary INTEGER NOT NULL DEFAULT 0,
      tax_rule_set_id INTEGER REFERENCES tax_rule_sets(id),
      default_method TEXT,
      default_convention TEXT,
      fiscal_year_end_month INTEGER NOT NULL DEFAULT 12,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, code)
    );

    CREATE TABLE IF NOT EXISTS gl_entries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      book_code TEXT NOT NULL,
      entry_date TEXT NOT NULL,
      fiscal_year INTEGER,
      account TEXT NOT NULL,
      account_type TEXT,
      description TEXT,
      reference TEXT,
      debit REAL NOT NULL DEFAULT 0,
      credit REAL NOT NULL DEFAULT 0,
      asset_id INTEGER REFERENCES assets(id) ON DELETE SET NULL,
      external_id TEXT,
      source TEXT NOT NULL DEFAULT 'manual',
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_gl_entries_scope ON gl_entries (entity_id, book_code, fiscal_year);
    CREATE INDEX IF NOT EXISTS idx_gl_entries_extid ON gl_entries (entity_id, book_code, external_id);

    CREATE TABLE IF NOT EXISTS gl_accounts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      code TEXT NOT NULL,
      name TEXT NOT NULL,
      type TEXT NOT NULL DEFAULT 'asset',
      normal_balance TEXT NOT NULL DEFAULT 'debit',
      description TEXT,
      active INTEGER NOT NULL DEFAULT 1,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, code)
    );

    CREATE TABLE IF NOT EXISTS gl_account_defaults (
      entity_id INTEGER PRIMARY KEY REFERENCES entities(id) ON DELETE CASCADE,
      asset_account TEXT,
      accumulated_account TEXT,
      expense_account TEXT,
      pm_expense_account TEXT,
      pm_clearing_account TEXT,
      cwip_account TEXT,
      gain_account TEXT,
      loss_account TEXT,
      proceeds_account TEXT,
      retained_earnings_account TEXT,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS close_periods (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      book_code TEXT NOT NULL,
      period_type TEXT NOT NULL DEFAULT 'month',
      fiscal_year INTEGER NOT NULL,
      period_month INTEGER,
      status TEXT NOT NULL DEFAULT 'open',
      steps_json TEXT,
      reconciliation_json TEXT,
      totals_json TEXT,
      notes TEXT,
      started_by INTEGER REFERENCES users(id),
      started_at TEXT,
      closed_by INTEGER REFERENCES users(id),
      closed_at TEXT,
      reopened_by INTEGER REFERENCES users(id),
      reopened_at TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, book_code, period_type, fiscal_year, period_month)
    );
    CREATE INDEX IF NOT EXISTS idx_close_periods_scope ON close_periods (entity_id, book_code, status);

    CREATE TABLE IF NOT EXISTS alerts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      type TEXT NOT NULL,
      reference_type TEXT NOT NULL,
      reference_id INTEGER,
      asset_id INTEGER REFERENCES assets(id) ON DELETE CASCADE,
      severity TEXT NOT NULL DEFAULT 'warning',
      title TEXT NOT NULL,
      message TEXT,
      due_date TEXT,
      status TEXT NOT NULL DEFAULT 'open',
      notified_at TEXT,
      acknowledged_by INTEGER REFERENCES users(id),
      acknowledged_at TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(reference_type, reference_id, type)
    );

    CREATE TABLE IF NOT EXISTS pm_plans (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT,
      category TEXT,
      frequency_value INTEGER NOT NULL DEFAULT 1,
      frequency_unit TEXT NOT NULL DEFAULT 'months',
      estimated_minutes INTEGER,
      instructions TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS pm_plan_steps (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      plan_id INTEGER NOT NULL REFERENCES pm_plans(id) ON DELETE CASCADE,
      step_order INTEGER NOT NULL DEFAULT 0,
      title TEXT NOT NULL,
      details TEXT,
      est_minutes INTEGER
    );

    CREATE TABLE IF NOT EXISTS pm_plan_components (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      plan_id INTEGER NOT NULL REFERENCES pm_plans(id) ON DELETE CASCADE,
      sort_order INTEGER NOT NULL DEFAULT 0,
      part_number TEXT,
      description TEXT,
      supplier TEXT,
      cost REAL NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS asset_pm_plans (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      plan_id INTEGER REFERENCES pm_plans(id),
      frequency_value INTEGER NOT NULL DEFAULT 1,
      frequency_unit TEXT NOT NULL DEFAULT 'months',
      start_date TEXT,
      end_date TEXT,
      next_due_date TEXT,
      last_completed_date TEXT,
      assigned_to INTEGER REFERENCES users(id),
      active INTEGER NOT NULL DEFAULT 1,
      notes TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS pm_completions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_pm_id INTEGER NOT NULL REFERENCES asset_pm_plans(id) ON DELETE CASCADE,
      completed_at TEXT NOT NULL,
      completed_by INTEGER REFERENCES users(id),
      notes TEXT,
      notes_json TEXT,
      steps_json TEXT,
      cost REAL NOT NULL DEFAULT 0,
      components_json TEXT,
      signature TEXT,
      rating_safety INTEGER,
      rating_physical INTEGER,
      rating_operating INTEGER,
      next_due_date TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS pm_completion_photos (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      completion_id INTEGER NOT NULL REFERENCES pm_completions(id) ON DELETE CASCADE,
      name TEXT,
      mime_type TEXT,
      data_base64 TEXT NOT NULL,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS pm_plan_meters (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      plan_id INTEGER NOT NULL REFERENCES pm_plans(id) ON DELETE CASCADE,
      sort_order INTEGER NOT NULL DEFAULT 0,
      label TEXT NOT NULL,
      unit TEXT,
      usage_interval REAL NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS asset_pm_meters (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_pm_id INTEGER NOT NULL REFERENCES asset_pm_plans(id) ON DELETE CASCADE,
      sort_order INTEGER NOT NULL DEFAULT 0,
      label TEXT NOT NULL,
      unit TEXT,
      usage_interval REAL NOT NULL DEFAULT 0,
      baseline_reading REAL NOT NULL DEFAULT 0,
      due_at_reading REAL,
      last_reading REAL,
      last_reading_date TEXT
    );

    CREATE TABLE IF NOT EXISTS pm_meter_readings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_pm_meter_id INTEGER NOT NULL REFERENCES asset_pm_meters(id) ON DELETE CASCADE,
      reading REAL NOT NULL,
      reading_date TEXT NOT NULL,
      source TEXT NOT NULL DEFAULT 'manual',
      completion_id INTEGER REFERENCES pm_completions(id) ON DELETE SET NULL,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS contacts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      type TEXT NOT NULL DEFAULT 'other',
      first_name TEXT,
      last_name TEXT,
      organization TEXT,
      email TEXT,
      phone TEXT,
      address_line1 TEXT,
      address_line2 TEXT,
      city TEXT,
      state_region TEXT,
      postal_code TEXT,
      country TEXT,
      notes TEXT,
      status TEXT NOT NULL DEFAULT 'active',
      employee_id TEXT,
      tax_id TEXT,
      department TEXT,
      work_location TEXT,
      manager_first_name TEXT,
      manager_last_name TEXT,
      manager_email TEXT,
      start_date TEXT,
      rating INTEGER,
      credit_term TEXT,
      source TEXT NOT NULL DEFAULT 'manual',
      workday_worker_id TEXT UNIQUE,
      source_payload TEXT,
      last_synced_at TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS contact_notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
      body TEXT NOT NULL,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS parts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      part_number TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      category TEXT,
      manufacturer TEXT,
      manufacturer_part_number TEXT,
      barcode TEXT,
      unit_of_measure TEXT NOT NULL DEFAULT 'each',
      unit_cost REAL NOT NULL DEFAULT 0,
      reorder_point REAL NOT NULL DEFAULT 0,
      reorder_quantity REAL NOT NULL DEFAULT 0,
      measurements TEXT,
      supplier_contact_id INTEGER REFERENCES contacts(id),
      status TEXT NOT NULL DEFAULT 'active',
      notes TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, part_number)
    );

    CREATE TABLE IF NOT EXISTS part_stock (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      part_id INTEGER NOT NULL REFERENCES parts(id) ON DELETE CASCADE,
      location_contact_id INTEGER REFERENCES contacts(id),
      aisle TEXT,
      bin TEXT,
      quantity REAL NOT NULL DEFAULT 0,
      reserved REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS part_photos (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      part_id INTEGER NOT NULL REFERENCES parts(id) ON DELETE CASCADE,
      name TEXT,
      mime_type TEXT,
      data_base64 TEXT NOT NULL,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS part_transactions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      part_id INTEGER NOT NULL REFERENCES parts(id) ON DELETE CASCADE,
      stock_id INTEGER REFERENCES part_stock(id) ON DELETE SET NULL,
      type TEXT NOT NULL DEFAULT 'receive',
      quantity REAL NOT NULL DEFAULT 0,
      unit_cost REAL,
      asset_id INTEGER REFERENCES assets(id) ON DELETE SET NULL,
      reference TEXT,
      notes TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS asset_photos (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      asset_id INTEGER NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
      name TEXT,
      mime_type TEXT,
      caption TEXT,
      data_base64 TEXT NOT NULL,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS pm_plan_photos (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      plan_id INTEGER NOT NULL REFERENCES pm_plans(id) ON DELETE CASCADE,
      name TEXT,
      mime_type TEXT,
      caption TEXT,
      data_base64 TEXT NOT NULL,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS asset_id_templates (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      pattern TEXT NOT NULL,
      next_number INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS asset_classes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      class_number TEXT,
      description TEXT NOT NULL,
      gds REAL,
      ads REAL,
      source TEXT NOT NULL DEFAULT 'custom',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS depreciation_zones (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      code TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL,
      allowance_percent REAL NOT NULL DEFAULT 0,
      pis_start TEXT,
      pis_end TEXT,
      real_property_end TEXT,
      max_recovery_years INTEGER,
      leasehold_life_years INTEGER,
      notes TEXT,
      enabled INTEGER NOT NULL DEFAULT 1,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS section_179_limits (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      tax_year INTEGER NOT NULL UNIQUE,
      base_limit REAL NOT NULL DEFAULT 0,
      phaseout_threshold REAL NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS audit_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      actor_id INTEGER REFERENCES users(id),
      action TEXT NOT NULL,
      entity_type TEXT NOT NULL,
      entity_id TEXT,
      before_json TEXT,
      after_json TEXT,
      created_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS password_history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      password_hash TEXT NOT NULL,
      created_at TEXT NOT NULL
    );
  `);

  migrate();
  seed();
}

// Additive column migrations for databases created before these columns existed.
function ensureColumn(table, column, definition) {
  const columns = all(`PRAGMA table_info(${table})`);
  if (!columns.some((col) => col.name === column)) {
    db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
  }
}

function migrate() {
  ensureColumn('pm_plan_steps', 'est_minutes', 'INTEGER');
  ensureColumn('pm_completions', 'cost', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('pm_completions', 'components_json', 'TEXT');
  ensureColumn('pm_completions', 'notes_json', 'TEXT');
  ensureColumn('pm_completions', 'signature', 'TEXT');
  ensureColumn('pm_completions', 'rating_safety', 'INTEGER');
  ensureColumn('pm_completions', 'rating_physical', 'INTEGER');
  ensureColumn('pm_completions', 'rating_operating', 'INTEGER');
  ensureColumn('workday_connections', 'sync_enabled', 'INTEGER NOT NULL DEFAULT 0');
  ensureColumn('workday_connections', 'sync_interval_hours', 'INTEGER NOT NULL DEFAULT 24');
  ensureColumn('alerts', 'external_ticket_id', 'TEXT');
  ensureColumn('alerts', 'external_ticket_number', 'TEXT');
  ensureColumn('alerts', 'external_ticket_url', 'TEXT');
  ensureColumn('alerts', 'external_ticket_system', 'TEXT');
  ensureColumn('assets', 'servicenow_sys_id', 'TEXT');
  ensureColumn('assets', 'asset_class_number', 'TEXT');
  ensureColumn('assets', 'special_zone', 'TEXT');
  ensureColumn('depreciation_zones', 'section_179_increase', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('depreciation_zones', 'section_179_cost_factor', 'REAL NOT NULL DEFAULT 1');
  ensureColumn('depreciation_zones', 'section_179_threshold_increase', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('depreciation_zones', 'section_179_pis_start', 'TEXT');
  ensureColumn('depreciation_zones', 'section_179_pis_end', 'TEXT');
  // Dynamic PM scheduling: usage-meter & lifespan-curve signals layered on the calendar interval.
  ensureColumn('pm_plans', 'lifespan_adjust', 'INTEGER NOT NULL DEFAULT 0');
  ensureColumn('asset_pm_plans', 'lifespan_adjust', 'INTEGER NOT NULL DEFAULT 0');
  ensureColumn('asset_pm_plans', 'schedule_mode', "TEXT NOT NULL DEFAULT 'earliest'");
  // Password access control & account-lockout columns.
  ensureColumn('users', 'password_changed_at', 'TEXT');
  ensureColumn('users', 'failed_attempts', 'INTEGER NOT NULL DEFAULT 0');
  ensureColumn('users', 'locked_until', 'TEXT');
  ensureColumn('users', 'last_login_at', 'TEXT');
  ensureColumn('users', 'must_change_password', 'INTEGER NOT NULL DEFAULT 0');
  dropUsersRoleCheck();
  // Multi-company scoping: company lifecycle + per-company foreign keys.
  ensureColumn('entities', 'status', "TEXT NOT NULL DEFAULT 'active'");
  ensureColumn('entities', 'disposed_at', 'TEXT');
  ensureColumn('entities', 'disposed_reason', 'TEXT');
  // Separate federal / state / optional local tax ids; the legacy single tax_id becomes the federal one.
  ensureColumn('entities', 'federal_tax_id', 'TEXT');
  ensureColumn('entities', 'state_tax_id', 'TEXT');
  ensureColumn('entities', 'local_tax_id', 'TEXT');
  run('UPDATE entities SET federal_tax_id = tax_id WHERE federal_tax_id IS NULL AND tax_id IS NOT NULL');
  ensureColumn('pm_plans', 'entity_id', 'INTEGER REFERENCES entities(id)');
  ensureColumn('alerts', 'entity_id', 'INTEGER REFERENCES entities(id)');
  rebuildBooksPerCompany();
  // Phase 2: scope reference data, contacts, templates, saved reports per company.
  ensureColumn('contacts', 'entity_id', 'INTEGER REFERENCES entities(id)');
  ensureColumn('saved_reports', 'entity_id', 'INTEGER REFERENCES entities(id)');
  ensureColumn('employees', 'entity_id', 'INTEGER REFERENCES entities(id)');
  rebuildReferenceValuesPerCompany();
  rebuildAssetTemplatesPerCompany();
  rebuildPartsPerCompany();
  // Period close: extra GL posting accounts + per-company capitalization threshold.
  ensureColumn('gl_account_defaults', 'cwip_account', 'TEXT');
  ensureColumn('gl_account_defaults', 'gain_account', 'TEXT');
  ensureColumn('gl_account_defaults', 'loss_account', 'TEXT');
  ensureColumn('gl_account_defaults', 'proceeds_account', 'TEXT');
  ensureColumn('gl_account_defaults', 'retained_earnings_account', 'TEXT');
  ensureColumn('entities', 'capitalization_threshold', 'REAL NOT NULL DEFAULT 0');
  // §280F: flag passenger automobiles subject to the annual luxury-auto depreciation caps.
  ensureColumn('assets', 'passenger_auto', 'INTEGER NOT NULL DEFAULT 0');
  // Depreciation-recapture characterization stored on disposals (Form 4797).
  ensureColumn('disposals', 'recapture_section', 'TEXT');
  ensureColumn('disposals', 'ordinary_recapture', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('disposals', 'section_1231_gain', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('disposals', 'unrecaptured_1250_gain', 'REAL NOT NULL DEFAULT 0');
  // Farm-property recapture inputs (IRS Pub 225): §175 soil/water conservation deductions (§1252) and
  // §126 excluded cost-sharing payments (§1255), with the computed recapture stored on disposals.
  ensureColumn('assets', 'soil_water_deductions', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('assets', 'cost_sharing_excluded', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('disposals', 'section_1252_recapture', 'REAL NOT NULL DEFAULT 0');
  ensureColumn('disposals', 'section_1255_recapture', 'REAL NOT NULL DEFAULT 0');
  // Refresh stale §179 limits seeded before the 2025 OBBBA increase (only touch the old seed values,
  // never a figure an administrator has deliberately raised). 2026 per IRS Pub 946 (2025 ed.).
  run('UPDATE section_179_limits SET base_limit = 2500000, phaseout_threshold = 4000000 WHERE tax_year = 2025 AND base_limit < 2000000');
  run('UPDATE section_179_limits SET base_limit = 2560000, phaseout_threshold = 4090000 WHERE tax_year = 2026 AND base_limit < 2000000');
  backfillCompanyScope();
}

// parts became per-company: UNIQUE(part_number) → UNIQUE(entity_id, part_number).
function rebuildPartsPerCompany() {
  const ddl = one("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'parts'");
  if (!ddl || /entity_id/i.test(ddl.sql)) return; // already scoped
  db.exec('PRAGMA foreign_keys=OFF');
  db.exec(`
    CREATE TABLE parts_rebuild (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      part_number TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      category TEXT,
      manufacturer TEXT,
      manufacturer_part_number TEXT,
      barcode TEXT,
      unit_of_measure TEXT NOT NULL DEFAULT 'each',
      unit_cost REAL NOT NULL DEFAULT 0,
      reorder_point REAL NOT NULL DEFAULT 0,
      reorder_quantity REAL NOT NULL DEFAULT 0,
      measurements TEXT,
      supplier_contact_id INTEGER REFERENCES contacts(id),
      status TEXT NOT NULL DEFAULT 'active',
      notes TEXT,
      created_by INTEGER REFERENCES users(id),
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, part_number)
    );
    INSERT INTO parts_rebuild (id, part_number, name, description, category, manufacturer, manufacturer_part_number, barcode, unit_of_measure, unit_cost, reorder_point, reorder_quantity, measurements, supplier_contact_id, status, notes, created_by, created_at, updated_at)
      SELECT id, part_number, name, description, category, manufacturer, manufacturer_part_number, barcode, unit_of_measure, unit_cost, reorder_point, reorder_quantity, measurements, supplier_contact_id, status, notes, created_by, created_at, updated_at FROM parts;
    DROP TABLE parts;
    ALTER TABLE parts_rebuild RENAME TO parts;
  `);
  db.exec('PRAGMA foreign_keys=ON');
}

// reference_values became per-company: UNIQUE(type, name) → UNIQUE(entity_id, type, name).
function rebuildReferenceValuesPerCompany() {
  const ddl = one("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'reference_values'");
  if (!ddl || /entity_id/i.test(ddl.sql)) return; // already scoped
  db.exec('PRAGMA foreign_keys=OFF');
  db.exec(`
    CREATE TABLE reference_values_rebuild (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      type TEXT NOT NULL,
      name TEXT NOT NULL,
      code TEXT,
      metadata TEXT,
      UNIQUE(entity_id, type, name)
    );
    INSERT INTO reference_values_rebuild (id, type, name, code, metadata)
      SELECT id, type, name, code, metadata FROM reference_values;
    DROP TABLE reference_values;
    ALTER TABLE reference_values_rebuild RENAME TO reference_values;
  `);
  db.exec('PRAGMA foreign_keys=ON');
}

// asset_templates became per-company: UNIQUE(name) → UNIQUE(entity_id, name).
function rebuildAssetTemplatesPerCompany() {
  const ddl = one("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'asset_templates'");
  if (!ddl || /entity_id/i.test(ddl.sql)) return; // already scoped
  db.exec('PRAGMA foreign_keys=OFF');
  db.exec(`
    CREATE TABLE asset_templates_rebuild (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      name TEXT NOT NULL,
      description TEXT,
      defaults TEXT NOT NULL,
      custom_fields TEXT NOT NULL,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, name)
    );
    INSERT INTO asset_templates_rebuild (id, name, description, defaults, custom_fields, created_at, updated_at)
      SELECT id, name, description, defaults, custom_fields, created_at, updated_at FROM asset_templates;
    DROP TABLE asset_templates;
    ALTER TABLE asset_templates_rebuild RENAME TO asset_templates;
  `);
  db.exec('PRAGMA foreign_keys=ON');
}

// Books became per-company: the old global UNIQUE(code) must become UNIQUE(entity_id, code).
// SQLite can't alter a constraint in place, so rebuild when the legacy column-level UNIQUE is present.
function rebuildBooksPerCompany() {
  const ddl = one("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'books'");
  if (!ddl || !/code\s+TEXT\s+NOT\s+NULL\s+UNIQUE/i.test(ddl.sql)) return; // already composite
  db.exec('PRAGMA foreign_keys=OFF');
  db.exec(`
    CREATE TABLE books_rebuild (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      entity_id INTEGER REFERENCES entities(id),
      code TEXT NOT NULL,
      name TEXT NOT NULL,
      description TEXT,
      book_type TEXT NOT NULL DEFAULT 'tax',
      enabled INTEGER NOT NULL DEFAULT 1,
      is_primary INTEGER NOT NULL DEFAULT 0,
      tax_rule_set_id INTEGER REFERENCES tax_rule_sets(id),
      default_method TEXT,
      default_convention TEXT,
      fiscal_year_end_month INTEGER NOT NULL DEFAULT 12,
      sort_order INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(entity_id, code)
    );
    INSERT INTO books_rebuild (id, code, name, description, book_type, enabled, is_primary, tax_rule_set_id, default_method, default_convention, fiscal_year_end_month, sort_order, created_at, updated_at)
      SELECT id, code, name, description, book_type, enabled, is_primary, tax_rule_set_id, default_method, default_convention, fiscal_year_end_month, sort_order, created_at, updated_at FROM books;
    DROP TABLE books;
    ALTER TABLE books_rebuild RENAME TO books;
  `);
  db.exec('PRAGMA foreign_keys=ON');
}

// Assign pre-multi-company rows to the default (first active) company. No-op on a fresh DB.
function backfillCompanyScope() {
  const def = one("SELECT id FROM entities WHERE status = 'active' ORDER BY id LIMIT 1") || one('SELECT id FROM entities ORDER BY id LIMIT 1');
  if (!def) return;
  // OR IGNORE so a pre-existing duplicate (e.g. two templates with the same name) can't break the
  // new per-company UNIQUE constraints — the duplicate is left unscoped rather than crashing init.
  for (const table of ['assets', 'books', 'pm_plans', 'alerts', 'reference_values', 'contacts', 'asset_templates', 'saved_reports', 'parts', 'employees']) {
    run(`UPDATE OR IGNORE ${table} SET entity_id = ? WHERE entity_id IS NULL`, [def.id]);
  }
  // Preserve "everyone sees all" on upgrade: grant each existing user every existing company,
  // but only when the mapping is empty (first run) so deliberate later restrictions aren't undone.
  if (!one('SELECT 1 AS x FROM user_companies LIMIT 1')) {
    run('INSERT OR IGNORE INTO user_companies (user_id, entity_id) SELECT u.id, e.id FROM users u CROSS JOIN entities e');
  }
  // Seed a starter chart of accounts + GL posting defaults for any existing company that lacks one.
  for (const company of all('SELECT id FROM entities')) seedGlAccountsForCompany(company.id);
}

// Custom roles require the legacy CHECK(role IN (...)) constraint on `users` to be removed.
// SQLite can't drop a column constraint in place, so rebuild the table when the old DDL is present.
function dropUsersRoleCheck() {
  const ddl = one("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'users'");
  if (!ddl || !/CHECK\s*\(\s*role/i.test(ddl.sql)) return;
  db.exec('PRAGMA foreign_keys=OFF');
  db.exec(`
    CREATE TABLE users_rebuild (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      team_id INTEGER REFERENCES teams(id),
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE,
      password_hash TEXT NOT NULL,
      role TEXT NOT NULL DEFAULT 'viewer',
      status TEXT NOT NULL DEFAULT 'active',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    INSERT INTO users_rebuild (id, team_id, name, email, password_hash, role, status, created_at, updated_at)
      SELECT id, team_id, name, email, password_hash, role, status, created_at, updated_at FROM users;
    DROP TABLE users;
    ALTER TABLE users_rebuild RENAME TO users;
  `);
  db.exec('PRAGMA foreign_keys=ON');
}

// Seed the standard book set for a company (idempotent per company). Called during initial seed and
// whenever a new company is created so it starts with GAAP/FEDERAL/STATE/AMT/ACE/USER + the PM book.
function seedBooksForCompany(entityId) {
  if (!entityId || one('SELECT id FROM books WHERE entity_id = ? LIMIT 1', [entityId])) return;
  const createdAt = now();
  const federalRule = one("SELECT id FROM tax_rule_sets WHERE active = 1 ORDER BY effective_date DESC, id DESC LIMIT 1");
  const ruleId = federalRule ? federalRule.id : null;
  const bookDefs = [
    ['GAAP', 'GAAP / Financial', 'Primary book for financial statements', 'financial', 1, 1, 'straight_line', 'half_year'],
    ['FEDERAL', 'Federal Tax', 'US federal income tax depreciation', 'tax', 1, 0, 'macrs_gds_200db_5', 'half_year'],
    ['STATE', 'State Tax', 'State income tax depreciation', 'tax', 1, 0, 'macrs_gds_200db_5', 'half_year'],
    ['AMT', 'Alternative Minimum Tax', 'AMT depreciation adjustments', 'tax', 1, 0, 'macrs_gds_150db_5', 'half_year'],
    ['ACE', 'Adjusted Current Earnings', 'ACE depreciation', 'tax', 1, 0, 'macrs_ads_sl_5', 'half_year'],
    ['USER', 'User-defined', 'Custom internal reporting book', 'internal', 0, 0, 'straight_line', 'half_year']
  ];
  bookDefs.forEach((def, index) => {
    run(
      `INSERT INTO books (entity_id, code, name, description, book_type, enabled, is_primary, tax_rule_set_id, default_method, default_convention, sort_order, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [entityId, def[0], def[1], def[2], def[3], def[4], def[5], ruleId, def[6], def[7], index, createdAt, createdAt]
    );
  });
  // The PM book tracks actual preventative-maintenance spend per asset (not depreciation).
  run(
    `INSERT OR IGNORE INTO books (entity_id, code, name, description, book_type, enabled, is_primary, sort_order, created_at, updated_at)
     VALUES (?, 'PM', 'PM / Maintenance', 'Actual preventative-maintenance spend per asset', 'maintenance', 1, 0, 6, ?, ?)`,
    [entityId, createdAt, createdAt]
  );
}

// Seed the standard reference data (categories, locations, departments, part categories) for a company.
// Idempotent per company; called during initial seed and when a new company is created.
function seedReferenceValuesForCompany(entityId) {
  if (!entityId) return;
  const refs = [
    ['location', 'Headquarters', 'HQ'],
    ['location', 'Warehouse', 'WH'],
    ['department', 'Accounting', 'ACCT'],
    ['department', 'Operations', 'OPS'],
    ['category', 'Computer Equipment', 'COMP'],
    ['category', 'Office Furniture', 'FURN'],
    ['category', 'Vehicles', 'AUTO'],
    ['category', 'Leasehold Improvements', 'LHI'],
    ['part_category', 'Electrical', null],
    ['part_category', 'Plumbing', null],
    ['part_category', 'Structural Component', null],
    ['part_category', 'Electronic Component', null],
    ['part_category', 'Fire Safety', null],
    ['part_category', 'Mechanical Safety', null],
    ['part_category', 'Other', null]
  ];
  for (const item of refs) {
    run('INSERT OR IGNORE INTO reference_values (entity_id, type, name, code, metadata) VALUES (?, ?, ?, ?, ?)', [entityId, item[0], item[1], item[2], '{}']);
  }
}

// Seed a starter chart of accounts and the GL posting defaults for a company (idempotent per company).
// The defaults are the fallback accounts the depreciation/PM ledgers post to when an asset doesn't
// carry its own. Called during initial seed and when a new company is created.
function seedGlAccountsForCompany(entityId) {
  if (!entityId) return;
  const ts = now();
  // Seed the starter chart only for a brand-new company, so deliberate later deletions aren't undone.
  if (!one('SELECT id FROM gl_accounts WHERE entity_id = ? LIMIT 1', [entityId])) {
    const accounts = [
      ['1010', 'Cash / Proceeds Clearing', 'asset', 'debit', 'Disposal proceeds clearing'],
      ['1500', 'Fixed Assets', 'asset', 'debit', 'Asset cost / basis'],
      ['1590', 'Accumulated Depreciation', 'contra_asset', 'credit', 'Accumulated depreciation (contra-asset)'],
      ['1800', 'Construction in Progress', 'asset', 'debit', 'Capital work-in-progress (CWIP)'],
      ['2000', 'Maintenance Clearing', 'liability', 'credit', 'PM/maintenance clearing'],
      ['3900', 'Retained Earnings', 'equity', 'credit', 'Year-end depreciation roll-forward'],
      ['6400', 'Depreciation Expense', 'expense', 'debit', 'Periodic depreciation expense'],
      ['6450', 'Maintenance Expense', 'expense', 'debit', 'Preventative-maintenance spend'],
      ['7200', 'Gain on Disposal', 'revenue', 'credit', 'Gain on asset disposal'],
      ['7300', 'Loss on Disposal', 'expense', 'debit', 'Loss on asset disposal']
    ];
    for (const [code, name, type, normal, description] of accounts) {
      run(
        `INSERT OR IGNORE INTO gl_accounts (entity_id, code, name, type, normal_balance, description, active, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, 1, ?, ?)`,
        [entityId, code, name, type, normal, description, ts, ts]
      );
    }
  }
  // Ensure the per-company posting defaults exist; backfill the close-account columns for companies
  // seeded before the close feature (the ledgers post to these account codes regardless of chart rows).
  run(
    `INSERT OR IGNORE INTO gl_account_defaults
       (entity_id, asset_account, accumulated_account, expense_account, pm_expense_account, pm_clearing_account,
        cwip_account, gain_account, loss_account, proceeds_account, retained_earnings_account, updated_at)
     VALUES (?, '1500', '1590', '6400', '6450', '2000', '1800', '7200', '7300', '1010', '3900', ?)`,
    [entityId, ts]
  );
  run(
    `UPDATE gl_account_defaults SET
       cwip_account = COALESCE(cwip_account, '1800'),
       gain_account = COALESCE(gain_account, '7200'),
       loss_account = COALESCE(loss_account, '7300'),
       proceeds_account = COALESCE(proceeds_account, '1010'),
       retained_earnings_account = COALESCE(retained_earnings_account, '3900')
     WHERE entity_id = ?`,
    [entityId]
  );
}

function seed() {
  const createdAt = now();

  // Built-in roles (capability sets). Insert missing ones; keep admin's wildcard in sync.
  const { DEFAULT_ROLES } = require('./services/accessControl');
  for (const role of DEFAULT_ROLES) {
    run(
      `INSERT INTO roles (key, name, description, capabilities_json, is_system, locked, sort_order, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(key) DO UPDATE SET is_system = excluded.is_system, locked = excluded.locked`,
      [role.key, role.name, role.description, json(role.capabilities), role.is_system, role.locked, role.sort_order, createdAt, createdAt]
    );
  }
  run("UPDATE roles SET capabilities_json = ? WHERE key = 'admin'", [json(['*'])]);

  // Asset class catalog: seed from the IRS reference tables once; afterwards it is
  // fully user-managed (edits/deletes persist and are not re-seeded).
  if (!one('SELECT id FROM asset_classes LIMIT 1')) {
    const { IRS_ASSET_CLASSES } = require('./data/irsAssetClasses');
    for (const item of IRS_ASSET_CLASSES) {
      run(
        'INSERT INTO asset_classes (class_number, description, gds, ads, source, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [item.class_number || null, item.description, item.gds ?? null, item.ads ?? null, item.table || 'common', createdAt, createdAt]
      );
    }
  }

  // Special depreciation zones (e.g. New York Liberty Zone). Seeded once, then user-managed.
  run(
    `INSERT OR IGNORE INTO depreciation_zones
       (code, name, allowance_percent, pis_start, pis_end, real_property_end, max_recovery_years, leasehold_life_years, notes, enabled, created_at, updated_at)
     VALUES ('ny_liberty', 'New York Liberty Zone', 30, '2001-09-11', '2006-12-31', '2009-12-31', 20, 5,
       '30% special depreciation allowance for qualified NY Liberty Zone property; qualified leasehold improvements use a 5-year life; increased Section 179. Verify current tax law before filing.', 1, ?, ?)`,
    [createdAt, createdAt]
  );

  // Enterprise / Empowerment Zone: raises the Section 179 limit by $35,000 (2002-2020) and counts
  // only 50% of the property's cost toward the §179 investment-phaseout threshold.
  run(
    `INSERT OR IGNORE INTO depreciation_zones
       (code, name, allowance_percent, pis_start, pis_end, real_property_end, max_recovery_years, leasehold_life_years,
        section_179_increase, section_179_cost_factor, notes, enabled, created_at, updated_at)
     VALUES ('enterprise_zone', 'Enterprise Zone (Empowerment Zone)', 0, '2002-01-01', '2020-12-31', NULL, NULL, NULL,
       35000, 0.5,
       'Section 179 limit increase for qualified empowerment/enterprise zone property: +$35,000 (2002-2020; +$20,000 for 1993-2001) and only 50% of cost counts toward the investment-phaseout threshold. Not available after 2020. Verify current tax law before filing.', 1, ?, ?)`,
    [createdAt, createdAt]
  );

  // Qualified Gulf Opportunity (GO) Zone property: 50% §168 special allowance (placed in service
  // 2005-08-28 through 2011-03-31, real property through 2010-12-31) AND an increased §179 limit of
  // +$100,000 (2005-08-28 through 2008-12-31), with the investment-phaseout threshold raised by the
  // lesser of $600,000 or the property's cost.
  run(
    `INSERT OR IGNORE INTO depreciation_zones
       (code, name, allowance_percent, pis_start, pis_end, real_property_end, max_recovery_years, leasehold_life_years,
        section_179_increase, section_179_cost_factor, section_179_threshold_increase, section_179_pis_start, section_179_pis_end,
        notes, enabled, created_at, updated_at)
     VALUES ('go_zone', 'Qualified Gulf Opportunity (GO) Zone', 50, '2005-08-28', '2011-03-31', '2010-12-31', 20, 9,
       100000, 1, 600000, '2005-08-28', '2008-12-31',
       '50% special depreciation allowance for qualified GO Zone property (placed in service 2005-08-28 to 2011-03-31; real property to 2010-12-31). Increased §179 limit of +$100,000 (2005-08-28 to 2008-12-31), with the phaseout threshold raised by the lesser of $600,000 or the property''s cost. Qualified leasehold improvements use a 9-year life. Verify current tax law before filing.', 1, ?, ?)`,
    [createdAt, createdAt]
  );

  // Qualified Recovery Assistance Property (Kansas Disaster Zone): 50% §168 special allowance (placed
  // in service after 2007-05-04 through 2008-12-31; real property through 2009-12-31) AND an increased
  // §179 limit of +$100,000 (2007-2008), with the phaseout threshold raised by the lesser of $600,000
  // or the property's cost. The §168 and §179 windows coincide, so no separate §179 window is needed.
  run(
    `INSERT OR IGNORE INTO depreciation_zones
       (code, name, allowance_percent, pis_start, pis_end, real_property_end, max_recovery_years, leasehold_life_years,
        section_179_increase, section_179_cost_factor, section_179_threshold_increase, section_179_pis_start, section_179_pis_end,
        notes, enabled, created_at, updated_at)
     VALUES ('kansas_disaster', 'Qualified Recovery Assistance (Kansas Disaster Zone)', 50, '2007-05-05', '2008-12-31', '2009-12-31', 20, NULL,
       100000, 1, 600000, NULL, NULL,
       '50% special depreciation allowance for qualified recovery assistance (Kansas Disaster Zone) property (placed in service 2007-05-05 to 2008-12-31; real property to 2009-12-31). Increased §179 limit of +$100,000 (2007-2008), with the phaseout threshold raised by the lesser of $600,000 or the property''s cost. Verify current tax law before filing.', 1, ?, ?)`,
    [createdAt, createdAt]
  );

  // Annual Section 179 deduction limits & investment-phaseout thresholds (IRS historical values).
  // Seeded once, then user-managed in Admin → Application Configuration → Section 179 limits.
  const section179Limits = [
    [2003, 100000, 400000], [2004, 102000, 410000], [2005, 105000, 420000], [2006, 108000, 430000],
    [2007, 125000, 500000], [2008, 250000, 800000], [2009, 250000, 800000], [2010, 500000, 2000000],
    [2011, 500000, 2000000], [2012, 500000, 2000000], [2013, 500000, 2000000],
    [2014, 500000, 2000000], [2015, 500000, 2000000], [2016, 500000, 2010000], [2017, 510000, 2030000],
    [2018, 1000000, 2500000], [2019, 1020000, 2550000], [2020, 1040000, 2590000], [2021, 1050000, 2620000],
    [2022, 1080000, 2700000], [2023, 1160000, 2890000], [2024, 1220000, 3050000],
    // 2025+ reflect the 2025 OBBBA increase; 2026 per IRS Pub 946 (2025 ed.), "What's New for 2026".
    [2025, 2500000, 4000000], [2026, 2560000, 4090000]
  ];
  for (const [year, limit, threshold] of section179Limits) {
    run(
      `INSERT OR IGNORE INTO section_179_limits (tax_year, base_limit, phaseout_threshold, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?)`,
      [year, limit, threshold, createdAt, createdAt]
    );
  }
  // §280F passenger-automobile depreciation caps [year, y1-no-bonus, y1-bonus, y2, y3, y4+]. Published
  // IRS figures (Rev. Proc. 2023-14 / 2024-13); out-years are estimates — these are editable data.
  const section280fLimits = [
    [2022, 11200, 19200, 18000, 10800, 6460],
    [2023, 12200, 20200, 19500, 11700, 6960],
    [2024, 12400, 20400, 19800, 11900, 7160],
    [2025, 12600, 20600, 20200, 12100, 7260],
    [2026, 12800, 20800, 20600, 12300, 7360]
  ];
  for (const [year, y1nb, y1b, y2, y3, y4] of section280fLimits) {
    run(
      `INSERT OR IGNORE INTO section_280f_limits (pis_year, year1_no_bonus, year1_bonus, year2, year3, year4plus, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [year, y1nb, y1b, y2, y3, y4, createdAt, createdAt]
    );
  }
  if (!one('SELECT id FROM teams LIMIT 1')) {
    run('INSERT INTO teams (name, description, created_at) VALUES (?, ?, ?)', [
      'Finance Operations',
      'Default DepreCore team',
      createdAt
    ]);
  }

  const team = one('SELECT id FROM teams WHERE name = ?', ['Finance Operations']);
  if (!one('SELECT id FROM users LIMIT 1')) {
    run(
      'INSERT INTO users (team_id, name, email, password_hash, role, password_changed_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [
        team.id,
        'DepreCore Admin',
        'admin@deprecore.local',
        bcrypt.hashSync(process.env.DEPRECORE_ADMIN_PASSWORD || 'ChangeMe123!', 12),
        'admin',
        createdAt,
        createdAt,
        createdAt
      ]
    );
  }

  if (!one('SELECT id FROM entities LIMIT 1')) {
    run(
      'INSERT INTO entities (name, legal_name, fiscal_year_end_month, base_currency, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)',
      ['Demo Company', 'Demo Company LLC', 12, 'USD', createdAt, createdAt]
    );
  }

  const refEntity = one('SELECT id FROM entities ORDER BY id LIMIT 1');
  if (refEntity) {
    seedReferenceValuesForCompany(refEntity.id);
    seedGlAccountsForCompany(refEntity.id);
  }

  run(
    `INSERT OR IGNORE INTO employees (
      entity_id, employee_number, name, email, department, location, manager_name, status, source, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [refEntity ? refEntity.id : null, 'E-1001', 'Jordan Avery', 'jordan.avery@example.com', 'Operations', 'Headquarters', 'DepreCore Admin', 'active', 'seed', createdAt, createdAt]
  );

  run(
    `INSERT OR IGNORE INTO workday_connections (
      name, tenant, base_url, token_url, workers_path, enabled, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    ['Primary Workday', '', '', '', '/workers', 0, createdAt, createdAt]
  );

  const starterTemplate = {
    category: 'Computer Equipment',
    useful_life_months: 60,
    depreciation_method: 'macrs_gds_200db_5',
    convention: 'half_year',
    gl_asset_account: '1500',
    gl_expense_account: '6400',
    gl_accumulated_account: '1590',
    business_use_percent: 100
  };
  run(
    'INSERT OR IGNORE INTO asset_templates (name, description, defaults, custom_fields, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)',
    [
      'Laptop or workstation',
      'Standard computer equipment template with 5-year tax life.',
      json(starterTemplate),
      json([
        { key: 'assigned_employee_id', label: 'Employee ID', type: 'text', required: false },
        { key: 'software_profile', label: 'Software profile', type: 'text', required: false }
      ]),
      createdAt,
      createdAt
    ]
  );

  const federalPath = path.join(process.cwd(), 'data', 'tax-rules', 'federal-2026.json');
  if (fs.existsSync(federalPath)) {
    const rules = expandRuleSet(JSON.parse(fs.readFileSync(federalPath, 'utf8')));
    run(
      `INSERT INTO tax_rule_sets (jurisdiction, name, effective_date, version, rules_json, active, source_note, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(jurisdiction, version) DO UPDATE SET
         name = excluded.name,
         effective_date = excluded.effective_date,
         rules_json = excluded.rules_json,
         active = excluded.active,
         source_note = excluded.source_note`,
      [
        rules.jurisdiction || 'US-FED',
        rules.name || 'Federal starter depreciation rules',
        rules.effectiveDate || rules.effective_date || '2026-01-01',
        rules.version || '2026.2',
        json(rules),
        1,
        rules.sourceNote || 'Starter rule template. Confirm current tax law before production filing.',
        createdAt
      ]
    );
  }

  // Seed the standard book set for the default company. Each company gets its own set; see
  // seedBooksForCompany (also called when a new company is created).
  const defaultEntity = one('SELECT id FROM entities ORDER BY id LIMIT 1');
  if (defaultEntity) seedBooksForCompany(defaultEntity.id);

  const settings = {
    server_fqdn: process.env.DEPRECORE_SERVER_FQDN || 'localhost',
    cors_allowed_domains: process.env.DEPRECORE_CORS_ORIGINS || 'http://localhost:5173',
    database_driver: 'node:sqlite',
    database_path: DB_PATH,
    default_books: 'GAAP,FEDERAL,STATE,AMT,ACE,USER',
    alerts_enabled: 'false',
    alerts_lead_days: '30',
    alerts_recipients: '',
    smtp_host: '',
    smtp_port: '587',
    smtp_secure: 'false',
    smtp_user: '',
    smtp_password: '',
    smtp_from: 'DepreCore <no-reply@deprecore.local>',
    webhook_enabled: 'false',
    webhook_url: '',
    webhook_secret: '',
    servicenow_instance_url: '',
    servicenow_username: '',
    servicenow_password: '',
    servicenow_incident_table: 'incident',
    servicenow_assignment_group: '',
    servicenow_caller_id: '',
    servicenow_ticket_enabled: 'false',
    servicenow_auto_ticket: 'false',
    servicenow_cmdb_table: 'cmdb_ci_hardware',
    servicenow_cmdb_query: '',
    servicenow_cmdb_limit: '200',
    servicenow_cmdb_map: '',
    servicenow_last_sync_at: '',
    servicenow_last_sync_status: '',
    pm_default_frequency_value: '3',
    pm_default_frequency_unit: 'months',
    pm_lead_days: '7'
  };
  for (const [key, value] of Object.entries(settings)) {
    run('INSERT OR IGNORE INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)', [key, String(value), createdAt]);
  }
}

function audit(actorId, action, entityType, entityId, beforeValue, afterValue) {
  run(
    'INSERT INTO audit_logs (actor_id, action, entity_type, entity_id, before_json, after_json, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)',
    [actorId || null, action, entityType, entityId === undefined ? null : String(entityId), json(beforeValue), json(afterValue), now()]
  );
}

module.exports = {
  DB_PATH,
  all,
  audit,
  db,
  initialize,
  json,
  now,
  one,
  parseJson,
  run,
  seedBooksForCompany,
  seedGlAccountsForCompany,
  seedReferenceValuesForCompany,
  tx
};