/**
 * SQLite store for the staff live-chat module (1:1 direct messages).
 *
 * Frontend-owned operational data — NOT iDempiere business data — exactly like
 * the push-subscriptions DB. Lives at data/chat.db.
 *
 * ISOLATION: unlike pushDb.ts (which opens at import), this lazy-opens inside
 * try/catch on first use and is only ever imported by chat code. If
 * better-sqlite3 or the file fails, every export logs and no-ops (returns
 * empty/null) — chat history degrades but the server never crashes at boot,
 * and disabling/removing the chat module leaves nothing dangling.
 */

import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'

let db: any = null
let initFailed = false

const getDb = () => {
  if (db) return db
  if (initFailed) return null
  try {
    const dbPath = path.join(process.cwd(), 'data', 'chat.db')
    const dataDir = path.dirname(dbPath)
    if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true })

    const _db = new Database(dbPath)
    _db.pragma('journal_mode = WAL')
    _db.exec(`
      CREATE TABLE IF NOT EXISTS messages (
        id                INTEGER PRIMARY KEY AUTOINCREMENT,
        sender_user_id    INTEGER NOT NULL,
        recipient_user_id INTEGER NOT NULL,
        body              TEXT NOT NULL,
        created_at        INTEGER NOT NULL,
        read_at           INTEGER,
        client_msg_id     TEXT
      );
      CREATE INDEX IF NOT EXISTS idx_msg_pair             ON messages(sender_user_id, recipient_user_id, id);
      CREATE INDEX IF NOT EXISTS idx_msg_recipient_unread ON messages(recipient_user_id, read_at);
      CREATE INDEX IF NOT EXISTS idx_msg_client           ON messages(client_msg_id);

      -- Voice-note audio blobs. Kept here (not Strapi) so the chat module stays
      -- self-contained and the audio survives deploys alongside chat.db. sender/
      -- recipient are duplicated on the row so playback can authorize without a join.
      CREATE TABLE IF NOT EXISTS voice_notes (
        id                INTEGER PRIMARY KEY AUTOINCREMENT,
        message_id        INTEGER,
        sender_user_id    INTEGER NOT NULL,
        recipient_user_id INTEGER NOT NULL,
        mime              TEXT NOT NULL,
        duration          INTEGER,
        bytes             BLOB NOT NULL,
        created_at        INTEGER NOT NULL
      );

      -- Emoji reactions: one row per (message, user, emoji); clicking the same
      -- emoji again deletes the row (toggle).
      CREATE TABLE IF NOT EXISTS message_reactions (
        message_id  INTEGER NOT NULL,
        user_id     INTEGER NOT NULL,
        emoji       TEXT NOT NULL,
        created_at  INTEGER NOT NULL,
        PRIMARY KEY (message_id, user_id, emoji)
      );
      CREATE INDEX IF NOT EXISTS idx_reaction_msg ON message_reactions(message_id);
    `)

    // Additive migration: DBs created before share-to-chat predate the `meta`
    // column (CREATE TABLE IF NOT EXISTS won't add it). Guarded so it's idempotent.
    try {
      const cols: any[] = _db.prepare(`PRAGMA table_info(messages)`).all()
      if (!cols.some((c: any) => c.name === 'meta')) {
        _db.exec(`ALTER TABLE messages ADD COLUMN meta TEXT`)
      }
    } catch (e: any) {
      console.error('[ChatDB] meta migration failed:', e?.message)
    }

    db = _db
    console.log('[ChatDB] ✅ SQLite database initialized at:', dbPath)
    startRetention() // daily sweep of old read messages (per-process; PM2 instances:1)
    return db
  } catch (err: any) {
    initFailed = true
    console.error('[ChatDB] ❌ init failed; chat history disabled:', err?.message)
    return null
  }
}

const parseMeta = (raw: any) => {
  if (!raw) return null
  try { return JSON.parse(raw) } catch { return null }
}

