import { test } from "node:test";
import assert from "node:assert/strict";
import { DatabaseSync } from "node:sqlite";
import { storeCrm } from "./crm.ts";

function db(): DatabaseSync {
  const d = new DatabaseSync(":memory:");
  d.exec("PRAGMA foreign_keys=ON;");
  d.exec(`CREATE TABLE contact (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, display_name TEXT, avatar_url TEXT, avatar_path TEXT, updated_at INTEGER NOT NULL, deleted_at INTEGER);`);
  d.exec(`CREATE TABLE company (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, name TEXT NOT NULL, industry TEXT, domain TEXT, updated_at INTEGER NOT NULL, deleted_at INTEGER);`);
  d.exec(`CREATE TABLE crm_deal (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, name TEXT NOT NULL, company TEXT, stage TEXT, value TEXT, close_date TEXT, updated_at INTEGER NOT NULL, deleted_at INTEGER);`);
  return d;
}

test("storeCrm upserts contacts/companies/deals into the live CRM schema", () => {
  const d = db();
  const r = storeCrm(d, {
    contacts: [{ id: "c1", name: "Ada" }],
    companies: [{ id: "co1", name: "Engines Ltd", domain: "engines.io" }],
    deals: [
      { id: "d1", title: "Expansion", stage: "OPEN", amount: 5000, contactId: "c1" }, // linkable
      { id: "d2", title: "Ghost", stage: "NEW", contactId: "missing" },               // contact not pulled
    ],
  });
  assert.deepEqual(r, { contacts: 1, companies: 1, deals: 2 });
  assert.equal((d.prepare("SELECT display_name FROM contact WHERE id='web:contact:c1'").get() as any).display_name, "Ada");
  const deal = d.prepare("SELECT name, value FROM crm_deal WHERE id='web:deal:d1'").get() as any;
  assert.equal(deal.name, "Expansion");
  assert.equal(deal.value, "5000");
  // linked deal points at the pulled contact; unlinkable deal stored with NULL contact (no FK violation)
  assert.equal((d.prepare("SELECT contact_id FROM crm_deal WHERE id='web:deal:d1'").get() as any).contact_id, "web:contact:c1");
  assert.equal((d.prepare("SELECT contact_id FROM crm_deal WHERE id='web:deal:d2'").get() as any).contact_id, null);
});

test("storeCrm adds crm_deal.contact_id on older live DBs", () => {
  const d = db();

  storeCrm(d, { deals: [{ id: "d1", title: "Migration Deal", stage: "OPEN" }] });

  const cols = d.prepare("PRAGMA table_info(crm_deal)").all() as any[];
  assert.equal(cols.some((c) => c.name === "contact_id"), true);
  assert.equal((d.prepare("SELECT name FROM crm_deal WHERE id='web:deal:d1'").get() as any).name, "Migration Deal");
});

test("storeCrm uses untitled fallback and preserves local company/close_date on re-pull", () => {
  const d = db();
  d.prepare(`INSERT INTO crm_deal (id, workspace_id, name, company, stage, value, close_date, updated_at)
    VALUES ('web:deal:d1', 'local', 'Local Name', 'Local Co', 'OLD', '1', '2026-12-31', 1)`).run();

  storeCrm(d, { deals: [{ id: "d1", stage: "OPEN", value: 12 }] });

  const deal = { ...(d.prepare("SELECT name, company, stage, value, close_date FROM crm_deal WHERE id='web:deal:d1'").get() as any) };
  assert.deepEqual(deal, {
    name: "(untitled deal)",
    company: "Local Co",
    stage: "OPEN",
    value: "12",
    close_date: "2026-12-31",
  });
});

test("idempotent re-pull updates, never duplicates", () => {
  const d = db();
  storeCrm(d, { contacts: [{ id: "c1", name: "Ada" }] });
  storeCrm(d, { contacts: [{ id: "c1", name: "Ada Lovelace" }] });
  assert.equal((d.prepare("SELECT COUNT(*) c FROM contact").get() as any).c, 1);
  assert.equal((d.prepare("SELECT display_name FROM contact WHERE id='web:contact:c1'").get() as any).display_name, "Ada Lovelace");
});

test("storeCrm binds live web payload shapes: stage as object, int value, boolean-ish noise", () => {
  const d = db();
  // Live /api/crm/companion/deals shape (2026-06-12): stage is {id,name,...}, value is int.
  storeCrm(d, {
    deals: [
      { id: "d9", title: "Real Web Deal", stage: { name: "Negotiation" } as any, value: 1500, contactId: "c-missing" },
      { id: "d10", title: "No stage name", stage: {} as any, status: "OPEN" },
    ],
  });
  const rows = d.prepare("SELECT name, stage, value, contact_id FROM crm_deal").all() as any[];
  assert.equal(rows.length, 2);
  const real = rows.find((r) => r.name === "Real Web Deal");
  const noName = rows.find((r) => r.name === "No stage name");
  assert.equal(real.stage, "Negotiation");
  assert.equal(real.value, "1500");
  assert.equal(real.contact_id, null);  // contact not pulled -> no dangling link
  assert.equal(noName.stage, "OPEN");   // falls back to status when stage.name missing
});
