import { test } from "node:test";
import assert from "node:assert/strict";
import { DatabaseSync } from "node:sqlite";
import { storeCrm } from "./crm.ts";
import { storeConversations } from "./pull.ts";
import { migrateLegacyLocalWorkspace, selectWorkspace, storeBootstrapWorkspaces } from "./workspace.ts";

function db(): DatabaseSync {
  const d = new DatabaseSync(":memory:");
  d.exec("PRAGMA foreign_keys=ON;");
  d.exec(`CREATE TABLE unified_conversation (
    id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, channel TEXT NOT NULL, account_id TEXT,
    external_id TEXT, title TEXT, contact_id TEXT, last_ts INTEGER, unread INTEGER NOT NULL DEFAULT 0,
    archived_at INTEGER, muted INTEGER NOT NULL DEFAULT 0, updated_at INTEGER NOT NULL, deleted_at INTEGER);`);
  d.exec(`CREATE TABLE unified_message (
    id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, conversation_id TEXT NOT NULL, direction TEXT NOT NULL,
    body TEXT, sender TEXT, ts INTEGER NOT NULL, provider_msg_id TEXT, attachments TEXT, media_type TEXT,
    media_path TEXT, reaction TEXT, source TEXT, updated_at INTEGER NOT NULL, deleted_at INTEGER,
    FOREIGN KEY(conversation_id) REFERENCES unified_conversation(id));`);
  d.exec(`CREATE UNIQUE INDEX ux_msg_provider ON unified_message(provider_msg_id) WHERE provider_msg_id IS NOT NULL;`);
  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 contact_identity (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, contact_id TEXT NOT NULL, channel TEXT NOT NULL, handle TEXT NOT NULL, email TEXT, phone TEXT, updated_at INTEGER NOT NULL, deleted_at INTEGER, FOREIGN KEY(contact_id) REFERENCES contact(id));`);
  d.exec(`CREATE TABLE contact_note (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, contact_id TEXT NOT NULL, body TEXT NOT NULL, ts INTEGER NOT NULL, updated_at INTEGER NOT NULL, deleted_at INTEGER, FOREIGN KEY(contact_id) REFERENCES contact(id));`);
  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 pipeline (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, name TEXT, updated_at INTEGER, deleted_at INTEGER);`);
  d.exec(`CREATE TABLE deal (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, contact_id TEXT, pipeline_id TEXT, title TEXT, stage TEXT, value REAL, updated_at INTEGER NOT NULL, deleted_at INTEGER, FOREIGN KEY(contact_id) REFERENCES contact(id), FOREIGN KEY(pipeline_id) REFERENCES pipeline(id));`);
  d.exec(`CREATE TABLE contact_merge (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, primary_contact_id TEXT NOT NULL, merged_contact_id TEXT NOT NULL, signal TEXT, conv_ids_json TEXT NOT NULL, created_at INTEGER NOT NULL, undone_at INTEGER);`);
  d.exec(`CREATE TABLE contact_tag (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, name TEXT, color TEXT, updated_at INTEGER, deleted_at INTEGER);`);
  d.exec(`CREATE TABLE crm_deal (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, name TEXT, company TEXT, stage TEXT, value TEXT, close_date TEXT, updated_at INTEGER, deleted_at INTEGER);`);
  d.exec(`CREATE TABLE crm_task (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, title TEXT, due TEXT, priority TEXT, status TEXT, updated_at INTEGER, deleted_at INTEGER);`);
  d.exec(`CREATE TABLE calendar_event (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, title TEXT, start_ms INTEGER, end_ms INTEGER, updated_at INTEGER);`);
  d.exec(`CREATE TABLE outbound_draft (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, conversation_id TEXT, channel TEXT, account_id TEXT, external_id TEXT, title TEXT, body TEXT, status TEXT, source TEXT, created_at INTEGER, updated_at INTEGER);`);
  d.exec(`CREATE TABLE draft_metadata (id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, created_at INTEGER);`);
  d.exec(`CREATE TABLE audio_transcript (message_id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, transcript TEXT, created_at INTEGER, updated_at INTEGER);`);
  return d;
}

