// data/registrations.db — pending + confirmed registrations.
// Deliberately a SEPARATE file from clinics.db: the clinic database is a build artefact
// that gets deleted and rebuilt on every Klinik-Atlas refresh.
import fs from 'node:fs'
import path from 'node:path'
import crypto from 'node:crypto'
import Database from 'better-sqlite3'

// The type union, the legacy aliases and the labels live in ./registrationTypes so that
// modules without a database dependency (i18n, mail) can normalise a type too.

export type RegistrationRow = {
  id: number
  token: string
  /**
   * The registration type AS STORED. Not narrowed to RegistrationType on purpose: rows
   * written before the three types were collapsed still hold 'abgebende_klinik' /
   * 'beziehende_klinik', and nothing validates this column on read. Run it through
   * `normaliseType()` (server/utils/registrationTypes.ts) before comparing or labelling.
   */
  type: string
  status: 'pending' | 'confirmed' | 'duplicate' | 'cancelled'
  needs_manual_approval: number
  stoid: string | null
  org_name: string
  street: string
  plz: string
  city: string
  land: string
  contact_name: string
  contact_email: string
  contact_phone: string
  email_domain: string
  domain_verified: number
  /** Language the visitor registered in ('de' | 'en') — decides the mail language. */
  lang: string
  note: string
  erp_status: string
  erp_message: string
  erp_payload: string
  created_at: string
  confirmed_at: string | null
  ip: string
  user_agent: string
}

let handle: any = null
let opened = false

const resolveDbPath = (): string => {
  const configured = (useRuntimeConfig().registrationDbPath || process.env.NUXT_REGISTRATION_DB_PATH || '').trim()
  const rel = configured || 'data/registrations.db'
  return path.isAbsolute(rel) ? rel : path.resolve(process.cwd(), rel)
}

const SCHEMA = `
  CREATE TABLE IF NOT EXISTS pending_registrations (
    id                    INTEGER PRIMARY KEY AUTOINCREMENT,
    token                 TEXT NOT NULL UNIQUE,
    type                  TEXT NOT NULL,
    status                TEXT NOT NULL DEFAULT 'pending',
    needs_manual_approval INTEGER NOT NULL DEFAULT 0,
    stoid                 TEXT,
    org_name              TEXT NOT NULL DEFAULT '',
    street                TEXT NOT NULL DEFAULT '',
    plz                   TEXT NOT NULL DEFAULT '',
    city                  TEXT NOT NULL DEFAULT '',
    land                  TEXT NOT NULL DEFAULT '',
    -- NOTE: databases created before the Anrede field was dropped still carry a
    -- contact_salutation column (TEXT NOT NULL DEFAULT ''). It is no longer written and
    -- no longer read; its DEFAULT keeps inserts that omit it valid, so no migration is
    -- needed and historic values stay readable. Do not re-add it here.
    contact_name          TEXT NOT NULL DEFAULT '',
    contact_email         TEXT NOT NULL DEFAULT '',
    contact_phone         TEXT NOT NULL DEFAULT '',
    email_domain          TEXT NOT NULL DEFAULT '',
    domain_verified       INTEGER NOT NULL DEFAULT 0,
    -- Language the registration was made in. Drives the confirmation and welcome mails,
    -- so a visitor who registered in English is not written to in German. See MIGRATIONS
    -- below: live databases predate this column.
    lang                  TEXT NOT NULL DEFAULT 'de',
    note                  TEXT NOT NULL DEFAULT '',
    erp_status            TEXT NOT NULL DEFAULT '',
    erp_message           TEXT NOT NULL DEFAULT '',
    erp_payload           TEXT NOT NULL DEFAULT '',
    created_at            TEXT NOT NULL,
    confirmed_at          TEXT,
    ip                    TEXT NOT NULL DEFAULT '',
    user_agent            TEXT NOT NULL DEFAULT ''
  );
  CREATE INDEX IF NOT EXISTS idx_pending_email  ON pending_registrations(contact_email);
  CREATE INDEX IF NOT EXISTS idx_pending_status ON pending_registrations(status);
  -- A Standort may hold exactly one live registration (pending or confirmed).
  CREATE UNIQUE INDEX IF NOT EXISTS ux_pending_stoid
    ON pending_registrations(stoid)
    WHERE stoid IS NOT NULL AND status IN ('pending', 'confirmed');
`

/**
 * Additive column migrations for databases that already exist.
 *
 * `CREATE TABLE IF NOT EXISTS` does nothing to a table that is already there, so a new
 * column never reaches the LIVE registrations.db — which holds real, unrecoverable data
 * and deliberately survives every deploy (see deploy/README.md). Each entry is applied
 * only when the column is genuinely missing, and a failure is logged rather than thrown:
 * an un-migrated column must degrade one feature, never take the site down.
 */
const COLUMN_MIGRATIONS: Array<[column: string, ddl: string]> = [
  ['lang', `ALTER TABLE pending_registrations ADD COLUMN lang TEXT NOT NULL DEFAULT 'de'`],
]

const migrate = (db: any) => {
  let existing: Set<string>
  try {
    existing = new Set(db.prepare('PRAGMA table_info(pending_registrations)').all().map((c: any) => c.name))
  } catch (err: any) {
    console.error('[blut24] Schema konnte nicht gelesen werden:', err?.message || err)
    return
  }
  for (const [column, ddl] of COLUMN_MIGRATIONS) {
    if (existing.has(column)) continue
    try {
      db.exec(ddl)
      console.info(`[blut24] registrations.db: Spalte "${column}" ergänzt.`)
    } catch (err: any) {
      console.error(`[blut24] Spalte "${column}" konnte nicht ergänzt werden:`, err?.message || err)
    }
  }
}

