import type { Database } from 'bun:sqlite';
import { toIsoSqlUTC } from '../../utils/datetime';

export type CleanupOptions = {
  retentionDaysSent: number;
  retentionDaysFailed: number;
  batchSize: number;
  optimize: boolean;
  vacuum: boolean;
  vacuumEveryNRuns: number;
  cleanupRunCount: number;
};

export async function runCleanupOnce(
  db: Database,
  opts: CleanupOptions,
  now: Date = new Date()
): Promise<{ deletedSent: number; deletedFailed: number; totalDeleted: number; nextCleanupRunCount: number }> {
  const msPerDay = 24 * 60 * 60 * 1000;
  const sentThresholdIso = toIsoSqlUTC(new Date(now.getTime() - opts.retentionDaysSent * msPerDay));
  const failedThresholdIso = toIsoSqlUTC(new Date(now.getTime() - opts.retentionDaysFailed * msPerDay));

  const cleanStatus = (status: 'sent' | 'failed', thresholdIso: string, batch: number): number => {
    let deleted = 0;
    const selectStmt = db.prepare(`
      SELECT id
      FROM response_queue
      WHERE status = ? AND updated_at < ?
      ORDER BY updated_at
      LIMIT ?
    `);

    while (true) {
      const rows = selectStmt.all(status, thresholdIso, batch) as Array<{ id: number }>;
      if (!rows || rows.length === 0) break;

      const ids = rows.map((r) => r.id);
      const placeholders = ids.map(() => '?').join(',');
      db.prepare(`DELETE FROM response_queue WHERE id IN (${placeholders})`).run(...ids);
      deleted += ids.length;

      if (rows.length < batch) break;
    }
    return deleted;
  };

  const deletedSent = cleanStatus('sent', sentThresholdIso, opts.batchSize);
  const deletedFailed = cleanStatus('failed', failedThresholdIso, opts.batchSize);
  const totalDeleted = deletedSent + deletedFailed;

  // Mantenimiento ligero tras limpieza
  if (opts.optimize && totalDeleted > 0) {
    try {
      db.exec('PRAGMA optimize;');
    } catch (e) {
      console.warn('PRAGMA optimize failed:', e);
    }
  }

  // VACUUM opcional
  let nextCleanupRunCount = opts.cleanupRunCount;
  if (opts.vacuum && totalDeleted > 0) {
    nextCleanupRunCount++;
    if (nextCleanupRunCount % Math.max(1, opts.vacuumEveryNRuns) === 0) {
      try {
        db.exec('VACUUM;');
      } catch (e) {
        console.warn('VACUUM failed:', e);
      }
    }
  }

  return { deletedSent, deletedFailed, totalDeleted, nextCleanupRunCount };
}