const mapRow = (r: any) => ({
  id: r.id,
  senderUserId: r.sender_user_id,
  recipientUserId: r.recipient_user_id,
  body: r.body,
  createdAt: r.created_at,
  readAt: r.read_at ?? null,
  clientMsgId: r.client_msg_id ?? null,
  meta: parseMeta(r.meta)
})

export interface InsertMessageInput {
  senderUserId: number
  recipientUserId: number
  body: string
  createdAt: number
  clientMsgId?: string | null
  meta?: any | null
}

export const insertMessage = (input: InsertMessageInput) => {
  const d = getDb()
  if (!d) return null
  try {
    const stmt = d.prepare(`
      INSERT INTO messages (sender_user_id, recipient_user_id, body, created_at, client_msg_id, meta)
      VALUES (?, ?, ?, ?, ?, ?)
    `)
    const res = stmt.run(
      input.senderUserId,
      input.recipientUserId,
      input.body,
      input.createdAt,
      input.clientMsgId ?? null,
      input.meta ? JSON.stringify(input.meta) : null
    )
    return {
      id: Number(res.lastInsertRowid),
      senderUserId: input.senderUserId,
      recipientUserId: input.recipientUserId,
      body: input.body,
      createdAt: input.createdAt,
      readAt: null,
      clientMsgId: input.clientMsgId ?? null,
      meta: input.meta ?? null
    }
  } catch (err: any) {
    console.error('[ChatDB] insert failed:', err?.message)
    return null
  }
}

/**
 * Full conversation between two users, oldest → newest, capped at `limit`.
 * Pass `beforeId` to page backwards (load older messages).
 */
export const getConversation = (userA: number, userB: number, limit = 50, beforeId: number | null = null) => {
  const d = getDb()
  if (!d) return []
  try {
    const params: any[] = [userA, userB, userB, userA]
    let sql = `
      SELECT * FROM messages
      WHERE ((sender_user_id = ? AND recipient_user_id = ?)
          OR (sender_user_id = ? AND recipient_user_id = ?))
    `
    if (beforeId) {
      sql += ` AND id < ?`
      params.push(beforeId)
    }
    sql += ` ORDER BY id DESC LIMIT ?`
    params.push(limit)
    const rows = d.prepare(sql).all(...params)
    const mapped = rows.reverse().map(mapRow)
    attachReactions(d, mapped)
    return mapped
  } catch (err: any) {
    console.error('[ChatDB] getConversation failed:', err?.message)
    return []
  }
}

/** Fetch a single message by id (used to authorize/route reactions). */
export const getMessageById = (id: number) => {
  const d = getDb()
  if (!d || !id) return null
  try {
    const row = d.prepare(`SELECT * FROM messages WHERE id = ? LIMIT 1`).get(id)
    return row ? mapRow(row) : null
  } catch (err: any) {
    console.error('[ChatDB] getMessageById failed:', err?.message)
    return null
  }
}

/** Update a message's body and/or meta (e.g. once a voice transcript is ready). */
export const updateMessageContent = (id: number, patch: { body?: string; meta?: any }) => {
  const d = getDb()
  if (!d || !id) return false
  try {
    const sets: string[] = []
    const vals: any[] = []
    if (typeof patch.body === 'string') { sets.push('body = ?'); vals.push(patch.body) }
    if (patch.meta !== undefined) { sets.push('meta = ?'); vals.push(patch.meta ? JSON.stringify(patch.meta) : null) }
    if (!sets.length) return false
    vals.push(id)
    d.prepare(`UPDATE messages SET ${sets.join(', ')} WHERE id = ?`).run(...vals)
    return true
  } catch (err: any) {
    console.error('[ChatDB] updateMessageContent failed:', err?.message)
    return false
  }
}

// ── voice notes ───────────────────────────────────────────────────────────────
export interface InsertVoiceNoteInput {
  senderUserId: number
  recipientUserId: number
  mime: string
  duration: number | null
  bytes: Buffer
}

