// Web→local inbox pull CORE (Phase 2). Writes web-sourced conversations + messages into the SAME
// unified tables the local inbox reads (unified_conversation / unified_message), so web LinkedIn (etc.)
// threads appear alongside local WhatsApp. Idempotent: deterministic ids → re-pulls update, never dupe.
// FK-safe: the conversation row is upserted before its messages (foreign_keys=ON is enforced).

import type { DatabaseSync } from "node:sqlite";
import { processInChatApprovalCommand } from "../inchatApprovals.ts";
import { LOCAL_WORKSPACE_ID, scopedWebId, selectedWorkspaceId } from "./workspace.ts";

export interface WebMessage {
  providerMsgId?: string | null;
  id?: string | null;
  externalId?: string | null;
  direction: "IN" | "OUT";
  body?: string | null;
  text?: string | null;
  html?: string | null;
  snippet?: string | null;
  subject?: string | null;
  sender?: string | null;
  from?: string | { email?: string | null; name?: string | null } | null;
  ts: number; // epoch ms
  source?: "web" | "local" | null;
  attachments?: unknown;
  media?: unknown;
  mediaType?: string | null;
  mediaPath?: string | null;
  reactions?: unknown;
  reaction?: string | null;
  status?: string | number | null;
  deliveryStatus?: string | number | null;
  readAt?: string | number | null;
  readReceipt?: unknown;
  readReceipts?: unknown;
}
export interface WebConversation {
  externalId: string; // Unipile chatId
  channel: string; // LINKEDIN | ...
  accountId: string | null; // the integration id it belongs to
  title: string | null;
  lastTs: number; // epoch ms
  unread: number;
  messages?: WebMessage[];
}

/// Deterministic ids so a re-pull updates the existing row instead of duplicating it.
export const webConvId = (channel: string, externalId: string, workspaceId = LOCAL_WORKSPACE_ID) =>
  scopedWebId(workspaceId, channel.toUpperCase(), externalId);
const webMsgId = (convId: string, providerMsgId: string) => `${convId}:${providerMsgId}`;

function ensureColumn(db: DatabaseSync, table: string, column: string, definition: string) {
  const cols = db.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string }>;
  if (!cols.some((c) => c.name === column)) db.exec(`ALTER TABLE ${table} ADD COLUMN ${definition};`);
}

function jsonOrNull(value: unknown): string | null {
  if (value == null) return null;
  if (typeof value === "string") return value.trim() ? value : null;
  try { return JSON.stringify(value); } catch { return null; }
}

function asRecord(value: unknown): Record<string, any> | null {
  return value && typeof value === "object" && !Array.isArray(value) ? value as Record<string, any> : null;
}

function firstRecord(value: unknown): Record<string, any> | null {
  if (Array.isArray(value)) return asRecord(value[0]);
  return asRecord(value);
}

function providerMsgId(m: WebMessage): string {
  return String(m.providerMsgId || m.id || m.externalId || `webmsg:${Number(m.ts) || Date.now()}:${Math.random().toString(36).slice(2, 8)}`);
}

function messageBody(m: WebMessage): string {
  return String(m.body ?? m.text ?? m.snippet ?? m.subject ?? m.html ?? "");
}

function messageSender(m: WebMessage): string | null {
  if (typeof m.sender === "string" && m.sender) return m.sender;
  if (typeof m.from === "string" && m.from) return m.from;
  const from = asRecord(m.from);
  return from ? (from.email ?? from.name ?? null) : null;
}

function normalizedAttachments(m: WebMessage): string | null {
  if (m.attachments != null) return jsonOrNull(m.attachments);
  if (m.media != null) return jsonOrNull(Array.isArray(m.media) ? m.media : [m.media]);
  return null;
}

function mediaTypeFor(m: WebMessage): string | null {
  if (m.mediaType) return m.mediaType;
  const media = firstRecord(m.media) ?? firstRecord(m.attachments);
  return media ? (media.mediaType ?? media.type ?? media.kind ?? media.contentType ?? media.mimeType ?? null) : null;
}

function mediaPathFor(m: WebMessage): string | null {
  if (m.mediaPath) return m.mediaPath;
  const media = firstRecord(m.media) ?? firstRecord(m.attachments);
  return media ? (media.localPath ?? media.path ?? media.filePath ?? null) : null;
}

