// Cross-channel identity CORE — suggest / merge / unmerge people across channels, from the terminal.
// Mirrors the Swift LocalDB merge logic (mergeContacts/unmergeContacts/suggestedMerges) on the SAME
// dewxlocal.db tables (contact, contact_identity, contact_merge, unified_conversation). Reversible.
// Pure: each fn takes a writable DatabaseSync + args. No auto-merge — the terminal proposes, a human
// (or an explicit tool call) confirms; every merge is logged and undoable.

import type { DatabaseSync } from "node:sqlite";
import { selectedWorkspaceId } from "./sync/workspace.ts";
const now = () => Date.now();

/// Email/phone signals derivable from a channel handle — mirrors LocalDB.matchSignals.
function matchSignals(channel: string, handle: string): { email: string | null; phone: string | null } {
  const h = (handle || "").trim();
  switch (channel.toUpperCase()) {
    case "EMAIL": case "GMAIL":
      return { email: h.includes("@") ? h.toLowerCase() : null, phone: null };
    case "WHATSAPP": {
      // <digits>@s.whatsapp.net = a real phone; @lid / @g.us carry no dialable number.
      if (h.endsWith("@s.whatsapp.net")) {
        const digits = h.replace("@s.whatsapp.net", "").replace(/\D/g, "");
        return { email: null, phone: digits || null };
      }
      return { email: null, phone: null };
    }
    default: // TELEGRAM ids are numeric but NOT phones; everything else carries no signal.
      return { email: null, phone: null };
  }
}

/// Pretty display name — mirrors WAName.pretty (handles raw jids → "+digits" / "WhatsApp user" / etc.).
function pretty(raw: string): string {
  const t = (raw || "").trim();
  if (!t || t === "Unknown") return "Unknown";
  const at = t.indexOf("@");
  if (at < 0) return t; // already a real name
  const id = t.slice(0, at), suffix = t.slice(at + 1);
  const last4 = id.slice(-4);
  const tail = last4 ? ` ··${last4}` : "";
  switch (suffix) {
    case "newsletter": return `Channel${tail}`;
    case "broadcast": return `Broadcast${tail}`;
    case "g.us": return `Group chat${tail}`;
    case "lid": return `WhatsApp user${tail}`;
    case "s.whatsapp.net": case "c.us":
      return /^\d+$/.test(id) ? `+${id}` : t;
    default: return t;
  }
}

/// Follow active merges (merged → primary) so a raw contact id resolves to its surviving person.
export function effectiveContactId(db: DatabaseSync, id: string): string {
  let cur = id;
  for (let i = 0; i < 10; i++) {
    const r = db.prepare(`SELECT primary_contact_id AS p FROM contact_merge WHERE merged_contact_id=? AND undone_at IS NULL LIMIT 1`).get(cur) as any;
    if (!r?.p) break;
    cur = r.p;
  }
  return cur;
}

/// Accept either a contact id OR a chat/conversation id; resolve to the surviving contact id.
function resolveContact(db: DatabaseSync, idOrChat: string): string | null {
  const conv = db.prepare(`SELECT contact_id AS c FROM unified_conversation WHERE id=? AND deleted_at IS NULL`).get(idOrChat) as any;
  const raw = conv?.c ?? idOrChat;
  const exists = db.prepare(`SELECT 1 FROM contact WHERE id=?`).get(raw);
  return exists ? effectiveContactId(db, raw) : null;
}

/// Merge `merged` into `primary`: repoint the merged contact's live conversations onto primary (so the
/// inbox shows one unified person) and record it reversibly. Returns {merge_id, moved}. Args may be
/// contact ids OR chat ids (a chat id resolves to its contact). Manual-confirm — never called by ingest.
export function mergeContacts(db: DatabaseSync, a: { primary: string; merged: string; signal?: string }) {
  const ws = selectedWorkspaceId(db);
  const primary = resolveContact(db, a.primary);
  const merged = resolveContact(db, a.merged);
  if (!primary || !merged) return { ok: false, error: "unknown contact/chat id", primary, merged };
  if (primary === merged) return { ok: false, error: "same person (already merged)" };
  const convs = db.prepare(`SELECT id FROM unified_conversation WHERE contact_id=? AND deleted_at IS NULL`).all(merged) as any[];
  const convIds = convs.map((c) => c.id);
  if (convIds.length === 0) return { ok: false, error: "merged contact has no live conversations" };
  for (const cid of convIds) db.prepare(`UPDATE unified_conversation SET contact_id=? WHERE id=?`).run(primary, cid);
  const mergeId = `mg:${now()}:${Math.floor(1000 + Math.random() * 9000)}`;
  db.prepare(`INSERT INTO contact_merge(id, workspace_id, primary_contact_id, merged_contact_id, signal, conv_ids_json, created_at)
              VALUES(?,?,?,?,?,?,?)`)
    .run(mergeId, ws, primary, merged, a.signal || "manual", JSON.stringify(convIds), now());
  return { ok: true, merge_id: mergeId, primary, primary_name: displayName(db, primary), merged, merged_name: displayName(db, merged), moved: convIds.length };
}

