import type { DatabaseSync } from "node:sqlite";
import { selectedWorkspaceId } from "./sync/workspace.ts";

export function ensureCrmMutationSchema(db: DatabaseSync) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS crm_mutation_queue (
      id              TEXT PRIMARY KEY,
      workspace_id    TEXT NOT NULL,
      entity_type     TEXT NOT NULL,
      entity_id       TEXT NOT NULL,
      action          TEXT NOT NULL,
      origin          TEXT NOT NULL,
      payload_json    TEXT NOT NULL,
      idempotency_key TEXT NOT NULL,
      status          TEXT NOT NULL DEFAULT 'pending',
      attempts        INTEGER NOT NULL DEFAULT 0,
      error           TEXT,
      created_at      INTEGER NOT NULL,
      updated_at      INTEGER NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS ux_crm_mutation_pending_key
      ON crm_mutation_queue(idempotency_key) WHERE status='pending';
    CREATE INDEX IF NOT EXISTS idx_crm_mutation_status
      ON crm_mutation_queue(workspace_id, status, created_at);
  `);
}

export function recordCrmMutation(
  db: DatabaseSync,
  a: {
    entityType: "contact" | "company" | "deal" | string;
    entityId: string;
    action: "create" | "update" | string;
    payload: unknown;
    origin?: string;
    workspaceId?: string;
  },
) {
  ensureCrmMutationSchema(db);
  const workspaceId = a.workspaceId ?? selectedWorkspaceId(db);
  const now = Date.now();
  const origin = a.origin ?? "local";
  const key = `${workspaceId}:${a.entityType}:${a.entityId}:${a.action}:${origin}`;
  const id = `crm_mut:${now}:${Math.random().toString(36).slice(2, 8)}`;
  const payload = JSON.stringify(a.payload ?? {});
  db.prepare(
    `INSERT INTO crm_mutation_queue
       (id, workspace_id, entity_type, entity_id, action, origin, payload_json, idempotency_key, status, created_at, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?, ?)
     ON CONFLICT(idempotency_key) WHERE status='pending' DO UPDATE SET
       payload_json=excluded.payload_json,
       updated_at=excluded.updated_at,
       error=NULL`,
  ).run(id, workspaceId, a.entityType, a.entityId, a.action, origin, payload, key, now, now);
  return { ok: true, idempotency_key: key };
}

export function pendingCrmMutations(db: DatabaseSync, a?: { limit?: number }) {
  ensureCrmMutationSchema(db);
  const workspaceId = selectedWorkspaceId(db);
  const rows = db.prepare(
    `SELECT id, workspace_id, entity_type, entity_id, action, origin, payload_json,
            idempotency_key, status, attempts, error, created_at, updated_at
     FROM crm_mutation_queue
     WHERE workspace_id=? AND status='pending'
     ORDER BY created_at ASC LIMIT ?`,
  ).all(workspaceId, a?.limit ?? 100) as any[];
  return { ok: true, workspace_id: workspaceId, count: rows.length, mutations: rows };
}