export const insertVoiceNote = (input: InsertVoiceNoteInput) => {
  const d = getDb()
  if (!d) return null
  try {
    const res = d.prepare(`
      INSERT INTO voice_notes (sender_user_id, recipient_user_id, mime, duration, bytes, created_at)
      VALUES (?, ?, ?, ?, ?, ?)
    `).run(input.senderUserId, input.recipientUserId, input.mime, input.duration ?? null, input.bytes, Date.now())
    return { id: Number(res.lastInsertRowid) }
  } catch (err: any) {
    console.error('[ChatDB] insertVoiceNote failed:', err?.message)
    return null
  }
}

/** Link a voice note to the chat message that carries it (for retention cleanup). */
export const linkVoiceNoteMessage = (voiceId: number, messageId: number) => {
  const d = getDb()
  if (!d || !voiceId || !messageId) return false
  try {
    d.prepare(`UPDATE voice_notes SET message_id = ? WHERE id = ?`).run(messageId, voiceId)
    return true
  } catch (err: any) {
    console.error('[ChatDB] linkVoiceNoteMessage failed:', err?.message)
    return false
  }
}

export const getVoiceNote = (id: number) => {
  const d = getDb()
  if (!d || !id) return null
  try {
    const r: any = d.prepare(`SELECT * FROM voice_notes WHERE id = ? LIMIT 1`).get(id)
    if (!r) return null
    return {
      id: r.id,
      senderUserId: r.sender_user_id,
      recipientUserId: r.recipient_user_id,
      mime: r.mime,
      duration: r.duration ?? null,
      bytes: r.bytes as Buffer
    }
  } catch (err: any) {
    console.error('[ChatDB] getVoiceNote failed:', err?.message)
    return null
  }
}

// ── reactions ───────────────────────────────────────────────────────────────
/** Toggle a reaction on/off. `on=false` removes it. Returns true on success. */
export const setReaction = (messageId: number, userId: number, emoji: string, on: boolean) => {
  const d = getDb()
  if (!d || !messageId || !userId || !emoji) return false
  try {
    if (on) {
      d.prepare(`
        INSERT OR IGNORE INTO message_reactions (message_id, user_id, emoji, created_at)
        VALUES (?, ?, ?, ?)
      `).run(messageId, userId, emoji, Date.now())
    } else {
      d.prepare(`DELETE FROM message_reactions WHERE message_id = ? AND user_id = ? AND emoji = ?`)
        .run(messageId, userId, emoji)
    }
    return true
  } catch (err: any) {
    console.error('[ChatDB] setReaction failed:', err?.message)
    return false
  }
}

/** Attach a `reactions: [{ userId, emoji }]` array to each mapped message in place. */
const attachReactions = (d: any, msgs: any[]) => {
  if (!msgs.length) return
  try {
    const ids = msgs.map(m => m.id).filter(Boolean)
    if (!ids.length) return
    const placeholders = ids.map(() => '?').join(',')
    const rows: any[] = d.prepare(
      `SELECT message_id, user_id, emoji FROM message_reactions WHERE message_id IN (${placeholders})`
    ).all(...ids)
    if (!rows.length) return
    const byMsg: Record<number, any[]> = {}
    rows.forEach((r: any) => {
      (byMsg[r.message_id] ||= []).push({ userId: r.user_id, emoji: r.emoji })
    })
    msgs.forEach(m => { if (byMsg[m.id]) m.reactions = byMsg[m.id] })
  } catch (err: any) {
    console.error('[ChatDB] attachReactions failed:', err?.message)
  }
}

/** Per-sender unread counts for a recipient: { [senderUserId]: count }. */
export const getUnreadCounts = (userId: number): Record<number, number> => {
  const d = getDb()
  if (!d) return {}
  try {
    const rows = d.prepare(`
      SELECT sender_user_id AS peer, COUNT(*) AS cnt
      FROM messages
      WHERE recipient_user_id = ? AND read_at IS NULL
      GROUP BY sender_user_id
    `).all(userId)
    const out: Record<number, number> = {}
    rows.forEach((r: any) => { out[r.peer] = r.cnt })
    return out
  } catch (err: any) {
    console.error('[ChatDB] getUnreadCounts failed:', err?.message)
    return {}
  }
}

