/**
 * pi-task - Task Store (SQLite-backed Kanban)
 *
 * Pure data layer. No framework dependencies.
 * Uses sql.js (WebAssembly SQLite) for cross-runtime compatibility.
 * Works in both Node.js and Bun environments.
 */

import initSqlJs, { type Database as SqlJsDatabase } from "sql.js";
import { join, dirname } from "path";
import { homedir } from "os";
import { existsSync, mkdirSync, readFileSync, writeFileSync } from "fs";
import { fileURLToPath } from "url";

// ═════════════════════════════════════════════════════════════════════════════
// Types
// ═════════════════════════════════════════════════════════════════════════════

export type TaskStatus =
  | "backlog"
  | "needs-assignment"
  | "in-progress"
  | "needs-review"
  | "blocked"
  | "done";

export type TaskPriority = "low" | "medium" | "high" | "critical";

export interface Task {
  id: string;
  title: string;
  description: string;
  status: TaskStatus;
  priority: TaskPriority;
  assignee?: string;
  sessionId: string;
  parentId?: string;
  tags: string[];
  createdAt: number;
  updatedAt: number;
  completedAt?: number;
  metadata: Record<string, unknown>;
}

export interface TaskComment {
  id: string;
  taskId: string;
  author: string;
  content: string;
  timestamp: number;
}

export interface TaskHistoryEntry {
  id: string;
  taskId: string;
  fromStatus: string | null;
  toStatus: string;
  changedBy: string;
  timestamp: number;
  note: string;
}

export const TASK_COLUMNS: { id: TaskStatus; label: string; emoji: string }[] = [
  { id: "backlog", label: "Backlog", emoji: "📋" },
  { id: "needs-assignment", label: "Needs Assignment", emoji: "👤" },
  { id: "in-progress", label: "In Progress", emoji: "🏗️" },
  { id: "needs-review", label: "Needs Review", emoji: "👀" },
  { id: "blocked", label: "Blocked", emoji: "🚫" },
  { id: "done", label: "Done", emoji: "✅" },
];

// ═════════════════════════════════════════════════════════════════════════════
// Database
// ═════════════════════════════════════════════════════════════════════════════

/**
 * Locate the sql-wasm.wasm file.
 * Tries multiple strategies to find it in the node_modules hierarchy.
 */
function locateWasmBinary(): string | undefined {
  const candidates: string[] = [];

  try {
    // Strategy 1: Relative to this file's location (works in bundled installs)
    const thisDir = dirname(fileURLToPath(import.meta.url));
    candidates.push(join(thisDir, "..", "node_modules", "sql.js", "dist", "sql-wasm.wasm"));
    candidates.push(join(thisDir, "node_modules", "sql.js", "dist", "sql-wasm.wasm"));
    // Also check sibling dist dirs (when hoisted)
    candidates.push(join(thisDir, "sql-wasm.wasm"));

    // Strategy 2: Walk up from this file looking for sql.js
    let dir = thisDir;
    for (let i = 0; i < 10; i++) {
      candidates.push(join(dir, "node_modules", "sql.js", "dist", "sql-wasm.wasm"));
      dir = dirname(dir);
    }

    // Strategy 3: require.resolve (Node.js only)
    try {
      const sqlJsPath = require.resolve("sql.js");
      candidates.push(join(dirname(sqlJsPath), "..", "dist", "sql-wasm.wasm"));
      candidates.push(join(dirname(sqlJsPath), "dist", "sql-wasm.wasm"));
    } catch { /* require.resolve may not work in ESM */ }
  } catch { /* import.meta.url may not be available */ }

  for (const candidate of candidates) {
    if (existsSync(candidate)) {
      return candidate;
    }
  }

  return undefined;
}

const DEFAULT_DB_DIR = join(homedir(), ".0xkobold");
const DEFAULT_DB_PATH = join(DEFAULT_DB_DIR, "tasks.db");

let db: SqlJsDatabase | null = null;
let saveTimer: ReturnType<typeof setTimeout> | null = null;

