// Web -> local outbound draft pull. Stores server-created DewLink local-device
// drafts in the same outbound_draft table the Swift approval UI already reads.

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

export interface WebOutboundDraft {
  id: string;
  conversationId?: string | null;
  providerConversationId?: string | null;
  channel: string;
  accountId: string;
  externalId: string;
  title?: string | null;
  body: string;
  status: "pending";
  source: "web";
  executionTarget?: "local-device";
  requiresOnlineDevice?: boolean;
  createdAt: number;
  updatedAt: number;
}

export interface OutboundDraftAck {
  id: string;
  webMessageId: string;
  status: "sent" | "placed" | "rejected" | "failed";
  providerMsgId?: string | null;
  error?: string | null;
}

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};`);
  }
}

export function ensureOutboundDraftSchema(db: DatabaseSync) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS outbound_draft (
      id TEXT PRIMARY KEY,
      workspace_id TEXT NOT NULL DEFAULT 'local',
      conversation_id TEXT NOT NULL,
      channel TEXT NOT NULL,
      account_id TEXT NOT NULL,
      external_id TEXT NOT NULL,
      title TEXT,
      body TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending',
      source TEXT DEFAULT 'mcp',
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_outbound_draft_status ON outbound_draft(workspace_id, status, created_at);
  `);
  ensureColumn(db, "outbound_draft", "web_message_id", "web_message_id TEXT");
  ensureColumn(db, "outbound_draft", "web_ack_status", "web_ack_status TEXT");
  ensureColumn(db, "outbound_draft", "web_ack_at", "web_ack_at INTEGER");
  ensureColumn(db, "outbound_draft", "web_ack_error", "web_ack_error TEXT");
  ensureColumn(db, "outbound_draft", "provider_msg_id", "provider_msg_id TEXT");
  ensureColumn(db, "outbound_draft", "error", "error TEXT");
}

function asMs(value: unknown, fallback: number): number {
  const n = Number(value);
  return Number.isFinite(n) && n > 0 ? Math.floor(n) : fallback;
}

function normalizeChannel(channel: string): string {
  return String(channel || "").trim().toUpperCase();
}

function localConversationId(db: DatabaseSync, draft: WebOutboundDraft, workspaceId: string): string {
  const channel = normalizeChannel(draft.channel);
  const externalId = draft.externalId || draft.providerConversationId || draft.conversationId || "";
  const accountId = draft.accountId || "";
  const existing = db.prepare(
    `SELECT id
     FROM unified_conversation
     WHERE workspace_id=?
       AND channel=?
       AND account_id=?
       AND external_id=?
       AND deleted_at IS NULL
     ORDER BY updated_at DESC
     LIMIT 1`,
  ).get(workspaceId, channel, accountId, externalId) as { id?: string } | undefined;
  if (existing?.id) return existing.id;
  if (channel === "WHATSAPP" && accountId && externalId) {
    return `wa:${accountId}:${externalId}`;
  }
  return scopedWebId(workspaceId, channel, externalId);
}

function ensureConversationShell(
  db: DatabaseSync,
  draft: WebOutboundDraft,
  workspaceId: string,
  conversationId: string,
  now: number,
) {
  const channel = normalizeChannel(draft.channel);
  const externalId = draft.externalId || draft.providerConversationId || draft.conversationId || "";
  db.prepare(
    `INSERT INTO unified_conversation
       (id, workspace_id, channel, account_id, external_id, title, last_ts, unread, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?)
     ON CONFLICT(id) DO UPDATE SET
       title=COALESCE(excluded.title, unified_conversation.title),
       account_id=COALESCE(excluded.account_id, unified_conversation.account_id),
       external_id=COALESCE(excluded.external_id, unified_conversation.external_id),
       updated_at=excluded.updated_at,
       deleted_at=NULL`,
  ).run(
    conversationId,
    workspaceId,
    channel,
    draft.accountId,
    externalId,
    draft.title ?? externalId,
    Math.max(asMs(draft.updatedAt, now), now),
    now,
  );
}

export function storeOutboundDrafts(
  db: DatabaseSync,
  drafts: WebOutboundDraft[],
  opts: { workspaceId?: string } = {},
): { drafts: number; stored: number } {
  ensureOutboundDraftSchema(db);
  const workspaceId = opts.workspaceId ?? selectedWorkspaceId(db);
  const now = Date.now();
  const upsert = db.prepare(
    `INSERT INTO outbound_draft
       (id, workspace_id, conversation_id, channel, account_id, external_id, title, body,
        status, source, created_at, updated_at, web_message_id)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'web', ?, ?, ?)
     ON CONFLICT(id) DO UPDATE SET
       workspace_id=excluded.workspace_id,
       conversation_id=excluded.conversation_id,
       channel=excluded.channel,
       account_id=excluded.account_id,
       external_id=excluded.external_id,
       title=COALESCE(excluded.title, outbound_draft.title),
       body=CASE
         WHEN outbound_draft.status='pending' THEN excluded.body
         ELSE outbound_draft.body
       END,
       status=CASE
         WHEN outbound_draft.status='pending' THEN excluded.status
         ELSE outbound_draft.status
       END,
       source='web',
       updated_at=excluded.updated_at,
       web_message_id=excluded.web_message_id`,
  );

  let stored = 0;
  for (const draft of drafts) {
    if (!draft.id || !draft.accountId || !draft.externalId || !draft.body) continue;
    const channel = normalizeChannel(draft.channel);
    if (!channel) continue;
    const conversationId = localConversationId(db, draft, workspaceId);
    ensureConversationShell(db, draft, workspaceId, conversationId, now);
    upsert.run(
      draft.id,
      workspaceId,
      conversationId,
      channel,
      draft.accountId,
      draft.externalId,
      draft.title ?? draft.externalId,
      draft.body,
      asMs(draft.createdAt, now),
      asMs(draft.updatedAt, now),
      draft.id,
    );
    stored++;
  }
  return { drafts: drafts.length, stored };
}

export function pendingOutboundDraftAcks(db: DatabaseSync, limit = 20): OutboundDraftAck[] {
  ensureOutboundDraftSchema(db);
  const rows = db.prepare(
    `SELECT id, web_message_id AS webMessageId, status, provider_msg_id AS providerMsgId, error
     FROM outbound_draft
     WHERE source='web'
       AND web_message_id IS NOT NULL
       AND status IN ('sent', 'placed', 'rejected', 'failed')
       AND COALESCE(web_ack_status, '') != status
     ORDER BY updated_at ASC
     LIMIT ?`,
  ).all(limit) as Array<{ id: string; webMessageId: string; status: OutboundDraftAck["status"]; providerMsgId?: string | null; error?: string | null }>;
  return rows.map((row) => ({
    id: row.id,
    webMessageId: row.webMessageId,
    status: row.status,
    providerMsgId: row.providerMsgId ?? null,
    error: row.error ?? null,
  }));
}

export function markOutboundDraftAcked(
  db: DatabaseSync,
  webMessageId: string,
  status: string,
  ackedAt = Date.now(),
  error?: string,
) {
  ensureOutboundDraftSchema(db);
  db.prepare(
    `UPDATE outbound_draft
     SET web_ack_status=?, web_ack_at=?, web_ack_error=?
     WHERE web_message_id=?`,
  ).run(status, ackedAt, error ?? null, webMessageId);
}
