import { DatabaseSync } from "node:sqlite";
import { randomBytes } from "node:crypto";
import { workDecisionPropose } from "../../core/work.ts";

export type PairingStatus = "none" | "pending" | "approved";

export type PairingConfig = {
  prefix: string;
  module: string;
  channelLabel: string;
};

export type PendingPairing = {
  code: string;
  decision_id: string | null;
  created: boolean;
  locked: boolean;
  expires_at: number;
};

function table(prefix: string, suffix: "pending_pairing" | "approved_sender"): string {
  if (!/^[a-z][a-z0-9_]*$/.test(prefix)) throw new Error(`invalid pairing table prefix: ${prefix}`);
  return `${prefix}_${suffix}`;
}

export function ensurePairingSchema(db: DatabaseSync, config: PairingConfig): void {
  const pending = table(config.prefix, "pending_pairing");
  const approved = table(config.prefix, "approved_sender");
  db.exec(`
    CREATE TABLE IF NOT EXISTS ${pending} (
      sender_id TEXT PRIMARY KEY,
      code TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending',
      decision_id TEXT,
      conversation_external_id TEXT NOT NULL,
      sender_name TEXT NOT NULL DEFAULT '',
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      expires_at INTEGER NOT NULL DEFAULT 0,
      attempts INTEGER NOT NULL DEFAULT 0,
      locked_until INTEGER NOT NULL DEFAULT 0
    );
    CREATE TABLE IF NOT EXISTS ${approved} (
      sender_id TEXT PRIMARY KEY,
      sender_name TEXT NOT NULL DEFAULT '',
      approved_by TEXT NOT NULL DEFAULT '',
      approved_at INTEGER NOT NULL,
      metadata_json TEXT NOT NULL DEFAULT '{}'
    );
  `);
  ensureColumn(db, pending, "expires_at", "expires_at INTEGER NOT NULL DEFAULT 0");
  ensureColumn(db, pending, "attempts", "attempts INTEGER NOT NULL DEFAULT 0");
  ensureColumn(db, pending, "locked_until", "locked_until INTEGER NOT NULL DEFAULT 0");
}

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

export function pairingCode(senderId: string, now = Date.now(), nonce = randomBytes(8).toString("hex")): string {
  let h = 2166136261;
  const input = `${senderId}:${now}:${nonce}`;
  for (let i = 0; i < input.length; i++) {
    h ^= input.charCodeAt(i);
    h = Math.imul(h, 16777619);
  }
  return (h >>> 0).toString(36).toUpperCase().padStart(8, "0").slice(0, 8);
}

export function pairingStatus(db: DatabaseSync, config: PairingConfig, senderId: string): PairingStatus {
  ensurePairingSchema(db, config);
  const approved = db.prepare(`SELECT 1 FROM ${table(config.prefix, "approved_sender")} WHERE sender_id=? LIMIT 1`).get(senderId);
  if (approved) return "approved";
  const now = Date.now();
  db.prepare(`UPDATE ${table(config.prefix, "pending_pairing")} SET status='expired', updated_at=? WHERE sender_id=? AND status='pending' AND expires_at > 0 AND expires_at <= ?`)
    .run(now, senderId, now);
  const pending = db.prepare(`SELECT 1 FROM ${table(config.prefix, "pending_pairing")} WHERE sender_id=? AND status='pending' AND (locked_until=0 OR locked_until<=?) LIMIT 1`)
    .get(senderId, now);
  return pending ? "pending" : "none";
}

