import type { DatabaseSync } from "node:sqlite";
import type { BootstrapTree } from "./webClient.ts";

export const LOCAL_WORKSPACE_ID = "local";

export interface WorkspaceChoice {
  workspace_id: string;
  workspace_name: string;
  workspace_slug: string | null;
  org_id: string;
  org_name: string;
  org_slug: string | null;
  is_default: number;
}

const WORKSPACE_TABLES = [
  "unified_conversation",
  "unified_message",
  "contact",
  "contact_identity",
  "contact_merge",
  "contact_tag",
  "contact_note",
  "pipeline",
  "deal",
  "company",
  "crm_deal",
  "crm_task",
  "crm_mutation_queue",
  "calendar_event",
  "outbound_draft",
  "draft_metadata",
  "audio_transcript",
  "call_recording",
  "call_recording_rule",
  "call_auto_record_state",
  "call_transcript",
  "call_summary",
  "crm_update_proposal",
] as const;

export function ensureWorkspaceSchema(db: DatabaseSync) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS sync_workspace (
      workspace_id   TEXT PRIMARY KEY,
      workspace_name TEXT NOT NULL,
      workspace_slug TEXT,
      org_id         TEXT NOT NULL,
      org_name       TEXT NOT NULL,
      org_slug       TEXT,
      is_default     INTEGER NOT NULL DEFAULT 0,
      updated_at     INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS sync_workspace_selection (
      key          TEXT PRIMARY KEY,
      workspace_id TEXT NOT NULL,
      org_id       TEXT,
      updated_at   INTEGER NOT NULL
    );
  `);
}

function hasWorkspaceColumn(db: DatabaseSync, table: string): boolean {
  try {
    return (db.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string }>).some((c) => c.name === "workspace_id");
  } catch {
    return false;
  }
}

function hasTable(db: DatabaseSync, table: string): boolean {
  try {
    const row = db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(table) as any;
    return !!row;
  } catch {
    return false;
  }
}

export function selectedWorkspaceId(db: DatabaseSync): string {
  ensureWorkspaceSchema(db);
  const row = db.prepare("SELECT workspace_id FROM sync_workspace_selection WHERE key='active'").get() as any;
  return String(row?.workspace_id || LOCAL_WORKSPACE_ID);
}

export function listWorkspaces(db: DatabaseSync): WorkspaceChoice[] {
  ensureWorkspaceSchema(db);
  return db.prepare(
    `SELECT workspace_id, workspace_name, workspace_slug, org_id, org_name, org_slug, is_default
     FROM sync_workspace ORDER BY org_name COLLATE NOCASE, is_default DESC, workspace_name COLLATE NOCASE`,
  ).all() as WorkspaceChoice[];
}

function chooseWorkspace(tree: BootstrapTree, existing: string | null): WorkspaceChoice | null {
  const rows = bootstrapRows(tree);
  if (!rows.length) return null;
  if (existing) {
    const found = rows.find((r) => r.workspace_id === existing);
    if (found) return found;
  }
  const activeOrgRows = tree.activeOrganizationId
    ? rows.filter((r) => r.org_id === tree.activeOrganizationId)
    : rows;
  return activeOrgRows.find((r) => r.is_default === 1) ?? activeOrgRows[0] ?? rows[0] ?? null;
}

function bootstrapRows(tree: BootstrapTree): WorkspaceChoice[] {
  const rows: WorkspaceChoice[] = [];
  for (const org of tree.organizations) {
    for (const ws of org.workspaces) {
      rows.push({
        workspace_id: ws.id,
        workspace_name: ws.name,
        workspace_slug: ws.slug ?? null,
        org_id: org.id,
        org_name: org.name,
        org_slug: org.slug ?? null,
        is_default: ws.isDefault ? 1 : 0,
      });
    }
  }
  return rows;
}

export function storeBootstrapWorkspaces(db: DatabaseSync, tree: BootstrapTree): { selectedWorkspaceId: string; workspaces: WorkspaceChoice[] } {
  ensureWorkspaceSchema(db);
  const now = Date.now();
  const rows = bootstrapRows(tree);
  const up = db.prepare(
    `INSERT INTO sync_workspace
       (workspace_id, workspace_name, workspace_slug, org_id, org_name, org_slug, is_default, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)
     ON CONFLICT(workspace_id) DO UPDATE SET
       workspace_name=excluded.workspace_name,
       workspace_slug=excluded.workspace_slug,
       org_id=excluded.org_id,
       org_name=excluded.org_name,
       org_slug=excluded.org_slug,
       is_default=excluded.is_default,
       updated_at=excluded.updated_at`,
  );
  for (const row of rows) {
    up.run(row.workspace_id, row.workspace_name, row.workspace_slug, row.org_id, row.org_name, row.org_slug, row.is_default, now);
  }

  const existing = selectedWorkspaceId(db);
  const choice = chooseWorkspace(tree, existing === LOCAL_WORKSPACE_ID ? null : existing);
  if (choice) {
    selectWorkspace(db, choice.workspace_id, choice.org_id);
    migrateLegacyLocalWorkspace(db, choice.workspace_id);
    return { selectedWorkspaceId: choice.workspace_id, workspaces: rows };
  }
  return { selectedWorkspaceId: LOCAL_WORKSPACE_ID, workspaces: rows };
}

export function selectWorkspace(db: DatabaseSync, workspaceId: string, orgId?: string | null) {
  ensureWorkspaceSchema(db);
  const row = db.prepare("SELECT org_id FROM sync_workspace WHERE workspace_id=?").get(workspaceId) as any;
  const resolvedOrg = orgId ?? row?.org_id ?? null;
  db.prepare(
    `INSERT INTO sync_workspace_selection(key, workspace_id, org_id, updated_at)
     VALUES ('active', ?, ?, ?)
     ON CONFLICT(key) DO UPDATE SET
     workspace_id=excluded.workspace_id,
       org_id=excluded.org_id,
       updated_at=excluded.updated_at`,
  ).run(workspaceId || LOCAL_WORKSPACE_ID, resolvedOrg, Date.now());
  migrateLegacyLocalWorkspace(db, workspaceId || LOCAL_WORKSPACE_ID);
}

export function scopedWebId(workspaceId: string, kind: string, id: string): string {
  return workspaceId && workspaceId !== LOCAL_WORKSPACE_ID
    ? `web:${workspaceId}:${kind}:${id}`
    : `web:${kind}:${id}`;
}

export function migrateLegacyLocalWorkspace(db: DatabaseSync, workspaceId: string) {
  if (!workspaceId || workspaceId === LOCAL_WORKSPACE_ID) return;
  ensureWorkspaceSchema(db);
  db.exec("PRAGMA defer_foreign_keys=ON; BEGIN;");
  try {
    // Preserve deterministic web ids after moving rows out of the legacy local partition.
    if (hasTable(db, "unified_message") && hasTable(db, "unified_conversation")) {
      db.prepare(
        `UPDATE unified_message
         SET conversation_id='web:' || ? || ':' || substr(conversation_id, 5)
         WHERE conversation_id IN (
           SELECT id FROM unified_conversation
           WHERE workspace_id='local' AND id = 'web:' || channel || ':' || external_id
         )`,
      ).run(workspaceId);
      db.prepare(
        `UPDATE unified_conversation
         SET id='web:' || ? || ':' || substr(id, 5)
         WHERE workspace_id='local' AND id = 'web:' || channel || ':' || external_id`,
      ).run(workspaceId);
    }
    for (const table of ["contact_identity", "contact_note", "deal", "unified_conversation"] as const) {
      if (!hasTable(db, table)) continue;
      const fk = table === "deal" || table === "unified_conversation" ? "contact_id" : "contact_id";
      db.prepare(
        `UPDATE ${table}
         SET ${fk}='web:' || ? || ':' || substr(${fk}, 5)
         WHERE workspace_id='local' AND ${fk} LIKE 'web:contact:%'`,
      ).run(workspaceId);
    }
    for (const table of ["contact", "company", "deal"] as const) {
      if (!hasTable(db, table)) continue;
      const kind = table === "contact" ? "contact" : table === "company" ? "company" : "deal";
      db.prepare(
        `UPDATE ${table}
         SET id='web:' || ? || ':' || substr(id, 5)
         WHERE workspace_id='local' AND id LIKE ?`,
      ).run(workspaceId, `web:${kind}:%`);
    }
    for (const table of WORKSPACE_TABLES) {
      if (!hasWorkspaceColumn(db, table)) continue;
      db.prepare(`UPDATE ${table} SET workspace_id=? WHERE workspace_id='local'`).run(workspaceId);
    }
    db.exec("COMMIT;");
  } catch (e) {
    db.exec("ROLLBACK;");
    throw e;
  }
}
