import mysql from 'mysql2/promise'

let pool: mysql.Pool | null = null
let tableReady = false

function getPool() {
  if (!pool) {
    pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      waitForConnections: true,
      connectionLimit: 4,
    })
  }
  return pool
}

const TABLE = '`vystrikejto_cz`.`email_log`'

async function ensureTable() {
  if (tableReady) return
  await getPool().execute(`
    CREATE TABLE IF NOT EXISTS ${TABLE} (
      id INT AUTO_INCREMENT PRIMARY KEY,
      source VARCHAR(20) NOT NULL,
      kind VARCHAR(80) NULL,
      recipient VARCHAR(255) NOT NULL,
      subject VARCHAR(500) NOT NULL,
      html MEDIUMTEXT NULL,
      text TEXT NULL,
      order_id INT NULL,
      sent_by VARCHAR(255) NULL,
      status VARCHAR(20) NOT NULL DEFAULT 'success',
      error TEXT NULL,
      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_email_log_created (created_at),
      INDEX idx_email_log_recipient (recipient),
      INDEX idx_email_log_source (source),
      INDEX idx_email_log_order (order_id)
    )
  `)
  tableReady = true
}

function inferKind(subject: string, explicit?: string | null) {
  if (explicit) return explicit
  const value = (subject || '').toLowerCase()
  if (value.includes('stříkáme') || value.includes('strikame')) return 'work-started'
  if (value.includes('na cestě') || value.includes('na ceste')) return 'shipped'
  if (value.includes('platb')) return 'payment'
  if (value.includes('rekapitulace') || value.includes('objednávk') || value.includes('objednavk')) return 'order'
  if (value.includes('ověření') || value.includes('overeni') || value.includes('verify')) return 'verify-email'
  if (value.includes('heslo') || value.includes('password')) return 'password'
  if (value.includes('poukaz') || value.includes('voucher')) return 'voucher'
  return 'other'
}

export async function logOutgoingEmail(entry: {
  source?: string
  kind?: string | null
  recipient: string
  subject: string
  html?: string | null
  text?: string | null
  orderId?: number | null
  sentBy?: string | null
  status: 'success' | 'failed'
  error?: string | null
}) {
  try {
    await ensureTable()
    await getPool().execute(
      `INSERT INTO ${TABLE}
        (source, kind, recipient, subject, html, text, order_id, sent_by, status, error)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        entry.source || 'shop',
        inferKind(entry.subject, entry.kind),
        entry.recipient,
        entry.subject,
        entry.html || null,
        entry.text || null,
        entry.orderId || null,
        entry.sentBy || null,
        entry.status,
        entry.error || null,
      ]
    )
  } catch (error) {
    console.error('Failed to log outgoing email:', error)
  }
}