/**
 * SQLite store for digital contract-signing requests.
 *
 * One row per "Vertrag senden" with digital signing enabled: a random token
 * (the public link /sign/<token>), the exact PDFs that were e-mailed (kept on
 * disk under data/contract-signing/<token>/ so the portal shows byte-identical
 * documents), the contract payload (to re-render the signed version), the
 * lifecycle (pending → signed | expired | superseded) and — once signed — the
 * signer data + signed file names.
 *
 * Frontend-owned operational data; the business outcome (signed PDF, signer
 * block) is ALSO written to iDempiere (Strapi attachment on the record +
 * offer_conditions.contract.signature) by contractSigning.ts. Fail-soft open,
 * same pattern as emailVerifyDb.ts.
 */
import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'
import crypto from 'crypto'

export type SigningStatus = 'pending' | 'signed' | 'expired' | 'superseded' | 'canceled'
/**
 * What the link is for: `contract` = digital contract signature (/sign/<token>),
 * `quote` = online quote confirmation (/confirm/<token>). Both share this table,
 * the on-disk document store and the lifecycle; superseding is per kind.
 */
export type SigningKind = 'contract' | 'quote'

export interface SigningDocumentMeta {
  key: 'contract' | 'offer' | 'agb' | string
  title: string
  filename: string
  sha256: string
  size: number
  version?: number
  signed?: boolean
}