/**
 * Initialize the task database.
 * Call with custom path for testing, or omit for default.
 * Must be called before any other operations.
 */
export async function initDatabase(dbPath?: string): Promise<SqlJsDatabase> {
  if (db) return db;

  const path = dbPath || DEFAULT_DB_PATH;
  const dir = join(path, "..");
  if (!existsSync(dir)) {
    mkdirSync(dir, { recursive: true });
  }

  const wasmBinary = locateWasmBinary();
  const SQL = wasmBinary
    ? await initSqlJs({ locateFile: () => wasmBinary })
    : await initSqlJs();

  if (existsSync(path)) {
    const buffer = readFileSync(path);
    db = new SQL.Database(buffer);
  } else {
    db = new SQL.Database();
  }

  db.run("PRAGMA journal_mode = WAL;");
  db.run("PRAGMA foreign_keys = OFF;");

  initTables();
  scheduleSave();

  return db;
}

/**
 * Synchronous init for cases where sql.js is already loaded.
 * Prefer async initDatabase() when possible.
 */
export function initDatabaseSync(sqlJsModule: any, dbPath?: string): SqlJsDatabase {
  if (db) return db;

  const path = dbPath || DEFAULT_DB_PATH;
  const dir = join(path, "..");
  if (!existsSync(dir)) {
    mkdirSync(dir, { recursive: true });
  }

  const SQL = new sqlJsModule.Database
    ? sqlJsModule
    : sqlJsModule;

  if (existsSync(path)) {
    const buffer = readFileSync(path);
    db = new SQL.Database(buffer);
  } else {
    db = new SQL.Database();
  }

  db.run("PRAGMA journal_mode = WAL;");
  db.run("PRAGMA foreign_keys = OFF;");

  initTables();
  scheduleSave();

  return db;
}

function initTables(): void {
  console.assert(db !== null, "Database must be initialized");

  db!.run(`
    CREATE TABLE IF NOT EXISTS tasks (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      description TEXT,
      status TEXT NOT NULL DEFAULT 'backlog',
      priority TEXT NOT NULL DEFAULT 'medium',
      assignee TEXT,
      session_id TEXT NOT NULL,
      parent_id TEXT,
      tags TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      completed_at INTEGER,
      metadata TEXT,
      FOREIGN KEY (parent_id) REFERENCES tasks(id) ON DELETE CASCADE
    )
  `);

  db!.run(`
    CREATE TABLE IF NOT EXISTS task_comments (
      id TEXT PRIMARY KEY,
      task_id TEXT NOT NULL,
      author TEXT NOT NULL,
      content TEXT NOT NULL,
      timestamp INTEGER NOT NULL,
      FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
    )
  `);

  db!.run(`
    CREATE TABLE IF NOT EXISTS task_history (
      id TEXT PRIMARY KEY,
      task_id TEXT NOT NULL,
      from_status TEXT,
      to_status TEXT NOT NULL,
      changed_by TEXT,
      timestamp INTEGER NOT NULL,
      note TEXT,
      FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
    )
  `);

  db!.run(`CREATE INDEX IF NOT EXISTS idx_tasks_session ON tasks(session_id)`);
  db!.run(`CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status)`);
  db!.run(`CREATE INDEX IF NOT EXISTS idx_tasks_assignee ON tasks(assignee)`);
}

/**
 * Schedule a debounced save to disk.
 */
function scheduleSave(): void {
  if (saveTimer) clearTimeout(saveTimer);
  saveTimer = setTimeout(() => saveToDisk(), 5000);
}

/**
 * Save database to disk.
 */
function saveToDisk(): void {
  if (!db) return;
  try {
    const data = db.export();
    const path = DEFAULT_DB_PATH;
    const buffer = Buffer.from(data);
    writeFileSync(path, buffer);
  } catch (err) {
    console.error("[pi-task] Error saving database to disk:", err);
  }
}

/**
 * Close the database connection and save.
 */
