// Tests for the web→local inbox pull. Run: node --experimental-strip-types --test
import { test } from "node:test";
import assert from "node:assert/strict";
import { DatabaseSync } from "node:sqlite";
import { storeConversations, webConvId, type WebConversation } from "./pull.ts";

function freshDb(): DatabaseSync {
  const db = new DatabaseSync(":memory:");
  db.exec("PRAGMA foreign_keys=ON;");
  db.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);`);
  db.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));`);
  db.exec(`CREATE UNIQUE INDEX ux_msg_provider ON unified_message(provider_msg_id) WHERE provider_msg_id IS NOT NULL;`);
  db.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);`);
  return db;
}

const sample = (): WebConversation[] => [
  {
    externalId: "chat-abc", channel: "LINKEDIN", accountId: "int1", title: "Jane Doe",
    lastTs: 1000, unread: 2,
    messages: [
      { providerMsgId: "m1", direction: "IN", body: "hi", sender: "jane", ts: 900 },
      { providerMsgId: "m2", direction: "OUT", body: "hey", sender: "me", ts: 1000 },
    ],
  },
];

test("storeConversations upserts conversation + messages into unified tables", () => {
  const db = freshDb();
  const r = storeConversations(db, sample());
  assert.deepEqual(r, { conversations: 1, messages: 2 });

  const conv = db.prepare("SELECT * FROM unified_conversation WHERE channel='LINKEDIN'").get() as any;
  assert.equal(conv.id, webConvId("LINKEDIN", "chat-abc"));
  assert.equal(conv.title, "Jane Doe");
  assert.equal(conv.unread, 2);
  assert.equal(conv.workspace_id, "local");

  const msgs = db.prepare("SELECT * FROM unified_message ORDER BY ts").all() as any[];
  assert.equal(msgs.length, 2);
  assert.equal(msgs[0].body, "hi");
  assert.equal(msgs[0].source, "web");
  assert.equal(msgs[1].direction, "OUT");
  // messages link to the conversation (FK satisfied)
  assert.equal(msgs[0].conversation_id, conv.id);
});

test("re-pull is idempotent (updates, never duplicates) and advances unread/last_ts", () => {
  const db = freshDb();
  storeConversations(db, sample());
  // second pull: same ids, newer state
  const updated = sample();
  updated[0].unread = 5; updated[0].title = "Jane D.";
  updated[0].messages!.push({ providerMsgId: "m3", direction: "IN", body: "you there?", sender: "jane", ts: 1100 });
  storeConversations(db, updated);

  const convCount = (db.prepare("SELECT COUNT(*) c FROM unified_conversation").get() as any).c;
  const msgCount = (db.prepare("SELECT COUNT(*) c FROM unified_message").get() as any).c;
  assert.equal(convCount, 1, "no duplicate conversation");
  assert.equal(msgCount, 3, "two existing updated + one new");
  const conv = db.prepare("SELECT * FROM unified_conversation").get() as any;
  assert.equal(conv.unread, 5);
  assert.equal(conv.title, "Jane D.");
  assert.equal(conv.last_ts, 1000); // MAX(existing, incoming) — incoming lastTs unchanged at 1000
});

test("send-then-pull with the same providerMsgId keeps one OUT row and local source", () => {
  const db = freshDb();
  const convId = webConvId("LINKEDIN", "chat-abc");
  db.prepare(
    `INSERT INTO unified_conversation
       (id, workspace_id, channel, account_id, external_id, title, last_ts, unread, updated_at)
     VALUES (?, 'local', 'LINKEDIN', 'int1', 'chat-abc', 'Jane Doe', 1000, 0, 1000)`,
  ).run(convId);
  db.prepare(
    `INSERT INTO unified_message
       (id, workspace_id, conversation_id, direction, body, sender, ts, provider_msg_id, source, updated_at)
     VALUES ('local-send-row', 'local', ?, 'OUT', 'sent locally', 'me', 1000, 'provider-1', 'local', 1000)`,
  ).run(convId);

  storeConversations(db, [{
    externalId: "chat-abc", channel: "LINKEDIN", accountId: "int1", title: "Jane Doe",
    lastTs: 1100, unread: 0,
    messages: [{ providerMsgId: "provider-1", direction: "OUT", body: "sent locally", sender: "me", ts: 1100 }],
  }]);

  const rows = db.prepare("SELECT id, direction, source, provider_msg_id FROM unified_message").all() as any[];
  assert.equal(rows.length, 1);
  assert.equal(rows[0].id, "local-send-row");
  assert.equal(rows[0].direction, "OUT");
  assert.equal(rows[0].source, "local");
  assert.equal(rows[0].provider_msg_id, "provider-1");
});

test("storeConversations adds source on old local DBs and tags web pulls", () => {
  const db = new DatabaseSync(":memory:");
  db.exec("PRAGMA foreign_keys=ON;");
  db.exec(`CREATE TABLE contact (
    id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, display_name TEXT, updated_at INTEGER NOT NULL, deleted_at INTEGER);`);
  db.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);`);
  db.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, updated_at INTEGER NOT NULL, deleted_at INTEGER,
    FOREIGN KEY(conversation_id) REFERENCES unified_conversation(id));`);
  db.exec(`CREATE UNIQUE INDEX ux_msg_provider ON unified_message(provider_msg_id) WHERE provider_msg_id IS NOT NULL;`);

  storeConversations(db, sample());

  const cols = db.prepare("PRAGMA table_info(unified_message)").all() as any[];
  assert.equal(cols.some((c) => c.name === "source"), true);
  assert.equal((db.prepare("SELECT DISTINCT source FROM unified_message").get() as any).source, "web");
});

test("storeConversations accepts richer inbox fields for email/media/reactions/read receipts", () => {
  const db = freshDb();
  const r = storeConversations(db, [{
    externalId: "email-thread-1",
    channel: "EMAIL",
    accountId: "mail-int",
    title: "Ada <ada@example.com>",
    lastTs: 2000,
    unread: 0,
    messages: [{
      id: "email-msg-1",
      direction: "IN",
      subject: "Proposal",
      text: "See attached",
      from: { email: "ada@example.com", name: "Ada" },
      ts: 2000,
      attachments: [{ id: "att1", type: "document", filename: "proposal.pdf", url: "https://example.test/p.pdf" }],
      mediaType: "document",
      reactions: [{ type: "👍", actor_id: "me" }],
      readAt: 2001,
    }],
  }]);

  assert.deepEqual(r, { conversations: 1, messages: 1 });
  const conv = db.prepare("SELECT channel, workspace_id FROM unified_conversation").get() as any;
  assert.equal(conv.channel, "EMAIL");
  assert.equal(conv.workspace_id, "local");
  const msg = db.prepare("SELECT body, sender, attachments, media_type, reaction, reactions, status FROM unified_message").get() as any;
  assert.equal(msg.body, "See attached");
  assert.equal(msg.sender, "ada@example.com");
  assert.equal(JSON.parse(msg.attachments)[0].filename, "proposal.pdf");
  assert.equal(msg.media_type, "document");
  assert.equal(msg.reaction, "👍");
  assert.equal(JSON.parse(msg.reactions)[0].actor_id, "me");
  assert.equal(msg.status, 5);
});

test("storeConversations creates+links contact rows; repairs pre-existing orphans (LinkedIn-invisible bug 2026-06-12)", () => {
  const d = freshDb();
  // Pre-existing orphan written by an older engine (no contact)
  d.prepare(`INSERT INTO unified_conversation (id, workspace_id, channel, account_id, external_id, title, last_ts, unread, updated_at)
             VALUES ('web:ws:LINKEDIN:old1','local','LINKEDIN',NULL,'old1','Old Orphan',1,0,1)`).run();
  storeConversations(d, [
    { externalId: "x1", channel: "LINKEDIN", accountId: "a", title: "Dhruv Karkera", lastTs: 5, unread: 1, messages: [] },
    { externalId: "x2", channel: "LINKEDIN", accountId: "a", title: "", lastTs: 6, unread: 0, messages: [] },
  ] as any);
  const link = (id: string) => d.prepare("SELECT contact_id FROM unified_conversation WHERE external_id=?").get(id) as any;
  assert.equal(link("x1").contact_id, "ct:LINKEDIN:li:Dhruv Karkera");        // name-keyed, merges with local convention
  assert.equal(link("x2").contact_id, "ct:LINKEDIN:web:x2");                  // titleless falls back to external id
  assert.equal(link("old1").contact_id, "ct:LINKEDIN:li:Old Orphan");         // orphan repaired
  const ct = d.prepare("SELECT display_name FROM contact WHERE id='ct:LINKEDIN:li:Dhruv Karkera'").get() as any;
  assert.equal(ct.display_name, "Dhruv Karkera");
});