export interface SigningRequest {
  id: number
  token: string
  kind: SigningKind
  source: 'lead' | 'partner'
  recordId: number
  recordUu: string | null
  company: string
  contactName: string
  email: string
  language: 'de' | 'en'
  status: SigningStatus
  payload: any
  documents: SigningDocumentMeta[]
  signedDocuments: SigningDocumentMeta[]
  signer: any | null
  createdBy: number | null
  createdAt: string
  expiresAt: string
  signedAt: string | null
  renewalRequestedAt: string | null
  lastOpenedAt: string | null
}

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', 'contract-signing.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 signing_requests (
        id                    INTEGER PRIMARY KEY AUTOINCREMENT,
        token                 TEXT NOT NULL UNIQUE,
        source                TEXT NOT NULL,
        record_id             INTEGER NOT NULL,
        record_uu             TEXT,
        company               TEXT,
        contact_name          TEXT,
        email                 TEXT,
        language              TEXT NOT NULL DEFAULT 'de',
        status                TEXT NOT NULL DEFAULT 'pending',
        payload_json          TEXT NOT NULL,
        documents_json        TEXT NOT NULL,
        signed_documents_json TEXT,
        signer_json           TEXT,
        created_by            INTEGER,
        created_at            TEXT NOT NULL,
        expires_at            TEXT NOT NULL,
        signed_at             TEXT,
        renewal_requested_at  TEXT,
        last_opened_at        TEXT
      );
      CREATE INDEX IF NOT EXISTS idx_sr_record ON signing_requests(source, record_id, created_at);
      CREATE INDEX IF NOT EXISTS idx_sr_status ON signing_requests(status, expires_at);
    `)
    // Migration: `kind` (contract | quote) was added with the online quote confirmation.
    const cols: any[] = _db.prepare('PRAGMA table_info(signing_requests)').all()
    if (!cols.some((c: any) => c.name === 'kind')) {
      _db.exec("ALTER TABLE signing_requests ADD COLUMN kind TEXT NOT NULL DEFAULT 'contract'")
    }
    db = _db
    console.log('[ContractSigningDB] ✅ SQLite database initialized at:', dbPath)
    return db
  } catch (err: any) {
    initFailed = true
    console.error('[ContractSigningDB] ❌ init failed; digital signing disabled:', err?.message)
    return null
  }
}

export const isSigningAvailable = (): boolean => !!getDb()

export const signingFilesDir = (token: string): string => path.join(process.cwd(), 'data', 'contract-signing', token)

const safeName = (name: string) => String(name || 'file.pdf').replace(/[^A-Za-z0-9._-]/g, '_')

export const writeSigningFile = (token: string, filename: string, buffer: Buffer): string => {
  const dir = signingFilesDir(token)
  if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true })
  const p = path.join(dir, safeName(filename))
  fs.writeFileSync(p, buffer)
  return p
}

export const readSigningFile = (token: string, filename: string): Buffer | null => {
  try {
    const p = path.join(signingFilesDir(token), safeName(filename))
    if (!fs.existsSync(p)) return null
    return fs.readFileSync(p)
  } catch {
    return null
  }
}

export const sha256Hex = (buffer: Buffer): string => crypto.createHash('sha256').update(buffer).digest('hex')

const parse = (s: any, fallback: any) => {
  try { return s ? JSON.parse(String(s)) : fallback } catch { return fallback }
}

const rowToRequest = (row: any): SigningRequest => ({
  id: row.id,
  token: row.token,
  kind: row.kind === 'quote' ? 'quote' : 'contract',
  source: row.source === 'lead' ? 'lead' : 'partner',
  recordId: Number(row.record_id),
  recordUu: row.record_uu || null,
  company: row.company || '',
  contactName: row.contact_name || '',
  email: row.email || '',
  language: row.language === 'en' ? 'en' : 'de',
  status: row.status,
  payload: parse(row.payload_json, {}),
  documents: parse(row.documents_json, []),
  signedDocuments: parse(row.signed_documents_json, []),
  signer: parse(row.signer_json, null),
  createdBy: row.created_by ?? null,
  createdAt: row.created_at,
  expiresAt: row.expires_at,
  signedAt: row.signed_at || null,
  renewalRequestedAt: row.renewal_requested_at || null,
  lastOpenedAt: row.last_opened_at || null
})

/** Effective status: a pending request past its expiry reads as `expired`. */
export const effectiveStatus = (r: SigningRequest): SigningStatus => {
  if (r.status === 'pending' && new Date(r.expiresAt).getTime() < Date.now()) return 'expired'
  return r.status
}

export const createSigningRequest = (data: {
  kind?: SigningKind
  source: 'lead' | 'partner'
  recordId: number
  recordUu?: string
  company: string
  contactName: string
  email: string
  language: 'de' | 'en'
  payload: any
  documents: SigningDocumentMeta[]
  createdBy?: number | null
  expiresAt: string
}): SigningRequest => {
  const d = getDb()
  if (!d) throw new Error('Signing store unavailable')
  const token = crypto.randomBytes(32).toString('hex')
  const kind: SigningKind = data.kind === 'quote' ? 'quote' : 'contract'
  const now = new Date().toISOString()
  const tx = d.transaction(() => {
    // A new send replaces every still-pending link of the same record AND kind
    // (a new quote must not kill an open contract link and vice versa).
    d.prepare("UPDATE signing_requests SET status = 'superseded' WHERE source = ? AND record_id = ? AND kind = ? AND status = 'pending'")
      .run(data.source, data.recordId, kind)
    d.prepare(`INSERT INTO signing_requests
      (token, kind, source, record_id, record_uu, company, contact_name, email, language, status, payload_json, documents_json, created_by, created_at, expires_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?, ?, ?, ?, ?)`)
      .run(token, kind, data.source, data.recordId, data.recordUu || null, data.company, data.contactName, data.email, data.language,
        JSON.stringify(data.payload || {}), JSON.stringify(data.documents || []), data.createdBy ?? null, now, data.expiresAt)
  })
  tx()
  return getSigningRequestByToken(token)!
}

export const getSigningRequestByToken = (token: string): SigningRequest | null => {
  const d = getDb()
  if (!d) return null
  const row = d.prepare('SELECT * FROM signing_requests WHERE token = ?').get(token)
  return row ? rowToRequest(row) : null
}

/** Latest request of ONE kind (default: contract — the chip/modal semantics). */
export const getLatestSigningRequestForRecord = (source: string, recordId: number, kind: SigningKind = 'contract'): SigningRequest | null => {
  const d = getDb()
  if (!d) return null
  const row = d.prepare('SELECT * FROM signing_requests WHERE source = ? AND record_id = ? AND kind = ? ORDER BY created_at DESC, id DESC LIMIT 1')
    .get(source === 'lead' ? 'lead' : 'partner', recordId, kind)
  return row ? rowToRequest(row) : null
}

/** Every request of a record (all kinds unless `kind` is given), newest first. */
export const listSigningRequestsForRecord = (source: string, recordId: number, limit = 10, kind: SigningKind | null = null): SigningRequest[] => {
  const d = getDb()
  if (!d) return []
  const src = source === 'lead' ? 'lead' : 'partner'
  const rows = kind
    ? d.prepare('SELECT * FROM signing_requests WHERE source = ? AND record_id = ? AND kind = ? ORDER BY created_at DESC, id DESC LIMIT ?').all(src, recordId, kind, limit)
    : d.prepare('SELECT * FROM signing_requests WHERE source = ? AND record_id = ? ORDER BY created_at DESC, id DESC LIMIT ?').all(src, recordId, limit)
  return rows.map(rowToRequest)
}

export const touchSigningRequestOpened = (token: string) => {
  const d = getDb()
  if (!d) return
  try { d.prepare('UPDATE signing_requests SET last_opened_at = ? WHERE token = ?').run(new Date().toISOString(), token) } catch {}
}

export const markSigningRequestSigned = (token: string, signer: any, signedDocuments: SigningDocumentMeta[], signedAt: string) => {
  const d = getDb()
  if (!d) throw new Error('Signing store unavailable')
  d.prepare("UPDATE signing_requests SET status = 'signed', signer_json = ?, signed_documents_json = ?, signed_at = ? WHERE token = ? AND status = 'pending'")
    .run(JSON.stringify(signer || {}), JSON.stringify(signedDocuments || []), signedAt, token)
}

export const markSigningRequestRenewalRequested = (token: string) => {
  const d = getDb()
  if (!d) return
  d.prepare('UPDATE signing_requests SET renewal_requested_at = ? WHERE token = ?').run(new Date().toISOString(), token)
}

export const cancelSigningRequest = (token: string) => {
  const d = getDb()
  if (!d) return
  d.prepare("UPDATE signing_requests SET status = 'canceled' WHERE token = ? AND status = 'pending'").run(token)
}