test("bootstrap stores workspace tree and selects the active default workspace", () => {
  const d = db();
  const state = storeBootstrapWorkspaces(d, {
    activeOrganizationId: "org2",
    organizations: [
      { id: "org1", name: "Dewx", slug: "dewx", role: "OWNER", integrations: [], workspaces: [{ id: "ws1", name: "Main", slug: "main", isDefault: true, status: "ACTIVE", integrations: [] }] },
      { id: "org2", name: "PE", slug: "pe", role: "OWNER", integrations: [], workspaces: [{ id: "ws2", name: "Sales", slug: "sales", isDefault: true, status: "ACTIVE", integrations: [] }] },
    ],
  });

  assert.equal(state.selectedWorkspaceId, "ws2");
  assert.equal((d.prepare("SELECT COUNT(*) c FROM sync_workspace").get() as any).c, 2);
  assert.equal((d.prepare("SELECT workspace_id FROM sync_workspace_selection WHERE key='active'").get() as any).workspace_id, "ws2");
});

test("web inbox and CRM storage use the selected workspace and scoped ids", () => {
  const d = db();
  selectWorkspace(d, "ws1", "org1");

  storeConversations(d, [{
    externalId: "chat-a",
    channel: "LINKEDIN",
    accountId: "int1",
    title: "Ada",
    lastTs: 1000,
    unread: 0,
    messages: [{ providerMsgId: "m1", direction: "IN", body: "hi", sender: "ada", ts: 1000 }],
  }]);
  storeCrm(d, {
    contacts: [{ id: "c1", name: "Ada" }],
    companies: [{ id: "co1", name: "Engines" }],
    deals: [{ id: "d1", title: "Pilot", contactId: "c1" }],
  });

  assert.equal((d.prepare("SELECT id FROM unified_conversation").get() as any).id, "web:ws1:LINKEDIN:chat-a");
  assert.equal((d.prepare("SELECT workspace_id FROM unified_message").get() as any).workspace_id, "ws1");
  assert.equal((d.prepare("SELECT id FROM contact").get() as any).id, "web:ws1:contact:c1");
  assert.equal((d.prepare("SELECT contact_id FROM crm_deal").get() as any).contact_id, "web:ws1:contact:c1");
});

test("legacy local web rows migrate idempotently into a selected workspace", () => {
  const d = db();
  d.prepare(`INSERT INTO contact(id, workspace_id, display_name, updated_at) VALUES ('web:contact:c1', 'local', 'Ada', 1)`).run();
  d.prepare(`INSERT INTO deal(id, workspace_id, contact_id, title, updated_at) VALUES ('web:deal:d1', 'local', 'web:contact:c1', 'Pilot', 1)`).run();
  d.prepare(`INSERT INTO unified_conversation(id, workspace_id, channel, account_id, external_id, title, contact_id, last_ts, unread, updated_at)
             VALUES ('web:LINKEDIN:chat-a', 'local', 'LINKEDIN', 'int1', 'chat-a', 'Ada', 'web:contact:c1', 1, 0, 1)`).run();
  d.prepare(`INSERT INTO unified_message(id, workspace_id, conversation_id, direction, body, ts, provider_msg_id, updated_at)
             VALUES ('web:LINKEDIN:chat-a:m1', 'local', 'web:LINKEDIN:chat-a', 'IN', 'hi', 1, 'm1', 1)`).run();

  migrateLegacyLocalWorkspace(d, "ws1");
  migrateLegacyLocalWorkspace(d, "ws1");

  assert.equal((d.prepare("SELECT id, workspace_id FROM contact").get() as any).id, "web:ws1:contact:c1");
  assert.equal((d.prepare("SELECT id, workspace_id FROM unified_conversation").get() as any).id, "web:ws1:LINKEDIN:chat-a");
  assert.equal((d.prepare("SELECT conversation_id, workspace_id FROM unified_message").get() as any).conversation_id, "web:ws1:LINKEDIN:chat-a");
  assert.equal((d.prepare("SELECT contact_id FROM deal").get() as any).contact_id, "web:ws1:contact:c1");
  assert.equal((d.prepare("SELECT COUNT(*) c FROM unified_conversation WHERE workspace_id='local'").get() as any).c, 0);
});
