import Database from 'better-sqlite3'
import { ReportSchedule } from '../models/ReportSchedule'
import cron from 'node-cron'

let db: Database.Database | null = null
const cronJobs = new Map<number, cron.ScheduledTask>()

export function initializeReportService(database: Database.Database) {
  db = database
  loadSchedules()
}

function loadSchedules() {
  if (!db) return

  const schedules = db
    .prepare('SELECT * FROM report_schedules WHERE enabled = 1')
    .all() as ReportSchedule[]

  schedules.forEach((schedule) => {
    scheduleCronJob(schedule)
  })
}

function scheduleCronJob(schedule: ReportSchedule) {
  if (!schedule.id) return

  // Cancel existing job if any
  const existingJob = cronJobs.get(schedule.id)
  if (existingJob) {
    existingJob.stop()
  }

  // Schedule based on frequency
  let cronExpression: string
  if (schedule.frequency === 'weekly') {
    cronExpression = '0 9 * * 1' // Every Monday at 9 AM
  } else {
    cronExpression = '0 9 1 * *' // First day of month at 9 AM
  }

  const job = cron.schedule(cronExpression, () => {
    executeScheduledReport(schedule)
  })

  cronJobs.set(schedule.id, job)
}

async function executeScheduledReport(schedule: ReportSchedule) {
  // This would generate and send the report
  // For now, just log it
  console.log(`Executing scheduled report: ${schedule.name}`)
  
  if (!db || !schedule.id) return

  // Update last_run
  db.prepare('UPDATE report_schedules SET last_run = ? WHERE id = ?').run(
    new Date().toISOString(),
    schedule.id
  )

  // Calculate next_run
  const nextRun = new Date()
  if (schedule.frequency === 'weekly') {
    nextRun.setDate(nextRun.getDate() + 7)
  } else {
    nextRun.setMonth(nextRun.getMonth() + 1)
  }

  db.prepare('UPDATE report_schedules SET next_run = ? WHERE id = ?').run(
    nextRun.toISOString(),
    schedule.id
  )
}

export function createSchedule(schedule: Omit<ReportSchedule, 'id' | 'createdAt'>): ReportSchedule {
  if (!db) throw new Error('Database not initialized')

  const nextRun = new Date()
  if (schedule.frequency === 'weekly') {
    nextRun.setDate(nextRun.getDate() + 7)
  } else {
    nextRun.setMonth(nextRun.getMonth() + 1)
  }

  const result = db
    .prepare(
      'INSERT INTO report_schedules (name, frequency, recipients, enabled, next_run) VALUES (?, ?, ?, ?, ?)'
    )
    .run(
      schedule.name,
      schedule.frequency,
      schedule.recipients,
      schedule.enabled ? 1 : 0,
      nextRun.toISOString()
    )

  const newSchedule: ReportSchedule = {
    ...schedule,
    id: result.lastInsertRowid as number,
    nextRun,
    createdAt: new Date(),
  }

  if (schedule.enabled) {
    scheduleCronJob(newSchedule)
  }

  return newSchedule
}

export function getSchedules(): ReportSchedule[] {
  if (!db) return []
  return db.prepare('SELECT * FROM report_schedules ORDER BY created_at DESC').all() as ReportSchedule[]
}

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

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

  if (updates.name !== undefined) {
    setClause.push('name = ?')
    values.push(updates.name)
  }
  if (updates.frequency !== undefined) {
    setClause.push('frequency = ?')
    values.push(updates.frequency)
  }
  if (updates.recipients !== undefined) {
    setClause.push('recipients = ?')
    values.push(updates.recipients)
  }
  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 report_schedules SET ${setClause.join(', ')} WHERE id = ?`).run(...values)

  const schedule = db.prepare('SELECT * FROM report_schedules WHERE id = ?').get(id) as ReportSchedule
  if (schedule && schedule.enabled) {
    scheduleCronJob(schedule)
  }

  return schedule
}

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

  const job = cronJobs.get(id)
  if (job) {
    job.stop()
    cronJobs.delete(id)
  }

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