export function ensurePendingPairing(db: DatabaseSync, config: PairingConfig, a: {
  senderId: string;
  senderName: string;
  conversationExternalId: string;
  now?: number;
}): PendingPairing {
  ensurePairingSchema(db, config);
  const pendingTable = table(config.prefix, "pending_pairing");
  const now = a.now ?? Date.now();
  const ttlMs = 10 * 60 * 1000;
  const maxAttempts = 5;
  db.prepare(`UPDATE ${pendingTable} SET status='expired', updated_at=? WHERE sender_id=? AND status='pending' AND expires_at > 0 AND expires_at <= ?`)
    .run(now, a.senderId, now);
  const existing = db.prepare(
    `SELECT code, decision_id, expires_at, attempts, locked_until FROM ${pendingTable} WHERE sender_id=? AND status='pending' LIMIT 1`,
  ).get(a.senderId) as { code: string; decision_id: string | null; expires_at: number; attempts: number; locked_until: number } | undefined;
  if (existing) {
    const attempts = Number(existing.attempts ?? 0) + 1;
    const lockedUntil = attempts >= maxAttempts ? now + ttlMs : Number(existing.locked_until ?? 0);
    db.prepare(`UPDATE ${pendingTable} SET sender_name=?, conversation_external_id=?, attempts=?, locked_until=?, updated_at=? WHERE sender_id=? AND status='pending'`)
      .run(a.senderName, a.conversationExternalId, attempts, lockedUntil, now, a.senderId);
    return {
      code: existing.code,
      decision_id: existing.decision_id,
      created: false,
      locked: lockedUntil > now,
      expires_at: Number(existing.expires_at ?? 0),
    };
  }
  const code = pairingCode(a.senderId, now);
  const decision = workDecisionPropose(db, {
    module: config.module,
    title: `Approve ${config.channelLabel} DM pairing ${code}`,
    proposal: `Approve sender ${a.senderName} (${a.senderId}) for ${config.channelLabel} DM engagement.`,
    rationale: `Unknown ${config.channelLabel} DM sender. Store inbound messages but do not auto-engage until approved.`,
    risk: "external",
  }) as { id?: string } | undefined;
  const decisionId = decision?.id ?? null;
  db.prepare(
    `INSERT INTO ${pendingTable}
      (sender_id, code, status, decision_id, conversation_external_id, sender_name, created_at, updated_at, attempts)
     VALUES (?, ?, 'pending', ?, ?, ?, ?, ?, 1)`,
  ).run(a.senderId, code, decisionId, a.conversationExternalId, a.senderName, now, now);
  db.prepare(`UPDATE ${pendingTable} SET expires_at=? WHERE sender_id=? AND status='pending'`)
    .run(now + ttlMs, a.senderId);
  return { code, decision_id: decisionId, created: true, locked: false, expires_at: now + ttlMs };
}

export function approvePendingPairing(db: DatabaseSync, config: PairingConfig, senderId: string, approvedBy = "Roki", now = Date.now()): boolean {
  ensurePairingSchema(db, config);
  const pendingTable = table(config.prefix, "pending_pairing");
  const approvedTable = table(config.prefix, "approved_sender");
  const pending = db.prepare(`SELECT sender_name, expires_at, locked_until FROM ${pendingTable} WHERE sender_id=? AND status='pending'`).get(senderId) as
    | { sender_name: string; expires_at: number; locked_until: number }
    | undefined;
  if (!pending) return false;
  if (Number(pending.expires_at ?? 0) > 0 && Number(pending.expires_at) <= now) return false;
  if (Number(pending.locked_until ?? 0) > now) return false;
  db.prepare(
    `INSERT INTO ${approvedTable}(sender_id, sender_name, approved_by, approved_at, metadata_json)
     VALUES (?, ?, ?, ?, '{}')
     ON CONFLICT(sender_id) DO UPDATE SET
       sender_name=excluded.sender_name,
       approved_by=excluded.approved_by,
       approved_at=excluded.approved_at`,
  ).run(senderId, pending.sender_name, approvedBy, now);
  db.prepare(`UPDATE ${pendingTable} SET status='approved', updated_at=? WHERE sender_id=?`).run(now, senderId);
  return true;
}

export function pairingCounts(db: DatabaseSync, config: PairingConfig): { pending: number; approved: number } {
  ensurePairingSchema(db, config);
  return {
    pending: Number((db.prepare(`SELECT COUNT(*) AS c FROM ${table(config.prefix, "pending_pairing")} WHERE status='pending'`).get() as any)?.c ?? 0),
    approved: Number((db.prepare(`SELECT COUNT(*) AS c FROM ${table(config.prefix, "approved_sender")}`).get() as any)?.c ?? 0),
  };
}

export function unpairedBody(channelLabel: string, body: string, pairing: { code: string; decision_id: string | null; locked?: boolean }): string {
  if (pairing.locked) {
    return `[Unpaired ${channelLabel} DM · pairing locked after repeated attempts] ${body}`;
  }
  const decision = pairing.decision_id ? ` decision ${pairing.decision_id}` : " pending approval";
  return `[Unpaired ${channelLabel} DM · code ${pairing.code} ·${decision}] ${body}`;
}
