SheetHappens/lib/db.ts

import * as SQLite from "expo-sqlite";
import type { Incident, IncidentDraft, IncidentStatus } from "@/types/incident";

const DB_NAME = "sheethappens.db";

let dbInstance: SQLite.SQLiteDatabase | null = null;

async function getDb(): Promise<SQLite.SQLiteDatabase> {
  if (dbInstance) return dbInstance;
  dbInstance = await SQLite.openDatabaseAsync(DB_NAME);
  await migrate(dbInstance);
  return dbInstance;
}

async function migrate(db: SQLite.SQLiteDatabase): Promise<void> {
  await db.execAsync(`
    CREATE TABLE IF NOT EXISTS schema_version (
      version INTEGER PRIMARY KEY NOT NULL
    );
  `);

  const row = await db.getFirstAsync<{ version: number }>(
    "SELECT MAX(version) as version FROM schema_version"
  );
  const current = row?.version ?? 0;

  if (current < 1) {
    await db.execAsync(`
      CREATE TABLE IF NOT EXISTS incidents (
        id          TEXT PRIMARY KEY NOT NULL,
        title       TEXT NOT NULL DEFAULT '',
        service     TEXT NOT NULL DEFAULT '',
        symptom     TEXT NOT NULL DEFAULT '',
        root_cause  TEXT NOT NULL DEFAULT '',
        fix         TEXT NOT NULL DEFAULT '',
        status      TEXT NOT NULL DEFAULT 'open',
        created_at  TEXT NOT NULL,
        updated_at  TEXT NOT NULL,
        tags        TEXT NOT NULL DEFAULT '[]'
      );
      CREATE INDEX IF NOT EXISTS idx_incidents_status
        ON incidents(status);
      CREATE INDEX IF NOT EXISTS idx_incidents_created_at
        ON incidents(created_at DESC);
      INSERT INTO schema_version (version) VALUES (1);
    `);
  }

  if (current < 2) {
    await db.execAsync(`
      ALTER TABLE incidents ADD COLUMN git_pushed_at TEXT;
      INSERT INTO schema_version (version) VALUES (2);
    `);
  }
}

// --- row mapper ---

interface IncidentRow {
  id: string;
  title: string;
  service: string;
  symptom: string;
  root_cause: string;
  fix: string;
  status: IncidentStatus;
  created_at: string;
  updated_at: string;
  tags: string;
  git_pushed_at: string | null;
}

function rowToIncident(row: IncidentRow): Incident {
  return {
    id: row.id,
    title: row.title,
    service: row.service,
    symptom: row.symptom,
    rootCause: row.root_cause,
    fix: row.fix,
    status: row.status,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    tags: JSON.parse(row.tags) as string[],
    gitPushedAt: row.git_pushed_at ?? null,
  };
}

// --- CRUD ---

export async function createIncident(draft: IncidentDraft): Promise<Incident> {
  const db = await getDb();
  const id = generateId();
  const now = new Date().toISOString();

  await db.runAsync(
    `INSERT INTO incidents
      (id, title, service, symptom, root_cause, fix, status, created_at, updated_at, tags)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id,
      draft.title,
      draft.service,
      draft.symptom,
      draft.rootCause,
      draft.fix,
      draft.status,
      now,
      now,
      JSON.stringify(draft.tags),
    ]
  );

  return {
    ...draft,
    id,
    createdAt: now,
    updatedAt: now,
    gitPushedAt: null,
  };
}

export async function getIncidents(limit = 50): Promise<Incident[]> {
  const db = await getDb();
  const rows = await db.getAllAsync<IncidentRow>(
    "SELECT * FROM incidents ORDER BY created_at DESC LIMIT ?",
    [limit]
  );
  return rows.map(rowToIncident);
}

export async function getIncidentById(id: string): Promise<Incident | null> {
  const db = await getDb();
  const row = await db.getFirstAsync<IncidentRow>(
    "SELECT * FROM incidents WHERE id = ?",
    [id]
  );
  return row ? rowToIncident(row) : null;
}

export async function updateIncident(
  id: string,
  updates: Partial<IncidentDraft>
): Promise<void> {
  const db = await getDb();
  const now = new Date().toISOString();

  const fields: string[] = [];
  const values: (string | null)[] = [];

  if (updates.title !== undefined) { fields.push("title = ?"); values.push(updates.title); }
  if (updates.service !== undefined) { fields.push("service = ?"); values.push(updates.service); }
  if (updates.symptom !== undefined) { fields.push("symptom = ?"); values.push(updates.symptom); }
  if (updates.rootCause !== undefined) { fields.push("root_cause = ?"); values.push(updates.rootCause); }
  if (updates.fix !== undefined) { fields.push("fix = ?"); values.push(updates.fix); }
  if (updates.status !== undefined) { fields.push("status = ?"); values.push(updates.status); }
  if (updates.tags !== undefined) { fields.push("tags = ?"); values.push(JSON.stringify(updates.tags)); }

  if (fields.length === 0) return;

  fields.push("updated_at = ?");
  values.push(now);
  values.push(id);

  await db.runAsync(
    `UPDATE incidents SET ${fields.join(", ")} WHERE id = ?`,
    values
  );
}

export async function deleteIncident(id: string): Promise<void> {
  const db = await getDb();
  await db.runAsync("DELETE FROM incidents WHERE id = ?", [id]);
}

export async function markIncidentPushed(id: string): Promise<void> {
  const db = await getDb();
  await db.runAsync(
    "UPDATE incidents SET git_pushed_at = ? WHERE id = ?",
    [new Date().toISOString(), id]
  );
}

export async function getMaxTitleNumber(prefix: string): Promise<number> {
  const db = await getDb();
  const rows = await db.getAllAsync<{ title: string }>(
    "SELECT title FROM incidents WHERE title LIKE ?",
    [`${prefix}%`]
  );
  let max = 0;
  for (const row of rows) {
    const n = parseInt(row.title.slice(prefix.length), 10);
    if (!isNaN(n) && n > max) max = n;
  }
  return max;
}

export async function getDistinctServices(): Promise<string[]> {
  const db = await getDb();
  const rows = await db.getAllAsync<{ service: string }>(
    "SELECT DISTINCT service FROM incidents WHERE service != '' ORDER BY service"
  );
  return rows.map((r) => r.service);
}

// --- UUID v4 (no external dep) ---

function generateId(): string {
  const hex = "0123456789abcdef";
  let uuid = "";
  for (let i = 0; i < 36; i++) {
    if (i === 8 || i === 13 || i === 18 || i === 23) {
      uuid += "-";
    } else if (i === 14) {
      uuid += "4";
    } else if (i === 19) {
      uuid += hex[(Math.random() * 4) | 8];
    } else {
      uuid += hex[(Math.random() * 16) | 0];
    }
  }
  return uuid;
}