import pool from '../db';

const DEFAULT_SLEEP_MS = 6 * 60 * 60 * 1000;
const DEFAULT_PRECOMPUTED_RETENTION_DAYS = 7;
const DEFAULT_FORECAST_CACHE_RETENTION_DAYS = 30;
const DEFAULT_EVENTS_RETENTION_DAYS = 30;

function getPositiveIntegerEnv(name: string, fallback: number): number {
  const raw = Number.parseInt(process.env[name] || '', 10);
  return Number.isFinite(raw) && raw > 0 ? raw : fallback;
}

export async function purgeExpiredPrecomputedAssets(now: Date = new Date()): Promise<number> {
  const retentionDays = getPositiveIntegerEnv('PRECOMPUTED_RETENTION_DAYS', DEFAULT_PRECOMPUTED_RETENTION_DAYS);
  const { rows } = await pool.query(
    `WITH deleted AS (
       DELETE FROM rm_forecast_precomputed fp
       WHERE fp.status = 'EXPIRED'
         AND fp.expires_at < $1::timestamptz - ($2 || ' days')::interval
       RETURNING fp.id
     )
     SELECT COUNT(*)::int AS count FROM deleted`,
    [now.toISOString(), String(retentionDays)],
  );

  return Number(rows[0]?.count || 0);
}

export async function purgeOldForecastCache(now: Date = new Date()): Promise<number> {
  const retentionDays = getPositiveIntegerEnv('FORECAST_CACHE_RETENTION_DAYS', DEFAULT_FORECAST_CACHE_RETENTION_DAYS);
  const { rows } = await pool.query(
    `WITH deleted AS (
       DELETE FROM rm_forecast_cache fc
       WHERE fc.expires_at < $1::timestamptz
         AND fc.starts_at < $1::timestamptz - ($2 || ' days')::interval
         AND NOT EXISTS (
           SELECT 1
           FROM rm_forecast_accuracy_v2 fa
           WHERE fa.forecast_id = fc.id
         )
       RETURNING fc.id
     )
     SELECT COUNT(*)::int AS count FROM deleted`,
    [now.toISOString(), String(retentionDays)],
  );

  return Number(rows[0]?.count || 0);
}

export async function purgeOldEndedEvents(now: Date = new Date()): Promise<number> {
  const retentionDays = getPositiveIntegerEnv('EVENTS_RETENTION_DAYS', DEFAULT_EVENTS_RETENTION_DAYS);
  const { rows } = await pool.query(
    `WITH deleted AS (
       DELETE FROM rm_events e
       WHERE e.status = 'ended'
         AND e.starts_at < $1::timestamptz - ($2 || ' days')::interval
         AND NOT EXISTS (
           SELECT 1
           FROM rm_user_picks up
           WHERE up.event_id = e.event_id
         )
         AND NOT EXISTS (
           SELECT 1
           FROM rm_forecast_cache fc
           WHERE fc.event_id = e.event_id
         )
         AND NOT EXISTS (
           SELECT 1
           FROM rm_archived_forecasts af
           WHERE af.event_id = e.event_id
         )
         AND NOT EXISTS (
           SELECT 1
           FROM rm_forecast_precomputed fp
           WHERE fp.event_id = e.event_id
             AND (
               fp.status = 'ACTIVE'
               OR (fp.status = 'STALE' AND fp.expires_at > $1::timestamptz)
             )
         )
       RETURNING e.event_id
     )
     SELECT COUNT(*)::int AS count FROM deleted`,
    [now.toISOString(), String(retentionDays)],
  );

  return Number(rows[0]?.count || 0);
}

export async function runDataRetention(now: Date = new Date()): Promise<{
  expiredPrecomputed: number;
  oldForecastCache: number;
  oldEndedEvents: number;
}> {
  const expiredPrecomputed = await purgeExpiredPrecomputedAssets(now);
  const oldForecastCache = await purgeOldForecastCache(now);
  const oldEndedEvents = await purgeOldEndedEvents(now);

  return {
    expiredPrecomputed,
    oldForecastCache,
    oldEndedEvents,
  };
}

let shuttingDown = false;

pool.on('error', (err) => {
  console.error('[data-retention] DB pool error:', err);
});

process.on('SIGTERM', () => { shuttingDown = true; });
process.on('SIGINT', () => { shuttingDown = true; });

async function main() {
  while (!shuttingDown) {
    try {
      const result = await runDataRetention();
      console.log(
        `[data-retention] Removed ${result.expiredPrecomputed} expired precomputed rows, ${result.oldForecastCache} stale cache rows, ${result.oldEndedEvents} ended event rows`,
      );
    } catch (err: any) {
      console.error('[data-retention] Cycle error:', err?.message || err);
    }

    if (shuttingDown) break;
    await new Promise((resolve) => setTimeout(resolve, DEFAULT_SLEEP_MS));
  }

  await pool.end().catch(() => {});
}

if (process.env.VITEST !== 'true') {
  main().catch((err) => {
    console.error('[data-retention] Fatal error:', err);
    process.exit(1);
  });
}