/// Reverse a merge: put the moved conversations back on the merged contact (idempotent on undone_at).
export function unmergeContacts(db: DatabaseSync, a: { merge_id: string }) {
  const row = db.prepare(`SELECT merged_contact_id AS m, conv_ids_json AS j FROM contact_merge WHERE id=? AND undone_at IS NULL`).get(a.merge_id) as any;
  if (!row) return { ok: false, error: "no active merge with that id" };
  let convIds: string[] = [];
  try { convIds = JSON.parse(row.j || "[]"); } catch { convIds = []; }
  for (const cid of convIds) db.prepare(`UPDATE unified_conversation SET contact_id=? WHERE id=?`).run(row.m, cid);
  db.prepare(`UPDATE contact_merge SET undone_at=? WHERE id=?`).run(now(), a.merge_id);
  return { ok: true, restored: convIds.length, to_contact: row.m, to_name: displayName(db, row.m) };
}

/// Active (not-undone) merges where `primary` is the survivor — powers an Unmerge affordance.
export function activeMerges(db: DatabaseSync, a: { primary: string }) {
  const p = resolveContact(db, a.primary) ?? a.primary;
  const rows = db.prepare(`SELECT id, merged_contact_id, signal, created_at FROM contact_merge
                           WHERE primary_contact_id=? AND undone_at IS NULL ORDER BY created_at DESC`).all(p) as any[];
  return rows.map((r) => ({ merge_id: r.id, merged: r.merged_contact_id, merged_name: displayName(db, r.merged_contact_id), signal: r.signal, created_at: r.created_at }));
}

/// Pretty name for a contact id (merge/unmerge labels).
function displayName(db: DatabaseSync, contactId: string): string {
  const r = db.prepare(`SELECT COALESCE(display_name,'') AS n FROM contact WHERE id=?`).get(contactId) as any;
  return pretty(r?.n ?? "");
}

/// Explicitly link a channel handle to a person (contact). Idempotent; enriches email/phone signals
/// so future suggest_merges can find this person by a shared phone/email. Mirrors LocalDB.linkIdentity.
export function linkIdentity(db: DatabaseSync, a: { contact: string; channel: string; handle: string }) {
  const ws = selectedWorkspaceId(db);
  const contact = resolveContact(db, a.contact);
  if (!contact) return { ok: false, error: "unknown contact/chat id" };
  const channel = a.channel.toUpperCase();
  const { email, phone } = matchSignals(channel, a.handle);
  db.prepare(`INSERT INTO contact_identity(id, workspace_id, contact_id, channel, handle, email, phone, updated_at)
              VALUES(?,?,?,?,?,?,?,?)
              ON CONFLICT(id) DO UPDATE SET contact_id=excluded.contact_id,
                email=COALESCE(excluded.email, contact_identity.email),
                phone=COALESCE(excluded.phone, contact_identity.phone), updated_at=excluded.updated_at`)
    .run(`id:${channel}:${a.handle}`, ws, contact, channel, a.handle, email, phone, now());
  return { ok: true, contact, contact_name: displayName(db, contact), channel, handle: a.handle, email, phone };
}

