import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'

let db: Database.Database | null = null

export function getDb(): Database.Database {
  if (db) return db
  const dataDir = path.join(process.cwd(), 'data')
  if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true })
  const dbPath = path.join(dataDir, 'app.db')
  db = new Database(dbPath)
  db.pragma('journal_mode = WAL')
  db.pragma('foreign_keys = ON')
  initTables(db)
  return db
}

function initTables(db: Database.Database) {
  db.exec(`
    -- 设备型号
    CREATE TABLE IF NOT EXISTS device_models (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      code TEXT NOT NULL UNIQUE,
      status TEXT NOT NULL DEFAULT '在产',
      description TEXT DEFAULT '',
      create_date TEXT NOT NULL
    );

    -- 装配 Checklist 模板
    CREATE TABLE IF NOT EXISTS checklist_templates (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      model_code TEXT NOT NULL,
      name TEXT NOT NULL,
      required INTEGER NOT NULL DEFAULT 1,
      sort_order INTEGER NOT NULL DEFAULT 0,
      FOREIGN KEY (model_code) REFERENCES device_models(code)
    );

    -- 板卡类型
    CREATE TABLE IF NOT EXISTS board_types (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      category TEXT NOT NULL,
      device_models TEXT NOT NULL DEFAULT '[]',
      description TEXT DEFAULT '',
      status TEXT NOT NULL DEFAULT '启用'
    );

    -- 板卡版本
    CREATE TABLE IF NOT EXISTS board_versions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      type TEXT NOT NULL,
      version TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT '在产'
    );

    -- 物料实例（板卡列表）
    CREATE TABLE IF NOT EXISTS materials (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      sn TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL DEFAULT '',
      category TEXT NOT NULL DEFAULT '',
      type TEXT NOT NULL,
      device_model TEXT NOT NULL DEFAULT '',
      version TEXT NOT NULL,
      description TEXT DEFAULT '',
      firmware TEXT DEFAULT '-',
      status TEXT NOT NULL DEFAULT '在库',
      device_sn TEXT DEFAULT '-',
      production_date TEXT NOT NULL,
      calib_status TEXT DEFAULT '-',
      calib_date TEXT DEFAULT '-'
    );

    -- 设备
    CREATE TABLE IF NOT EXISTS devices (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      sn TEXT NOT NULL UNIQUE,
      model TEXT NOT NULL,
      type TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT '装配中',
      firmware TEXT DEFAULT '',
      production_date TEXT NOT NULL,
      customer TEXT DEFAULT '-',
      batch TEXT DEFAULT ''
    );

    -- BOM 模板
    CREATE TABLE IF NOT EXISTS bom_templates (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      model_code TEXT NOT NULL,
      name TEXT NOT NULL,
      material_name TEXT NOT NULL DEFAULT '',
      model TEXT NOT NULL,
      versions TEXT NOT NULL DEFAULT '[]',
      qty INTEGER NOT NULL DEFAULT 1,
      required INTEGER NOT NULL DEFAULT 1,
      need_calibration INTEGER NOT NULL DEFAULT 0,
      enforce_version_match INTEGER NOT NULL DEFAULT 0,
      FOREIGN KEY (model_code) REFERENCES device_models(code)
    );

    -- 固件
    CREATE TABLE IF NOT EXISTS firmware (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      version TEXT NOT NULL,
      board_version TEXT DEFAULT '-',
      type TEXT NOT NULL,
      date TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT '草稿',
      size TEXT DEFAULT '',
      downloads INTEGER DEFAULT 0,
      hw_range TEXT DEFAULT '',
      upgrade_type TEXT DEFAULT '可选',
      signed INTEGER DEFAULT 0,
      md5 TEXT DEFAULT '',
      sha256 TEXT DEFAULT '',
      notes TEXT DEFAULT '[]',
      model_code TEXT DEFAULT ''
    );

    -- 配置文件
    CREATE TABLE IF NOT EXISTS config_files (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      model TEXT NOT NULL,
      version TEXT NOT NULL,
      create_time TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT '生效',
      voltage TEXT DEFAULT '',
      current TEXT DEFAULT '',
      duty_cycle TEXT DEFAULT '',
      pulse_width TEXT DEFAULT '',
      iterations TEXT DEFAULT '',
      channels INTEGER DEFAULT 0,
      sample_rate TEXT DEFAULT '',
      voltage_range TEXT DEFAULT '',
      waveform TEXT DEFAULT '',
      ssid TEXT DEFAULT ''
    );

    -- 授权
    CREATE TABLE IF NOT EXISTS licenses (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      model TEXT NOT NULL,
      modules TEXT NOT NULL,
      expiry TEXT DEFAULT '',
      status TEXT NOT NULL DEFAULT '生效'
    );

    -- 维修工单
    CREATE TABLE IF NOT EXISTS repair_orders (
      id TEXT PRIMARY KEY,
      sn TEXT NOT NULL,
      fault_type TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT '待处理',
      priority TEXT NOT NULL DEFAULT '中',
      assignee TEXT DEFAULT '',
      create_date TEXT NOT NULL,
      description TEXT DEFAULT ''
    );

    -- 报废记录
    CREATE TABLE IF NOT EXISTS scrap_records (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      sn TEXT NOT NULL,
      model TEXT NOT NULL,
      reason TEXT NOT NULL,
      applicant TEXT DEFAULT '',
      status TEXT NOT NULL DEFAULT '待审批',
      order_id TEXT DEFAULT '',
      date TEXT NOT NULL,
      value INTEGER DEFAULT 0,
      materials TEXT DEFAULT '[]'
    );

    -- 物料分类类型
    CREATE TABLE IF NOT EXISTS material_categories (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      description TEXT DEFAULT '',
      has_firmware INTEGER NOT NULL DEFAULT 0,
      has_calibration INTEGER NOT NULL DEFAULT 0,
      sort_order INTEGER NOT NULL DEFAULT 0,
      status TEXT NOT NULL DEFAULT '启用'
    );

    -- 应用列表
    CREATE TABLE IF NOT EXISTS applications (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      package_name TEXT DEFAULT '',
      description TEXT DEFAULT '',
      logo_url TEXT DEFAULT '',
      status INTEGER NOT NULL DEFAULT 1,
      create_time TEXT NOT NULL,
      update_time TEXT NOT NULL
    );

    -- 应用平台
    CREATE TABLE IF NOT EXISTS app_platforms (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      app_id INTEGER NOT NULL,
      platform_type INTEGER NOT NULL DEFAULT 2,
      description TEXT DEFAULT '',
      extend_info TEXT DEFAULT '',
      create_time TEXT NOT NULL,
      FOREIGN KEY (app_id) REFERENCES applications(id)
    );

    -- 平台版本
    CREATE TABLE IF NOT EXISTS app_platform_versions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      app_id INTEGER NOT NULL,
      platform_id INTEGER NOT NULL,
      major_version INTEGER NOT NULL DEFAULT 0,
      minor_version INTEGER NOT NULL DEFAULT 0,
      patch_version INTEGER NOT NULL DEFAULT 0,
      version_name TEXT NOT NULL,
      description TEXT DEFAULT '',
      file_type TEXT DEFAULT '',
      file_url TEXT DEFAULT '',
      file_size INTEGER DEFAULT 0,
      distribution_type TEXT DEFAULT '',
      primary_url TEXT DEFAULT '',
      fallback_url TEXT DEFAULT '',
      url_expire_time TEXT DEFAULT '',
      signature_info TEXT DEFAULT '',
      min_support_version TEXT DEFAULT '',
      os_min_version TEXT DEFAULT '',
      status INTEGER NOT NULL DEFAULT 1,
      is_force_update INTEGER NOT NULL DEFAULT 0,
      release_date TEXT NOT NULL,
      changelog TEXT DEFAULT '[]',
      FOREIGN KEY (app_id) REFERENCES applications(id),
      FOREIGN KEY (platform_id) REFERENCES app_platforms(id)
    );

    -- 应用统计
    CREATE TABLE IF NOT EXISTS app_statistics (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      app_id INTEGER NOT NULL,
      version_id INTEGER DEFAULT NULL,
      date TEXT NOT NULL,
      download_count INTEGER DEFAULT 0,
      install_count INTEGER DEFAULT 0,
      active_count INTEGER DEFAULT 0,
      crash_count INTEGER DEFAULT 0,
      create_time TEXT NOT NULL,
      FOREIGN KEY (app_id) REFERENCES applications(id)
    );
  `)
}