function reactionsJson(m: WebMessage): string | null {
  if (m.reactions != null) return jsonOrNull(m.reactions);
  if (m.reaction) return jsonOrNull([{ type: m.reaction }]);
  return null;
}

function firstReaction(m: WebMessage): string | null {
  if (m.reaction) return m.reaction;
  const first = firstRecord(m.reactions);
  return first ? (first.type ?? first.emoji ?? first.reaction ?? null) : null;
}

function receiptStatus(m: WebMessage): number | null {
  if (m.readAt != null || m.readReceipt != null || m.readReceipts != null) return 5;
  const raw = m.deliveryStatus ?? m.status;
  if (typeof raw === "number") return raw;
  const s = String(raw ?? "").toLowerCase();
  if (!s) return null;
  if (["read", "seen", "opened"].includes(s)) return 5;
  if (["delivered"].includes(s)) return 3;
  if (["sent", "sending", "queued"].includes(s)) return 2;
  if (["failed", "error", "undelivered"].includes(s)) return -1;
  return null;
}

/// Upsert web conversations (+ their messages) into the unified inbox tables. Pure storage — the caller
/// supplies the data (from webClient). Returns counts. Assumes the unified_* tables already exist
/// (created by the app's DB init) and the connection has foreign_keys=ON.
export function storeConversations(
  db: DatabaseSync,
  convs: WebConversation[],
  opts: { workspaceId?: string } = {},
): { conversations: number; messages: number } {
  const now = Date.now();
  const ws = opts.workspaceId ?? selectedWorkspaceId(db);
  let messages = 0;
  ensureColumn(db, "unified_message", "source", "source TEXT");
  ensureColumn(db, "unified_message", "attachments", "attachments TEXT");
  ensureColumn(db, "unified_message", "media_type", "media_type TEXT");
  ensureColumn(db, "unified_message", "media_path", "media_path TEXT");
  ensureColumn(db, "unified_message", "reaction", "reaction TEXT");
  ensureColumn(db, "unified_message", "reactions", "reactions TEXT");
  ensureColumn(db, "unified_message", "status", "status INTEGER");
  db.exec(`CREATE UNIQUE INDEX IF NOT EXISTS ux_msg_provider ON unified_message(provider_msg_id)
    WHERE provider_msg_id IS NOT NULL;`);

  const upConv = db.prepare(
    `INSERT INTO unified_conversation
       (id, workspace_id, channel, account_id, external_id, title, last_ts, unread, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?)
     ON CONFLICT(id) DO UPDATE SET
       title=excluded.title,
       last_ts=MAX(unified_conversation.last_ts, excluded.last_ts),
       unread=excluded.unread,
       account_id=excluded.account_id,
       updated_at=excluded.updated_at,
       deleted_at=NULL`,
  );
  const upMsg = db.prepare(
    `INSERT INTO unified_message
       (id, workspace_id, conversation_id, direction, body, sender, ts, provider_msg_id, source,
        attachments, media_type, media_path, reaction, reactions, status, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
     ON CONFLICT(provider_msg_id) WHERE provider_msg_id IS NOT NULL DO UPDATE SET
       body=excluded.body,
       direction=CASE WHEN unified_message.source='local' THEN unified_message.direction ELSE excluded.direction END,
       sender=excluded.sender, ts=excluded.ts,
       source=COALESCE(unified_message.source, excluded.source),
       attachments=COALESCE(excluded.attachments, unified_message.attachments),
       media_type=COALESCE(excluded.media_type, unified_message.media_type),
       media_path=COALESCE(excluded.media_path, unified_message.media_path),
       reaction=COALESCE(excluded.reaction, unified_message.reaction),
       reactions=COALESCE(excluded.reactions, unified_message.reactions),
       status=MAX(COALESCE(unified_message.status,0), COALESCE(excluded.status,0)),
       updated_at=excluded.updated_at, deleted_at=NULL`,
  );
  const upMsgById = db.prepare(
    `INSERT INTO unified_message
       (id, workspace_id, conversation_id, direction, body, sender, ts, provider_msg_id, source,
        attachments, media_type, media_path, reaction, reactions, status, updated_at)
     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
     ON CONFLICT(id) DO UPDATE SET
       body=excluded.body, direction=excluded.direction, sender=excluded.sender,
       ts=excluded.ts, provider_msg_id=excluded.provider_msg_id,
       source=COALESCE(unified_message.source, excluded.source),
       attachments=COALESCE(excluded.attachments, unified_message.attachments),
       media_type=COALESCE(excluded.media_type, unified_message.media_type),
       media_path=COALESCE(excluded.media_path, unified_message.media_path),
       reaction=COALESCE(excluded.reaction, unified_message.reaction),
       reactions=COALESCE(excluded.reactions, unified_message.reactions),
       status=MAX(COALESCE(unified_message.status,0), COALESCE(excluded.status,0)),
       updated_at=excluded.updated_at, deleted_at=NULL`,
  );

  // The inbox UI is contact-centric: a conversation without a valid contact row is INVISIBLE
  // (caught live 2026-06-12 — 101 of 111 web-pulled LinkedIn threads had no contact). Reuse the
  // local LinkedIn convention (ct:<CHANNEL>:li:<name>) so a web-pulled thread MERGES with the same
  // person's locally-scraped thread instead of duplicating it.
  // IF NOT EXISTS: production DBs already have the app-created contact table (no-op there).
  db.exec(`CREATE TABLE IF NOT EXISTS 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);`);
  const upContact = db.prepare(
    `INSERT INTO contact (id, workspace_id, display_name, updated_at) VALUES (?,?,?,?)
     ON CONFLICT(id) DO UPDATE SET
       display_name=COALESCE(contact.display_name, excluded.display_name),
       updated_at=excluded.updated_at, deleted_at=NULL`,
  );
  const linkConv = db.prepare(`UPDATE unified_conversation SET contact_id=? WHERE id=?;`);
  const contactIdFor = (channel: string, title: string | null | undefined, externalId: string): string => {
    const name = (title ?? "").trim();
    return name ? `ct:${channel.toUpperCase()}:li:${name}` : `ct:${channel.toUpperCase()}:web:${externalId}`;
  };

  for (const c of convs) {
    const cId = webConvId(c.channel, c.externalId, ws);
    upConv.run(
      cId, ws, c.channel.toUpperCase(), c.accountId ?? null, c.externalId,
      c.title ?? null, Number(c.lastTs) || 0, Number(c.unread) || 0, now,
    );
    const contactId = contactIdFor(c.channel, c.title, c.externalId);
    upContact.run(contactId, ws, (c.title ?? "").trim() || null, now);
    linkConv.run(contactId, cId);
    for (const m of c.messages ?? []) {
      const pId = providerMsgId(m);
      const insertId = webMsgId(cId, pId);
      const source = m.source ?? "web";
      const stmt = pId ? upMsg : upMsgById;
      stmt.run(
        insertId, ws, cId,
        m.direction === "OUT" ? "OUT" : "IN", messageBody(m), messageSender(m),
        Number(m.ts) || now, pId || null, source,
        normalizedAttachments(m), mediaTypeFor(m), mediaPathFor(m), firstReaction(m), reactionsJson(m), receiptStatus(m), now,
      );
      if (m.direction !== "OUT") {
        processInChatApprovalCommand(db, {
          conversation_id: cId,
          message_id: insertId,
          body: messageBody(m),
        });
      }
      messages++;
    }
  }

  // Self-healing repair: link ANY conversation in this workspace whose contact_id is NULL or
  // dangling (covers rows written by older engine versions before the upContact step existed).
  const orphans = db.prepare(
    `SELECT id, channel, external_id, title FROM unified_conversation c
     WHERE c.workspace_id=? AND c.deleted_at IS NULL
       AND (c.contact_id IS NULL OR NOT EXISTS (SELECT 1 FROM contact ct WHERE ct.id=c.contact_id))`,
  ).all(ws) as Array<{ id: string; channel: string; external_id: string; title: string | null }>;
  for (const o of orphans) {
    const contactId = contactIdFor(o.channel, o.title, o.external_id);
    upContact.run(contactId, ws, (o.title ?? "").trim() || null, now);
    linkConv.run(contactId, o.id);
  }

  return { conversations: convs.length, messages };
}
