import Database from 'better-sqlite3'
import { sendReportEmail } from './emailService'

export interface Alert {
  id?: number
  type: 'revenue_drop' | 'order_drop' | 'customer_drop' | 'custom'
  threshold: number
  enabled: boolean
  lastTriggered?: Date
  createdAt?: Date
}

let db: Database.Database | null = null

export function initializeAlertService(database: Database.Database) {
  db = database
  createAlertsTable()
}

function createAlertsTable() {
  if (!db) return

  db.exec(`
    CREATE TABLE IF NOT EXISTS alerts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      type TEXT NOT NULL,
      threshold REAL NOT NULL,
      enabled INTEGER DEFAULT 1,
      last_triggered TEXT,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
  `)
}

export function createAlert(alert: Omit<Alert, 'id' | 'createdAt'>): Alert {
  if (!db) throw new Error('Database not initialized')

  const result = db
    .prepare('INSERT INTO alerts (type, threshold, enabled) VALUES (?, ?, ?)')
    .run(alert.type, alert.threshold, alert.enabled ? 1 : 0)

  return {
    ...alert,
    id: result.lastInsertRowid as number,
    createdAt: new Date(),
  }
}

export function getAlerts(): Alert[] {
  if (!db) return []

  return db
    .prepare('SELECT * FROM alerts ORDER BY created_at DESC')
    .all()
    .map((row: any) => ({
      id: row.id,
      type: row.type,
      threshold: row.threshold,
      enabled: row.enabled === 1,
      lastTriggered: row.last_triggered ? new Date(row.last_triggered) : undefined,
      createdAt: new Date(row.created_at),
    }))
}

export function updateAlert(id: number, updates: Partial<Omit<Alert, 'id' | 'createdAt'>>): Alert | null {
  if (!db) return null

  const setClause: string[] = []
  const values: any[] = []

  if (updates.type !== undefined) {
    setClause.push('type = ?')
    values.push(updates.type)
  }
  if (updates.threshold !== undefined) {
    setClause.push('threshold = ?')
    values.push(updates.threshold)
  }
  if (updates.enabled !== undefined) {
    setClause.push('enabled = ?')
    values.push(updates.enabled ? 1 : 0)
  }

  if (setClause.length === 0) return null

  values.push(id)
  db.prepare(`UPDATE alerts SET ${setClause.join(', ')} WHERE id = ?`).run(...values)

  return db.prepare('SELECT * FROM alerts WHERE id = ?').get(id) as Alert
}

export function deleteAlert(id: number): boolean {
  if (!db) return false

  const result = db.prepare('DELETE FROM alerts WHERE id = ?').run(id)
  return result.changes > 0
}

export async function checkAlerts(
  currentMetrics: {
    revenue?: number
    orders?: number
    customers?: number
  },
  previousMetrics: {
    revenue?: number
    orders?: number
    customers?: number
  },
  recipients: string[] = []
): Promise<void> {
  if (!db) return

  const alerts = getAlerts().filter((a) => a.enabled)

  for (const alert of alerts) {
    let shouldTrigger = false
    let message = ''

    switch (alert.type) {
      case 'revenue_drop':
        if (previousMetrics.revenue && currentMetrics.revenue) {
          const drop = ((previousMetrics.revenue - currentMetrics.revenue) / previousMetrics.revenue) * 100
          if (drop >= alert.threshold) {
            shouldTrigger = true
            message = `Alerta: Queda de receita de ${drop.toFixed(1)}% (threshold: ${alert.threshold}%)`
          }
        }
        break

      case 'order_drop':
        if (previousMetrics.orders && currentMetrics.orders) {
          const drop = ((previousMetrics.orders - currentMetrics.orders) / previousMetrics.orders) * 100
          if (drop >= alert.threshold) {
            shouldTrigger = true
            message = `Alerta: Queda de pedidos de ${drop.toFixed(1)}% (threshold: ${alert.threshold}%)`
          }
        }
        break

      case 'customer_drop':
        if (previousMetrics.customers && currentMetrics.customers) {
          const drop = ((previousMetrics.customers - currentMetrics.customers) / previousMetrics.customers) * 100
          if (drop >= alert.threshold) {
            shouldTrigger = true
            message = `Alerta: Queda de clientes de ${drop.toFixed(1)}% (threshold: ${alert.threshold}%)`
          }
        }
        break
    }

    if (shouldTrigger && alert.id) {
      // Update last triggered
      db.prepare('UPDATE alerts SET last_triggered = ? WHERE id = ?').run(
        new Date().toISOString(),
        alert.id
      )

      // Send email if recipients provided
      if (recipients.length > 0) {
        try {
          await sendReportEmail(
            recipients,
            'Alerta de Vendas - 100mlstore',
            `<h2>${message}</h2><p>Métricas atuais vs anteriores detectaram uma queda significativa.</p>`
          )
        } catch (error) {
          console.error('Failed to send alert email:', error)
        }
      }
    }
  }
}