import sqlite3 from 'sqlite3'
import { open } from 'sqlite'
import fs from 'fs/promises'
import { constants as fsConstants } from 'fs'
import path from 'path'
import { fileURLToPath } from 'url'
import bcrypt from 'bcryptjs'

const __filename = fileURLToPath(import.meta.url)
const __dirname = path.dirname(__filename)
const appRoot = path.join(__dirname, '..')
const legacyDbPath = path.resolve(path.join(__dirname, 'database.sqlite'))
const dbPath = path.resolve(
  process.env.SQLITE_DB_PATH
    ? path.isAbsolute(process.env.SQLITE_DB_PATH)
      ? process.env.SQLITE_DB_PATH
      : path.join(appRoot, process.env.SQLITE_DB_PATH)
    : path.join(appRoot, 'tmp', 'data', 'database.sqlite'),
)

async function ensureDatabaseFile() {
  await fs.mkdir(path.dirname(dbPath), { recursive: true })

  try {
    await fs.access(dbPath, fsConstants.F_OK)
    return
  } catch {
    // Fall back to copying the legacy database on first run.
  }

  if (dbPath === legacyDbPath) {
    return
  }

  try {
    await fs.access(legacyDbPath, fsConstants.F_OK)
    await fs.copyFile(legacyDbPath, dbPath)
  } catch {
    // No legacy database to migrate, SQLite will create a new one.
  }
}

export async function initDb() {
  await ensureDatabaseFile()

  const db = await open({
    filename: dbPath,
    driver: sqlite3.Database
  })

  await db.exec(`
    PRAGMA foreign_keys = ON;
    PRAGMA busy_timeout = 5000;
  `)

  // Create tables
  await db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      role TEXT DEFAULT 'MEMBER' -- ADMIN, BANG_CHU, BANG_PHO, MEMBER
    );

    CREATE TABLE IF NOT EXISTS state (
      id INTEGER PRIMARY KEY CHECK (id = 1),
      registrationLocked INTEGER DEFAULT 0,
      teamGroups TEXT,
      teamPositions TEXT
    );

    CREATE TABLE IF NOT EXISTS members (
      id INTEGER PRIMARY KEY,
      char_name TEXT NOT NULL,
      class_name TEXT NOT NULL,
      team_slot INTEGER,
      type TEXT NOT NULL,
      has_item INTEGER DEFAULT 0,
      created_by INTEGER,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(created_by) REFERENCES users(id)
    );

    CREATE TABLE IF NOT EXISTS member_skills (
      id INTEGER PRIMARY KEY,
      member_id INTEGER NOT NULL,
      skill_id INTEGER NOT NULL,
      pos_x REAL NOT NULL,
      pos_y REAL NOT NULL,
      FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS skill_library (
      id INTEGER PRIMARY KEY,
      url TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS save_slots (
      id INTEGER PRIMARY KEY,
      saved_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      state_json TEXT NOT NULL
    );
  `)

  // Initialize global state row
  const stateExists = await db.get('SELECT id FROM state WHERE id = 1')
  if (!stateExists) {
    await db.run("INSERT INTO state (id, registrationLocked, teamGroups, teamPositions) VALUES (1, 0, '{}', '{}')")
  }

  const stateRow = await db.get('SELECT registrationLocked FROM state WHERE id = 1')
  const memberCountRow = await db.get('SELECT COUNT(*) AS count FROM members')

  // Keep old rosters unchanged, but auto-open brand new/empty rosters so
  // members can create their first character without an admin toggle step.
  if (stateRow?.registrationLocked === 1 && Number(memberCountRow?.count || 0) === 0) {
    await db.run('UPDATE state SET registrationLocked = 0 WHERE id = 1')
  }

  // Create default ADMIN if none exists
  const adminExists = await db.get("SELECT id FROM users WHERE role = 'ADMIN'")
  if (!adminExists) {
    const hash = await bcrypt.hash('admin123', 10)
    await db.run("INSERT INTO users (username, password, role) VALUES (?, ?, 'ADMIN')", ['admin', hash])
  }

  return db
}