export function closeDatabase(): void {
  if (db) {
    saveToDisk();
    db.close();
    db = null;
  }
  if (saveTimer) {
    clearTimeout(saveTimer);
    saveTimer = null;
  }
}

/**
 * Check if the database is initialized.
 */
export function isInitialized(): boolean {
  return db !== null;
}

// ═════════════════════════════════════════════════════════════════════════════
// Helpers
// ═════════════════════════════════════════════════════════════════════════════

let idCounter = 0;
function uniqueId(prefix: string): string {
  return `${prefix}-${Date.now()}-${idCounter++}-${Math.random().toString(36).slice(2, 5)}`;
}
function generateTaskId(): string {
  return uniqueId("task");
}

function rowToTask(row: Record<string, any>): Task {
  return {
    id: row.id,
    title: row.title,
    description: row.description,
    status: row.status,
    priority: row.priority,
    assignee: row.assignee,
    sessionId: row.session_id,
    parentId: row.parent_id,
    tags: JSON.parse(String(row.tags || "[]")),
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    completedAt: row.completed_at,
    metadata: JSON.parse(String(row.metadata || "{}")),
  };
}

/**
 * Run a query that returns rows.
 */
function queryAll(sql: string, params: any[] = []): Record<string, any>[] {
  console.assert(db !== null, "Database must be initialized");
  const stmt = db!.prepare(sql);
  if (params.length > 0) stmt.bind(params);
  const rows: Record<string, any>[] = [];
  while (stmt.step()) {
    const obj = stmt.getAsObject();
    rows.push(obj as Record<string, any>);
  }
  stmt.free();
  return rows;
}

/**
 * Run a query that returns a single row.
 */
function queryOne(sql: string, params: any[] = []): Record<string, any> | null {
  const rows = queryAll(sql, params);
  return rows.length > 0 ? rows[0] : null;
}

/**
 * Run a statement (INSERT, UPDATE, DELETE).
 */
function run(sql: string, params: any[] = []): void {
  console.assert(db !== null, "Database must be initialized");
  db!.run(sql, params);
  scheduleSave();
}

// ═════════════════════════════════════════════════════════════════════════════
// CRUD Operations
// ═════════════════════════════════════════════════════════════════════════════

/**
 * Create a new task.
 */
export function createTask(
  title: string,
  description: string = "",
  options: Partial<Pick<Task, "status" | "priority" | "assignee" | "sessionId" | "parentId" | "tags" | "metadata">> = {},
  sessionId: string = "default"
): Task {
  console.assert(db !== null, "Database must be initialized with initDatabase() first");

  const id = generateTaskId();
  const now = Date.now();

  const task: Task = {
    id,
    title,
    description,
    status: options.status || "backlog",
    priority: options.priority || "medium",
    assignee: options.assignee,
    sessionId: options.sessionId || sessionId,
    parentId: options.parentId,
    tags: options.tags || [],
    createdAt: now,
    updatedAt: now,
    metadata: options.metadata || {},
  };

  run(
    `INSERT INTO tasks (id, title, description, status, priority, assignee, session_id, parent_id, tags, created_at, updated_at, metadata)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [task.id, task.title, task.description, task.status, task.priority,
     task.assignee || null, task.sessionId || null, task.parentId || null,
     JSON.stringify(task.tags), task.createdAt, task.updatedAt,
     JSON.stringify(task.metadata)]
  );

  run(
    `INSERT INTO task_history (id, task_id, from_status, to_status, changed_by, timestamp, note)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [uniqueId("hist"), task.id, null, task.status, sessionId, now, "Task created"]
  );

  return task;
}

/**
 * Get a task by ID (exact match or prefix).
 */
export function getTask(id: string): Task | null {
  const row = queryOne("SELECT * FROM tasks WHERE id = ?", [id]);
  if (row) return rowToTask(row);

  // Prefix match
  const prefixRows = queryAll("SELECT * FROM tasks WHERE id LIKE ? LIMIT 5", [`${id}%`]);
  if (prefixRows.length === 1) return rowToTask(prefixRows[0]);

  return null;
}

