import type { Database } from 'bun:sqlite';
import { db } from '../db';

function toIsoSql(d: Date): string {
  return d.toISOString().replace('T', ' ').replace('Z', '');
}

export class MaintenanceService {
  private static _timer: any = null;

  private static get retentionDays(): number {
    const v = Number(process.env.GROUP_MEMBERS_INACTIVE_RETENTION_DAYS);
    if (Number.isFinite(v)) return v;
    return 180; // por defecto 180 días
  }

  static start(): void {
    if (process.env.NODE_ENV === 'test' && process.env.FORCE_SCHEDULERS !== 'true') return;
    if (this.retentionDays <= 0) return;

    const intervalMs = 24 * 60 * 60 * 1000; // diario
    this._timer = setInterval(() => {
      this.cleanupInactiveMembersOnce().catch(err => {
        console.error('❌ Error en cleanup de miembros inactivos:', err);
      });
      this.reconcileAliasUsersOnce().catch(err => {
        console.error('❌ Error en reconcile de alias de usuarios:', err);
      });
    }, intervalMs);
  }

  static stop(): void {
    if (this._timer) {
      clearInterval(this._timer);
      this._timer = null;
    }
  }

  static async cleanupInactiveMembersOnce(instance: Database = db, retentionDays: number = this.retentionDays): Promise<number> {
    if (retentionDays <= 0) return 0;
    const threshold = toIsoSql(new Date(Date.now() - retentionDays * 24 * 60 * 60 * 1000));
    const res = instance.prepare(`
      DELETE FROM group_members
      WHERE is_active = 0
        AND last_seen_at < ?
    `).run(threshold);
    const deleted = Number(res?.changes || 0);
    return deleted;
  }

  /**
   * Reconciliación de usuarios: fusiona IDs alias (LID u opacos) hacia el número real
   * en todas las tablas relevantes, basándose en user_aliases.
   * Devuelve el número de alias procesados.
   */
  static async reconcileAliasUsersOnce(instance: Database = db): Promise<number> {
    try {
      const rows = instance.prepare(`SELECT alias, user_id FROM user_aliases WHERE alias != user_id`).all() as any[];
      let merged = 0;

      for (const r of rows) {
        const alias = String(r.alias);
        const real = String(r.user_id);

        instance.transaction(() => {
          const nowIso = toIsoSql(new Date());
          // Asegurar existencia del usuario real
          try {
            instance.prepare(`INSERT OR IGNORE INTO users (id, created_at, updated_at) VALUES (?, ?, ?)`)
              .run(real, nowIso, nowIso);
          } catch {}

          const updates = [
            `UPDATE tasks SET created_by = ? WHERE created_by = ?`,
            `UPDATE task_assignments SET user_id = ? WHERE user_id = ?`,
            `UPDATE task_assignments SET assigned_by = ? WHERE assigned_by = ?`,
            `UPDATE user_preferences SET user_id = ? WHERE user_id = ?`,
            `UPDATE web_tokens SET user_id = ? WHERE user_id = ?`,
            `UPDATE group_members SET user_id = ? WHERE user_id = ?`
          ];

          for (const sql of updates) {
            try {
              instance.prepare(sql).run(real, alias);
            } catch {
              // Ignorar si la tabla no existe en este despliegue
            }
          }

          // Intentar eliminar el usuario alias si ya no tiene referencias
          try {
            instance.prepare(`DELETE FROM users WHERE id = ?`).run(alias);
          } catch {}
        })();

        merged++;
      }

      return merged;
    } catch {
      // Si no existe la tabla user_aliases o hay error de DB, no hacemos nada
      return 0;
    }
  }
}