/// All channel identities for a person (handle + channel + email/phone), following active merges and
/// including any contacts merged into them. Mirrors LocalDB.identitiesFor. Pure read.
export function identitiesFor(db: DatabaseSync, a: { contact: string }) {
  const eff = resolveContact(db, a.contact);
  if (!eff) return { ok: false, error: "unknown contact/chat id" };
  const ids = [eff];
  const merged = db.prepare(`SELECT merged_contact_id AS m FROM contact_merge WHERE primary_contact_id=? AND undone_at IS NULL`).all(eff) as any[];
  for (const r of merged) ids.push(r.m);
  const out: any[] = [];
  for (const cid of ids) {
    const rows = db.prepare(`SELECT channel, handle, email, phone FROM contact_identity WHERE contact_id=? AND deleted_at IS NULL`).all(cid) as any[];
    for (const r of rows) out.push({ channel: r.channel, handle: r.handle, email: r.email, phone: r.phone, via: cid === eff ? "primary" : "merged" });
  }
  return { ok: true, contact: eff, name: displayName(db, eff), merged_in: ids.length - 1, identities: out };
}

/// Candidate same-person pairs across channels — NEVER auto-applies. Strong: exact shared email or
/// normalized phone. Weak: identical normalized display name (flagged 'name'). Only between people who
/// each have a live conversation (visible in the inbox), de-duped through active merges.
export function suggestedMerges(db: DatabaseSync, a?: { limit?: number }) {
  const ws = selectedWorkspaceId(db);
  const limit = a?.limit ?? 50;
  // Names + visibility (a contact is "visible" if it has at least one live conversation).
  const name = new Map<string, string>();
  const visible = new Set<string>();
  const people = db.prepare(`SELECT ct.id AS id, COALESCE(ct.display_name,'') AS dn,
      (SELECT COUNT(*) FROM unified_conversation c WHERE c.contact_id=ct.id AND c.deleted_at IS NULL) AS nconv
    FROM contact ct WHERE ct.workspace_id=? AND ct.deleted_at IS NULL`).all(ws) as any[];
  for (const p of people) { name.set(p.id, pretty(p.dn)); if (p.nconv > 0) visible.add(p.id); }

  const seen = new Set<string>();
  const out: any[] = [];
  const addPair = (a0: string, b0: string, signal: string) => {
    const a1 = effectiveContactId(db, a0), b1 = effectiveContactId(db, b0);
    if (a1 === b1 || !visible.has(a1) || !visible.has(b1)) return;
    const key = [a1, b1].sort().join("|");
    if (seen.has(key)) return;
    seen.add(key);
    const [lo, hi] = a1 < b1 ? [a1, b1] : [b1, a1];
    out.push({ a_id: lo, a_name: name.get(lo) ?? lo, b_id: hi, b_name: name.get(hi) ?? hi, signal });
  };

  // Strong: shared email / phone (self-join on the identity signals).
  for (const [colname, sig] of [["email", "email"], ["phone", "phone"]] as const) {
    const rows = db.prepare(`SELECT a.contact_id AS a, b.contact_id AS b FROM contact_identity a
        JOIN contact_identity b ON a.${colname}=b.${colname} AND a.contact_id<b.contact_id
        WHERE a.workspace_id=? AND a.${colname} IS NOT NULL AND a.${colname}!='' AND a.deleted_at IS NULL AND b.deleted_at IS NULL`).all(ws) as any[];
    for (const r of rows) addPair(r.a, r.b, sig);
  }

  // Weak: identical normalized display name (letters only, ≥2 chars, not a placeholder).
  const byName = new Map<string, string[]>();
  for (const [id, nm] of name) {
    if (!visible.has(id)) continue;
    const norm = nm.toLowerCase().replace(/[^a-z]/g, "");
    const low = nm.toLowerCase();
    if (norm.length < 2 || low.startsWith("whatsapp user") || low.startsWith("group") || low.startsWith("unknown") || low.startsWith("+")) continue;
    (byName.get(norm) ?? byName.set(norm, []).get(norm)!).push(id);
  }
  for (const ids of byName.values()) {
    if (ids.length < 2) continue;
    for (let i = 0; i < ids.length; i++) for (let j = i + 1; j < ids.length; j++) addPair(ids[i], ids[j], "name");
  }

  const strong = out.filter((c) => c.signal !== "name").length;
  return { count: out.length, strong, weak: out.length - strong,
    note: "Strong (email/phone) = same person with high confidence. Weak (name) = review before merging. Nothing is auto-merged.",
    candidates: out.slice(0, limit) };
}