export const getRegistrationDb = (): any => {
  if (opened && handle) return handle
  opened = true
  const file = resolveDbPath()
  try {
    fs.mkdirSync(path.dirname(file), { recursive: true })
    handle = new Database(file)
    handle.pragma('journal_mode = WAL')
    // In production PM2 runs this app in cluster mode, so several processes hold
    // their own connection to this file. WAL lets them read concurrently, but
    // SQLite still allows only ONE writer at a time — without a busy timeout a
    // second concurrent registration would fail immediately with SQLITE_BUSY.
    // 5s is far beyond any write here (single-row inserts) and keeps the failure
    // mode "slightly slower" instead of "lost registration".
    handle.pragma('busy_timeout = 5000')
    handle.exec(SCHEMA)
    migrate(handle)
  } catch (err: any) {
    console.error('[blut24] registrations.db konnte nicht geöffnet werden:', err?.message || err)
    handle = null
  }
  return handle
}

export const newToken = () => crypto.randomBytes(32).toString('base64url')

/** Live registration for a Standort, if any (used for the duplicate guard). */
export const findLiveByStoid = (stoid: string): RegistrationRow | null => {
  const db = getRegistrationDb()
  if (!db || !stoid) return null
  return db.prepare(`
    SELECT * FROM pending_registrations
    WHERE stoid = ? AND status IN ('pending','confirmed')
    ORDER BY id DESC LIMIT 1
  `).get(String(stoid)) || null
}

/** Live registration for a contact address (couriers have no STOID to key on). */
export const findLiveByEmail = (email: string): RegistrationRow | null => {
  const db = getRegistrationDb()
  if (!db || !email) return null
  return db.prepare(`
    SELECT * FROM pending_registrations
    WHERE contact_email = ? AND status IN ('pending','confirmed')
    ORDER BY id DESC LIMIT 1
  `).get(String(email).toLowerCase()) || null
}

export const findByToken = (token: string): RegistrationRow | null => {
  const db = getRegistrationDb()
  if (!db || !token) return null
  return db.prepare('SELECT * FROM pending_registrations WHERE token = ?').get(String(token)) || null
}

/**
 * Inserts a registration. Returns null on any failure — including the UNIQUE index on
 * stoid firing when two submissions for the same Standort race each other. Callers must
 * treat null as "not stored" and answer neutrally.
 */
export const insertRegistration = (data: Partial<RegistrationRow>): RegistrationRow | null => {
  const db = getRegistrationDb()
  if (!db) return null
  const token = data.token || newToken()
  try {
    return insertRow(db, token, data)
  } catch (err: any) {
    console.error('[blut24] Registrierung konnte nicht gespeichert werden:', err?.message || err)
    return null
  }
}

const insertRow = (db: any, token: string, data: Partial<RegistrationRow>): RegistrationRow | null => {
  const info = db.prepare(`
    INSERT INTO pending_registrations
      (token, type, status, needs_manual_approval, stoid, org_name, street, plz, city, land,
       contact_name, contact_email, contact_phone, email_domain,
       domain_verified, lang, note, created_at, ip, user_agent)
    VALUES
      (@token, @type, @status, @needs_manual_approval, @stoid, @org_name, @street, @plz, @city, @land,
       @contact_name, @contact_email, @contact_phone, @email_domain,
       @domain_verified, @lang, @note, @created_at, @ip, @user_agent)
  `).run({
    token,
    type: data.type,
    status: data.status || 'pending',
    needs_manual_approval: data.needs_manual_approval ? 1 : 0,
    stoid: data.stoid ?? null,
    org_name: data.org_name || '',
    street: data.street || '',
    plz: data.plz || '',
    city: data.city || '',
    land: data.land || '',
    contact_name: data.contact_name || '',
    contact_email: (data.contact_email || '').toLowerCase(),
    contact_phone: data.contact_phone || '',
    email_domain: data.email_domain || '',
    domain_verified: data.domain_verified ? 1 : 0,
    lang: data.lang === 'en' ? 'en' : 'de',
    note: data.note || '',
    created_at: new Date().toISOString(),
    ip: data.ip || '',
    user_agent: (data.user_agent || '').slice(0, 300),
  })
  return db.prepare('SELECT * FROM pending_registrations WHERE id = ?').get(info.lastInsertRowid) || null
}

export const markConfirmed = (id: number) => {
  const db = getRegistrationDb()
  if (!db) return
  db.prepare(`UPDATE pending_registrations SET status = 'confirmed', confirmed_at = ? WHERE id = ?`)
    .run(new Date().toISOString(), id)
}

export const setErpResult = (id: number, status: string, message: string, payload: any = null) => {
  const db = getRegistrationDb()
  if (!db) return
  let serialised = ''
  try { serialised = payload ? JSON.stringify(payload).slice(0, 20000) : '' } catch { serialised = '' }
  db.prepare('UPDATE pending_registrations SET erp_status = ?, erp_message = ?, erp_payload = ? WHERE id = ?')
    .run(String(status || ''), String(message || '').slice(0, 2000), serialised, id)
}