/**
 * SQLite store for company legal documents (`agb` — Allgemeine Geschäfts-
 * bedingungen, `avv` — Auftragsverarbeitungsvertrag Art. 28 DSGVO), editable at Settings → Legal Documents and
 * rendered as a branded PDF (server/utils/offers/agbPdf.ts) for contract
 * annexes, website export and the public signing portal.
 *
 * Frontend-owned operational content — NOT iDempiere business data. Same
 * fail-soft pattern as chatDb.ts / emailVerifyDb.ts: lazy open, every export
 * degrades to the bundled default (server/utils/offers/agbDefault.ts) on DB
 * failure so the server never crashes and contracts can always be sent.
 *
 * Every save keeps the previous text as a version row (`legal_document_versions`)
 * so an accidental edit can be recovered.
 */
import Database from 'better-sqlite3'
import path from 'path'
import fs from 'fs'
import {
  AGB_DEFAULT_HTML_DE, AGB_DEFAULT_HTML_EN, AGB_DEFAULT_TITLE_DE, AGB_DEFAULT_TITLE_EN,
  AVV_DEFAULT_HTML_DE, AVV_DEFAULT_HTML_EN, AVV_DEFAULT_TITLE_DE, AVV_DEFAULT_TITLE_EN
} from './offers/agbDefault'

/** Documents managed at Settings → Legal Documents. */
export const LEGAL_DOC_KEYS = ['agb', 'avv'] as const
export type LegalDocKey = typeof LEGAL_DOC_KEYS[number]
export const isLegalDocKey = (k: any): k is LegalDocKey => (LEGAL_DOC_KEYS as readonly string[]).includes(String(k))

export interface LegalDocument {
  docKey: string
  language: 'de' | 'en'
  title: string
  contentHtml: string
  version: number
  updatedAt: string | null
  updatedBy: string | null
  isDefault: boolean
}

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', 'legal-docs.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 legal_documents (
        doc_key      TEXT NOT NULL,
        language     TEXT NOT NULL,
        title        TEXT NOT NULL,
        content_html TEXT NOT NULL,
        version      INTEGER NOT NULL DEFAULT 1,
        updated_at   TEXT NOT NULL,
        updated_by   TEXT,
        PRIMARY KEY (doc_key, language)
      );
      CREATE TABLE IF NOT EXISTS legal_document_versions (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        doc_key      TEXT NOT NULL,
        language     TEXT NOT NULL,
        version      INTEGER NOT NULL,
        title        TEXT NOT NULL,
        content_html TEXT NOT NULL,
        saved_at     TEXT NOT NULL,
        saved_by     TEXT
      );
      CREATE INDEX IF NOT EXISTS idx_ldv_doc ON legal_document_versions(doc_key, language, version);
    `)
    db = _db
    console.log('[LegalDocsDB] ✅ SQLite database initialized at:', dbPath)
    return db
  } catch (err: any) {
    initFailed = true
    console.error('[LegalDocsDB] ❌ init failed; legal documents fall back to bundled defaults:', err?.message)
    return null
  }
}

const defaultFor = (docKey: string, language: 'de' | 'en'): LegalDocument => {
  if (docKey === 'agb') {
    return {
      docKey,
      language,
      title: language === 'en' ? AGB_DEFAULT_TITLE_EN : AGB_DEFAULT_TITLE_DE,
      contentHtml: language === 'en' ? AGB_DEFAULT_HTML_EN : AGB_DEFAULT_HTML_DE,
      version: 0,
      updatedAt: null,
      updatedBy: null,
      isDefault: true
    }
  }
  if (docKey === 'avv') {
    return {
      docKey,
      language,
      title: language === 'en' ? AVV_DEFAULT_TITLE_EN : AVV_DEFAULT_TITLE_DE,
      contentHtml: language === 'en' ? AVV_DEFAULT_HTML_EN : AVV_DEFAULT_HTML_DE,
      version: 0,
      updatedAt: null,
      updatedBy: null,
      isDefault: true
    }
  }
  return { docKey, language, title: '', contentHtml: '', version: 0, updatedAt: null, updatedBy: null, isDefault: true }
}

export const normalizeLegalLanguage = (l: any): 'de' | 'en' => (String(l || '').toLowerCase().startsWith('en') ? 'en' : 'de')

/** Stored row, or the bundled default when nothing was saved yet / DB unavailable. */
export const getLegalDocument = (docKey: string, language: any): LegalDocument => {
  const lang = normalizeLegalLanguage(language)
  const d = getDb()
  if (!d) return defaultFor(docKey, lang)
  try {
    const row = d.prepare('SELECT * FROM legal_documents WHERE doc_key = ? AND language = ?').get(docKey, lang)
    if (!row) return defaultFor(docKey, lang)
    return {
      docKey,
      language: lang,
      title: row.title,
      contentHtml: row.content_html,
      version: Number(row.version) || 1,
      updatedAt: row.updated_at,
      updatedBy: row.updated_by,
      isDefault: false
    }
  } catch (err: any) {
    console.error('[LegalDocsDB] read failed:', err?.message)
    return defaultFor(docKey, lang)
  }
}

/**
 * The document to actually USE for a language: the requested language when it
 * has content, else the German original (the only authoritative version).
 */
export const getLegalDocumentWithFallback = (docKey: string, language: any): LegalDocument => {
  const doc = getLegalDocument(docKey, language)
  if (String(doc.contentHtml || '').trim()) return doc
  return getLegalDocument(docKey, 'de')
}

export const saveLegalDocument = (docKey: string, language: any, title: string, contentHtml: string, updatedBy: string): LegalDocument => {
  const lang = normalizeLegalLanguage(language)
  const d = getDb()
  if (!d) throw new Error('Legal document store unavailable')
  const now = new Date().toISOString()
  const tx = d.transaction(() => {
    const existing = d.prepare('SELECT * FROM legal_documents WHERE doc_key = ? AND language = ?').get(docKey, lang)
    if (existing) {
      d.prepare('INSERT INTO legal_document_versions (doc_key, language, version, title, content_html, saved_at, saved_by) VALUES (?, ?, ?, ?, ?, ?, ?)')
        .run(docKey, lang, existing.version, existing.title, existing.content_html, existing.updated_at, existing.updated_by)
      d.prepare('UPDATE legal_documents SET title = ?, content_html = ?, version = version + 1, updated_at = ?, updated_by = ? WHERE doc_key = ? AND language = ?')
        .run(title, contentHtml, now, updatedBy, docKey, lang)
    } else {
      d.prepare('INSERT INTO legal_documents (doc_key, language, title, content_html, version, updated_at, updated_by) VALUES (?, ?, ?, ?, 1, ?, ?)')
        .run(docKey, lang, title, contentHtml, now, updatedBy)
    }
  })
  tx()
  return getLegalDocument(docKey, lang)
}

export const listLegalDocumentVersions = (docKey: string, language: any, limit = 20) => {
  const d = getDb()
  if (!d) return []
  try {
    return d.prepare('SELECT id, version, title, saved_at AS savedAt, saved_by AS savedBy, length(content_html) AS size FROM legal_document_versions WHERE doc_key = ? AND language = ? ORDER BY version DESC LIMIT ?')
      .all(docKey, normalizeLegalLanguage(language), limit)
  } catch {
    return []
  }
}

export const getLegalDocumentVersion = (id: number) => {
  const d = getDb()
  if (!d) return null
  try {
    return d.prepare('SELECT * FROM legal_document_versions WHERE id = ?').get(id) || null
  } catch {
    return null
  }
}