/**
 * Update task status. Records history.
 */
export function updateTaskStatus(id: string, newStatus: TaskStatus, note?: string, changedBy: string = "default"): boolean {
  const task = getTask(id);
  if (!task) return false;

  const oldStatus = task.status;
  const now = Date.now();

  run(
    `UPDATE tasks SET status = ?, updated_at = ?, completed_at = ? WHERE id = ?`,
    [newStatus, now, newStatus === "done" ? now : task.completedAt || null, id]
  );

  run(
    `INSERT INTO task_history (id, task_id, from_status, to_status, changed_by, timestamp, note)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [uniqueId("hist"), id, oldStatus, newStatus, changedBy, now, note || `Moved from ${oldStatus} to ${newStatus}`]
  );

  return true;
}

/**
 * Assign a task. Moves to in-progress if in backlog/needs-assignment.
 */
export function assignTask(id: string, assignee: string, changedBy: string = "default"): boolean {
  const task = getTask(id);
  if (!task) return false;

  run(
    `UPDATE tasks SET assignee = ?, updated_at = ? WHERE id = ?`,
    [assignee, Date.now(), id]
  );

  if (task.status === "backlog" || task.status === "needs-assignment") {
    updateTaskStatus(id, "in-progress", `Assigned to ${assignee}`, changedBy);
  }

  return true;
}

/**
 * Add a comment to a task.
 */
export function addComment(taskId: string, content: string, author: string = "default"): boolean {
  run(
    `INSERT INTO task_comments (id, task_id, author, content, timestamp)
     VALUES (?, ?, ?, ?, ?)`,
    [uniqueId("comment"), taskId, author, content, Date.now()]
  );
  return true;
}

/**
 * Get comments for a task.
 */
export function getComments(taskId: string): TaskComment[] {
  const rows = queryAll("SELECT * FROM task_comments WHERE task_id = ? ORDER BY timestamp", [taskId]);
  return rows.map(row => ({
    id: row.id as string,
    taskId: row.task_id as string,
    author: row.author as string,
    content: row.content as string,
    timestamp: row.timestamp as number,
  }));
}

/**
 * List tasks with optional filters.
 */
export function listTasks(filter?: {
  status?: TaskStatus;
  sessionId?: string;
  assignee?: string;
}): Task[] {
  let query = "SELECT * FROM tasks WHERE 1=1";
  const params: any[] = [];

  if (filter?.status) {
    query += " AND status = ?";
    params.push(filter.status);
  }
  if (filter?.sessionId) {
    query += " AND session_id = ?";
    params.push(filter.sessionId);
  }
  if (filter?.assignee) {
    query += " AND assignee = ?";
    params.push(filter.assignee);
  }

  query += " ORDER BY priority DESC, updated_at DESC";

  const rows = queryAll(query, params);
  return rows.map(rowToTask);
}

/**
 * Get full kanban board.
 */
export function getBoard(): Record<TaskStatus, Task[]> {
  const board: Record<TaskStatus, Task[]> = {
    backlog: [],
    "needs-assignment": [],
    "in-progress": [],
    "needs-review": [],
    blocked: [],
    done: [],
  };

  for (const task of listTasks()) {
    board[task.status].push(task);
  }
  return board;
}

/**
 * Delete a task.
 */
export function deleteTask(id: string): boolean {
  run("DELETE FROM tasks WHERE id = ?", [id]);
  return true;
}

/**
 * Get task history.
 */
export function getTaskHistory(taskId: string): TaskHistoryEntry[] {
  const rows = queryAll("SELECT * FROM task_history WHERE task_id = ? ORDER BY timestamp", [taskId]);
  return rows.map(row => ({
    id: row.id as string,
    taskId: row.task_id as string,
    fromStatus: row.from_status as string | null,
    toStatus: row.to_status as string,
    changedBy: row.changed_by as string,
    timestamp: row.timestamp as number,
    note: row.note as string,
  }));
}