// Inbox management CORE — DB-level operations so the MCP/terminal can RUN the inbox, not just read it.
// Mirrors the Swift LocalDB methods (archive/mute/mark-read/unread/compose) on the same dewxlocal.db.
// Pure: each fn takes a writable DatabaseSync + args. Engine-actions (react/load-more) need the engine
// socket and go through the command queue (separate); these are pure DB writes the MCP can do directly.

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

/// Archive (or unarchive) a conversation — hidden from All, shown in Archived.
export function archiveConversation(db: DatabaseSync, a: { chat_id: string; archived?: boolean }) {
  const on = a.archived !== false;
  const r = db.prepare(`UPDATE unified_conversation SET archived_at=?, updated_at=? WHERE id=?`)
    .run(on ? now() : null, now(), a.chat_id);
  return { ok: (r.changes ?? 0) > 0, archived: on };
}

/// Mute (or unmute) a conversation — still ingested + visible, but no notifications.
export function muteConversation(db: DatabaseSync, a: { chat_id: string; muted?: boolean }) {
  const on = a.muted !== false;
  const r = db.prepare(`UPDATE unified_conversation SET muted=?, updated_at=? WHERE id=?`)
    .run(on ? 1 : 0, now(), a.chat_id);
  return { ok: (r.changes ?? 0) > 0, muted: on };
}

/// Mark every non-archived conversation read.
export function markAllRead(db: DatabaseSync) {
  const ws = selectedWorkspaceId(db);
  const r = db.prepare(`UPDATE unified_conversation SET unread=0, updated_at=?
                        WHERE workspace_id=? AND deleted_at IS NULL AND archived_at IS NULL AND unread>0`).run(now(), ws);
  return { ok: true, cleared: r.changes ?? 0 };
}

/// Total unread across live, non-archived conversations + a quick per-channel breakdown.
export function unreadTotal(db: DatabaseSync) {
  const ws = selectedWorkspaceId(db);
  const t = db.prepare(`SELECT COALESCE(SUM(unread),0) AS total, COUNT(*) AS chats FROM unified_conversation
                        WHERE workspace_id=? AND deleted_at IS NULL AND archived_at IS NULL AND unread>0`).get(ws) as any;
  const byCh = db.prepare(`SELECT channel, COALESCE(SUM(unread),0) AS unread FROM unified_conversation
                           WHERE workspace_id=? AND deleted_at IS NULL AND archived_at IS NULL AND unread>0
                           GROUP BY channel ORDER BY unread DESC`).all(ws);
  return { unread_total: t.total, chats_with_unread: t.chats, by_channel: byCh };
}

/// Soft-delete a single message locally (LOCAL only — never touches the provider).
export function deleteMessageLocal(db: DatabaseSync, a: { message_id: string }) {
  const r = db.prepare(`UPDATE unified_message SET deleted_at=?, updated_at=? WHERE id=?`).run(now(), now(), a.message_id);
  return { ok: (r.changes ?? 0) > 0 };
}

/// Connected accounts — the per-account (channel + account slug) breakdown the operator needs before
/// sending: which slug to pass to send_message/new_conversation, how many convos/unread it carries, and
/// last_activity (a DB-side freshness proxy — most recent inbound, not a live socket check).
export function connectedAccounts(db: DatabaseSync) {
  const ws = selectedWorkspaceId(db);
  const rows = db.prepare(
    `SELECT channel, account_id AS account, COUNT(*) AS conversations,
            COALESCE(SUM(unread),0) AS unread, MAX(last_ts) AS last_activity_ms
     FROM unified_conversation WHERE workspace_id=? AND deleted_at IS NULL AND account_id IS NOT NULL
     GROUP BY channel, account_id ORDER BY conversations DESC`).all(ws) as any[];
  const nowMs = now();
  const accounts = rows.map((r) => {
    const ageMin = r.last_activity_ms ? Math.round((nowMs - r.last_activity_ms) / 60000) : null;
    return {
      channel: r.channel, account: r.account, conversations: r.conversations, unread: r.unread,
      last_activity_ms: r.last_activity_ms,
      last_activity: ageMin == null ? "never" : ageMin < 60 ? `${ageMin}m ago` : ageMin < 1440 ? `${Math.round(ageMin / 60)}h ago` : `${Math.round(ageMin / 1440)}d ago`,
      fresh: ageMin != null && ageMin < 1440, // inbound within 24h — quiet ≠ disconnected
    };
  });
  return { count: accounts.length, note: "account = the slug to pass to send_message/new_conversation. last_activity = most recent inbound (freshness proxy, not a live-socket check).", accounts };
}

/// Start a NEW conversation (compose): ensure the conversation + contact rows exist, return chat_id.
/// Then send to it with send_message. external_id = the channel handle (WhatsApp jid, email addr, etc.).
/// Mirrors Swift LocalDB.convId ("wa:<account>:<ext>") + ensureContactId ("ct:<CHANNEL>:<ext>") exactly.
export function newConversation(db: DatabaseSync, a: { channel: string; account: string; external_id: string; title?: string }) {
  const ws = selectedWorkspaceId(db);
  const channel = a.channel.toUpperCase();
  const convId = `wa:${a.account}:${a.external_id}`;
  const contactId = `ct:${channel}:${a.external_id}`;
  const title = a.title || a.external_id;
  db.prepare(`INSERT OR IGNORE INTO contact(id, workspace_id, display_name, updated_at) VALUES(?, ?, ?, ?)`)
    .run(contactId, ws, title, now());
  db.prepare(`INSERT INTO unified_conversation(id, workspace_id, channel, account_id, external_id, title, contact_id, last_ts, unread, updated_at)
              VALUES(?, ?, ?, ?, ?, ?, ?, ?, 0, ?)
              ON CONFLICT(id) DO UPDATE SET title=COALESCE(excluded.title, unified_conversation.title), updated_at=excluded.updated_at`)
    .run(convId, ws, channel, a.account, a.external_id, title, contactId, now(), now());
  return { chat_id: convId, contact_id: contactId, channel, account: a.account };
}
