/**
 * SQLite store for pending (unconfirmed) e-mail address changes made through
 * the merchant self-service account page (/users/profile → E-Mails & Billing).
 *
 * A change to any of the c_bpartner / ad_user e-mail fields is NOT written to
 * iDempiere directly — it is parked here with a random token, a confirmation
 * link is mailed to the NEW address, and only when that link is clicked
 * (server/api/email-verification/confirm.post.ts) is the change applied.
 * Until then the old address stays active.
 *
 * Frontend-owned operational data — NOT iDempiere business data. Same
 * fail-soft pattern as chatDb.ts: lazy open, every export no-ops on DB
 * failure so the server never crashes at boot.
 */

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', 'email-verify.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 pending_email_changes (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        token        TEXT NOT NULL UNIQUE,
        kind         TEXT NOT NULL,              -- 'bpartner' | 'aduser'
        field_key    TEXT NOT NULL,              -- key in EMAIL_FIELDS
        target_id    INTEGER NOT NULL,           -- c_bpartner.id or ad_user.id
        requested_by INTEGER NOT NULL,           -- ad_user.id of the requester
        old_email    TEXT,
        new_email    TEXT NOT NULL,
        language     TEXT,
        created_at   INTEGER NOT NULL,
        expires_at   INTEGER NOT NULL,
        confirmed_at INTEGER,
        canceled_at  INTEGER
      );
      CREATE INDEX IF NOT EXISTS idx_pec_target    ON pending_email_changes(kind, target_id, field_key);
      CREATE INDEX IF NOT EXISTS idx_pec_requester ON pending_email_changes(requested_by, created_at);
    `)

    db = _db
    console.log('[EmailVerifyDB] ✅ SQLite database initialized at:', dbPath)
    return db
  } catch (err: any) {
    initFailed = true
    console.error('[EmailVerifyDB] ❌ init failed; email verification disabled:', err?.message)
    return null
  }
}

const mapRow = (r: any) => ({
  id: r.id,
  token: r.token,
  kind: r.kind as 'bpartner' | 'aduser',
  fieldKey: r.field_key,
  targetId: r.target_id,
  requestedBy: r.requested_by,
  oldEmail: r.old_email ?? '',
  newEmail: r.new_email,
  language: r.language ?? '',
  createdAt: r.created_at,
  expiresAt: r.expires_at,
  confirmedAt: r.confirmed_at ?? null,
  canceledAt: r.canceled_at ?? null
})

export interface CreatePendingInput {
  token: string
  kind: 'bpartner' | 'aduser'
  fieldKey: string
  targetId: number
  requestedBy: number
  oldEmail: string
  newEmail: string
  language: string
  ttlMs: number
}

/**
 * Creates a new pending change, superseding (canceling) any still-active
 * pending change for the same target field first — a field can only ever
 * have ONE outstanding confirmation.
 */
export const createPendingChange = (input: CreatePendingInput) => {
  const d = getDb()
  if (!d) return null
  try {
    const now = Date.now()
    cancelActivePending(input.kind, input.targetId, input.fieldKey)
    const res = d.prepare(`
      INSERT INTO pending_email_changes
        (token, kind, field_key, target_id, requested_by, old_email, new_email, language, created_at, expires_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      input.token, input.kind, input.fieldKey, input.targetId, input.requestedBy,
      input.oldEmail ?? '', input.newEmail, input.language ?? '', now, now + input.ttlMs
    )
    return { id: Number(res.lastInsertRowid), createdAt: now, expiresAt: now + input.ttlMs }
  } catch (err: any) {
    console.error('[EmailVerifyDB] createPendingChange failed:', err?.message)
    return null
  }
}

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

/**
 * Still-active (unconfirmed, uncanceled, unexpired) pending changes for the
 * caller's own records: their ad_user row + their company's c_bpartner row.
 * Keyed by fieldKey. Queried by TARGET (not requester) so a colleague of the
 * same merchant org sees a pending company-field change too.
 */
export const getActivePendingFor = (userId: number, bpartnerId: number | null): Record<string, any> => {
  const d = getDb()
  if (!d) return {}
  try {
    const now = Date.now()
    const rows = d.prepare(`
      SELECT * FROM pending_email_changes
      WHERE confirmed_at IS NULL AND canceled_at IS NULL AND expires_at > ?
        AND ((kind = 'aduser' AND target_id = ?) OR (kind = 'bpartner' AND target_id = ?))
      ORDER BY id ASC
    `).all(now, userId, bpartnerId ?? -1)
    const out: Record<string, any> = {}
    rows.forEach((r: any) => {
      const m = mapRow(r)
      out[m.fieldKey] = { newEmail: m.newEmail, createdAt: m.createdAt, expiresAt: m.expiresAt }
    })
    return out
  } catch (err: any) {
    console.error('[EmailVerifyDB] getActivePendingFor failed:', err?.message)
    return {}
  }
}

/** Cancels any active pending change for one target field. Returns rows changed. */
export const cancelActivePending = (kind: 'bpartner' | 'aduser', targetId: number, fieldKey: string) => {
  const d = getDb()
  if (!d) return 0
  try {
    const res = d.prepare(`
      UPDATE pending_email_changes SET canceled_at = ?
      WHERE kind = ? AND target_id = ? AND field_key = ?
        AND confirmed_at IS NULL AND canceled_at IS NULL
    `).run(Date.now(), kind, targetId, fieldKey)
    return res.changes
  } catch (err: any) {
    console.error('[EmailVerifyDB] cancelActivePending failed:', err?.message)
    return 0
  }
}

export const confirmPending = (id: number) => {
  const d = getDb()
  if (!d || !id) return false
  try {
    const res = d.prepare(`
      UPDATE pending_email_changes SET confirmed_at = ?
      WHERE id = ? AND confirmed_at IS NULL AND canceled_at IS NULL
    `).run(Date.now(), id)
    return res.changes > 0
  } catch (err: any) {
    console.error('[EmailVerifyDB] confirmPending failed:', err?.message)
    return false
  }
}

/** Verification requests created by this user in the last `windowMs` (rate limit). */
export const countRecentRequests = (userId: number, windowMs: number) => {
  const d = getDb()
  if (!d) return 0
  try {
    const row: any = d.prepare(`
      SELECT COUNT(*) AS cnt FROM pending_email_changes
      WHERE requested_by = ? AND created_at > ?
    `).get(userId, Date.now() - windowMs)
    return Number(row?.cnt || 0)
  } catch (err: any) {
    console.error('[EmailVerifyDB] countRecentRequests failed:', err?.message)
    return 0
  }
}