/** Mark all messages from `peer` to `reader` as read. Returns rows updated. */
export const markConversationRead = (reader: number, peer: number, readAt: number) => {
  const d = getDb()
  if (!d) return 0
  try {
    const res = d.prepare(`
      UPDATE messages SET read_at = ?
      WHERE recipient_user_id = ? AND sender_user_id = ? AND read_at IS NULL
    `).run(readAt, reader, peer)
    return res.changes
  } catch (err: any) {
    console.error('[ChatDB] markConversationRead failed:', err?.message)
    return 0
  }
}

export const findByClientMsgId = (clientMsgId: string) => {
  const d = getDb()
  if (!d || !clientMsgId) return null
  try {
    const row = d.prepare(`SELECT * FROM messages WHERE client_msg_id = ? LIMIT 1`).get(clientMsgId)
    return row ? mapRow(row) : null
  } catch (err: any) {
    console.error('[ChatDB] findByClientMsgId failed:', err?.message)
    return null
  }
}

/**
 * Search the caller's own conversations (LIKE on body). Returns the newest
 * matches first, each annotated with `peer` (the other party in that 1:1).
 */
export const searchMessages = (userId: number, q: string, limit = 30) => {
  const d = getDb()
  const term = (q || '').trim()
  if (!d || !term) return []
  try {
    // Escape LIKE wildcards so a literal % / _ in the query isn't treated as one.
    const like = `%${term.replace(/[\\%_]/g, (m) => '\\' + m)}%`
    const rows = d.prepare(`
      SELECT * FROM messages
      WHERE (sender_user_id = ? OR recipient_user_id = ?)
        AND body LIKE ? ESCAPE '\\'
      ORDER BY id DESC
      LIMIT ?
    `).all(userId, userId, like, limit)
    return rows.map((r: any) => {
      const m = mapRow(r)
      return { ...m, peer: m.senderUserId === userId ? m.recipientUserId : m.senderUserId }
    })
  } catch (err: any) {
    console.error('[ChatDB] searchMessages failed:', err?.message)
    return []
  }
}

/** Retention sweep: drop already-read messages older than `days`. */
export const cleanupOldMessages = (days = 60) => {
  const d = getDb()
  if (!d) return 0
  try {
    const cutoff = Date.now() - days * 24 * 60 * 60 * 1000
    const res = d.prepare(`DELETE FROM messages WHERE read_at IS NOT NULL AND created_at < ?`).run(cutoff)
    if (res.changes > 0) console.log('[ChatDB] cleaned up', res.changes, 'old messages')

    // Drop reactions + voice blobs whose owning message is gone, plus voice
    // uploads that never got linked to a message (failed sends, >1 day old).
    try { d.prepare(`DELETE FROM message_reactions WHERE message_id NOT IN (SELECT id FROM messages)`).run() } catch {}
    try {
      d.prepare(`
        DELETE FROM voice_notes
        WHERE (message_id IS NOT NULL AND message_id NOT IN (SELECT id FROM messages))
           OR (message_id IS NULL AND created_at < ?)
      `).run(Date.now() - 24 * 60 * 60 * 1000)
    } catch {}
    return res.changes
  } catch (err: any) {
    console.error('[ChatDB] cleanupOldMessages failed:', err?.message)
    return 0
  }
}

// Daily retention sweep, started once the DB is open. No Nitro cron infra exists
// here, so we mirror the app's setInterval pattern (e.g. chatPresence.sweepTimer).
// Per-process — fine at PM2 instances:1; a multi-instance deploy would just run it
// on each instance (harmless: DELETE is idempotent).
let retentionStarted = false
const DAY_MS = 24 * 60 * 60 * 1000
const startRetention = () => {
  if (retentionStarted) return
  retentionStarted = true
  try { cleanupOldMessages(60) } catch { /* fail-soft */ }
  setInterval(() => { try { cleanupOldMessages(60) } catch { /* fail-soft */ } }, DAY_MS)
}