/**
 * Semilla enriquecida de datos de demo para desarrollo.
 * Inserta usuarios, grupos (allowed/pending), membresías, preferencias y un set amplio de tareas.
 * Idempotente: solo ejecuta si la tabla tasks está vacía.
 */
function toIsoYmd(d: Date): string {
  const yyyy = d.getUTCFullYear();
  const mm = String(d.getUTCMonth() + 1).padStart(2, '0');
  const dd = String(d.getUTCDate()).padStart(2, '0');
  return `${yyyy}-${mm}-${dd}`;
}
function addDays(base: Date, days: number): Date {
  return new Date(base.getTime() + days * 24 * 3600 * 1000);
}
import { toIsoSqlUTC } from './datetime';

export async function seedDev(db: any, defaultUser: string): Promise<void> {
  try { db.exec(`PRAGMA foreign_keys = ON;`); } catch {}
  // Si ya hay tareas, asumimos BD poblada
  try {
    const row = db.prepare(`SELECT COUNT(*) AS c FROM tasks`).get() as any;
    if (row && Number(row.c || 0) > 0) return;
  } catch {}

  const now = new Date();
  const today = toIsoYmd(now);
  const tomorrow = toIsoYmd(addDays(now, 1));
  const nextWeek = toIsoYmd(addDays(now, 7));
  const overdue = toIsoYmd(addDays(now, -1));
  const inTwoDays = toIsoYmd(addDays(now, 2));
  const noDate = null;

  // Usuarios (incluye el de desarrollo aunque no sea numérico)
  const DEV = (defaultUser || '').trim();
  const U1 = DEV; // usuario de desarrollo (puede no ser numérico; lo insertamos igualmente)
  const U2 = '34600123456';
  const U3 = '5550001111';
  const U4 = '600123456';
  const U5 = '34987654321';
  const users = [U1, U2, U3, U4, U5].filter(Boolean);

  const insertUser = db.prepare(`
    INSERT OR IGNORE INTO users (id, first_seen, last_seen)
    VALUES (?, strftime('%Y-%m-%d %H:%M:%f','now'), strftime('%Y-%m-%d %H:%M:%f','now'))
  `);
  for (const u of users) insertUser.run(u);

  // Grupos (IDs tipo JID) y estados
  const G_FAM = 'g-familia@g.us';
  const G_TRA = 'g-trabajo@g.us';
  const G_VOL = 'g-voluntariado@g.us';
  const G_COM = 'g-compras@g.us';
  const G_VAR = 'g-varios@g.us';
  const groups: Array<{ id: string; name: string; allowed: 'allowed' | 'pending' | 'blocked' }> = [
    { id: G_FAM, name: 'Familia', allowed: 'allowed' },
    { id: G_TRA, name: 'Trabajo', allowed: 'allowed' },
    { id: G_VOL, name: 'Voluntariado', allowed: 'allowed' },
    { id: G_COM, name: 'Compras', allowed: 'allowed' },    // allowed pero sin membresía del usuario DEV
    { id: G_VAR, name: 'Varios', allowed: 'pending' }      // pendiente/bloqueado para validar gating
  ];

  const insertGroup = db.prepare(`
    INSERT OR IGNORE INTO groups (id, community_id, name, active, last_verified)
    VALUES (?, 'comm-dev', ?, 1, strftime('%Y-%m-%d %H:%M:%f','now'))
  `);
  const insertAllowed = db.prepare(`
    INSERT OR IGNORE INTO allowed_groups (group_id, label, status, discovered_at, updated_at, discovered_by)
    VALUES (?, ?, ?, strftime('%Y-%m-%d %H:%M:%f','now'), strftime('%Y-%m-%d %H:%M:%f','now'), NULL)
  `);
  for (const g of groups) {
    insertGroup.run(g.id, g.name);
    try { insertAllowed.run(g.id, g.name, g.allowed); } catch {}
  }

  // Membresías activas: el usuario DEV en Familia, Trabajo, Voluntariado; otros usuarios repartidos
  const insertMember = db.prepare(`
    INSERT OR REPLACE INTO group_members (group_id, user_id, is_admin, is_active, first_seen_at, last_seen_at)
    VALUES (?, ?, 0, 1, strftime('%Y-%m-%d %H:%M:%f','now'), strftime('%Y-%m-%d %H:%M:%f','now'))
  `);
  for (const gid of [G_FAM, G_TRA, G_VOL]) insertMember.run(gid, U1);
  // Otros usuarios en distintos grupos para facilitar múltiples responsables
  insertMember.run(G_FAM, U2);
  insertMember.run(G_FAM, U3);
  insertMember.run(G_TRA, U3);
  insertMember.run(G_TRA, U4);
  insertMember.run(G_VOL, U5);
  // Compras: allowed pero sin membresía del usuario DEV (solo U2), para validar gating
  insertMember.run(G_COM, U2);

  // Preferencias del usuario DEV
  try {
    db.prepare(`
      INSERT OR REPLACE INTO user_preferences (user_id, reminder_freq, reminder_time, last_reminded_on, updated_at)
      VALUES (?, 'daily', '08:30', NULL, strftime('%Y-%m-%d %H:%M:%f','now'))
    `).run(U1);
  } catch {}

  // Insertadores
  const insertTask = db.prepare(`
    INSERT INTO tasks (description, due_date, group_id, created_by, completed, completed_at, completed_by)
    VALUES (?, ?, ?, ?, COALESCE(?, 0), ?, ?)
  `);
  const assignStmt = db.prepare(`
    INSERT OR IGNORE INTO task_assignments (task_id, user_id, assigned_by)
    VALUES (?, ?, ?)
  `);

  // Helpers para completadas
  const completedRecent = toIsoSqlUTC(addDays(now, 0));          // ahora
  const completed2hAgo = toIsoSqlUTC(new Date(Date.now() - 2 * 3600 * 1000));
  const completed12hAgo = toIsoSqlUTC(new Date(Date.now() - 12 * 3600 * 1000));
  const completed48hAgo = toIsoSqlUTC(new Date(Date.now() - 48 * 3600 * 1000));
  const completed72hAgo = toIsoSqlUTC(new Date(Date.now() - 72 * 3600 * 1000));

  type Spec = {
    desc: string;
    due: string | null;
    group: string | null;
    createdBy: string;
    completed?: 0 | 1;
    completedAt?: string | null;
    completedBy?: string | null;
    assignees?: string[]; // responsables
  };

  const specs: Spec[] = [
    // Familia (mezcla: sin responsables, 1 responsable, múltiples, completadas reciente/antigua)
    { desc: 'Compra semanal para la casa', due: today, group: G_FAM, createdBy: U1, assignees: [] },
    { desc: 'Llevar coche al taller', due: tomorrow, group: G_FAM, createdBy: U1, assignees: [U1] },
    { desc: 'Organizar cumpleaños (lista de invitados y tarta)', due: nextWeek, group: G_FAM, createdBy: U2, assignees: [U1, U2] },
    { desc: 'Revisar facturas de luz y gas', due: overdue, group: G_FAM, createdBy: U1, assignees: [], completed: 0 },
    { desc: 'Pedir cita pediatra', due: inTwoDays, group: G_FAM, createdBy: U1, assignees: [U3] },
    { desc: 'Sacar basura orgánica', due: noDate, group: G_FAM, createdBy: U3, assignees: [], completed: 1, completedAt: completed2hAgo, completedBy: U1 },
    { desc: 'Regar plantas del balcón (abundante agua)', due: today, group: G_FAM, createdBy: U1, assignees: [U1, U3] },

    // Trabajo
    { desc: 'Preparar informe trimestral de ventas', due: nextWeek, group: G_TRA, createdBy: U3, assignees: [U1] },
    { desc: 'Reunión con proveedor clave', due: tomorrow, group: G_TRA, createdBy: U4, assignees: [], completed: 1, completedAt: completed12hAgo, completedBy: U1 },
    { desc: 'Actualizar tablero de tareas del sprint', due: today, group: G_TRA, createdBy: U1, assignees: [U1, U4] },
    { desc: 'Revisión de PRs acumuladas', due: overdue, group: G_TRA, createdBy: U1, assignees: [U3] },
    { desc: 'Definir OKRs del próximo trimestre', due: noDate, group: G_TRA, createdBy: U1, assignees: [] },
    { desc: 'Publicar release menor (v1.0.1)', due: inTwoDays, group: G_TRA, createdBy: U2, assignees: [U1], completed: 1, completedAt: completed48hAgo, completedBy: U2 },

    // Voluntariado
    { desc: 'Clasificar donaciones de ropa', due: today, group: G_VOL, createdBy: U5, assignees: [] },
    { desc: 'Coordinar recogida de alimentos', due: tomorrow, group: G_VOL, createdBy: U1, assignees: [U1, U5] },
    { desc: 'Llamar a nuevos voluntarios (lista A–M)', due: nextWeek, group: G_VOL, createdBy: U1, assignees: [U1] },
    { desc: 'Actualizar listado de familias beneficiarias', due: overdue, group: G_VOL, createdBy: U5, assignees: [], completed: 1, completedAt: completed72hAgo, completedBy: U5 },
    { desc: 'Solicitar permiso para evento solidario', due: noDate, group: G_VOL, createdBy: U1, assignees: [] },

    // Compras (allowed pero sin membresía del usuario DEV)
    { desc: 'Comprar detergente y suavizante', due: today, group: G_COM, createdBy: U2, assignees: [U2] },
    { desc: 'Reponer comida para mascotas', due: tomorrow, group: G_COM, createdBy: U2, assignees: [] },
    { desc: 'Comparar precios de frutas y verduras', due: nextWeek, group: G_COM, createdBy: U2, assignees: [U2], completed: 1, completedAt: completed2hAgo, completedBy: U2 },
    { desc: 'Planificar compra mensual a granel', due: noDate, group: G_COM, createdBy: U2, assignees: [] },

    // Personales (group_id NULL)
    { desc: 'Pagar recibo del móvil', due: overdue, group: null, createdBy: U1, assignees: [U1] },
    { desc: 'Hacer copia de seguridad del portátil', due: today, group: null, createdBy: U1, assignees: [], completed: 1, completedAt: completed2hAgo, completedBy: U1 },
    { desc: 'Llamar al banco para aclarar comisión', due: tomorrow, group: null, createdBy: U1, assignees: [U1] },
    { desc: 'Leer artículo técnico pendiente', due: noDate, group: null, createdBy: U1, assignees: [] },
    { desc: 'Renovar DNI (pedir cita)', due: inTwoDays, group: null, createdBy: U1, assignees: [U1] },
    { desc: 'Terminar curso online de accesibilidad', due: nextWeek, group: null, createdBy: U1, assignees: [U1, U3] },
    { desc: 'Ordenar fotos antiguas en la nube (muchas carpetas)', due: noDate, group: null, createdBy: U1, assignees: [], completed: 1, completedAt: completed48hAgo, completedBy: U1 },

    // Más casos para densidad y mezcla (≈ 30–35 total)
    { desc: 'Preparar lista de la compra grande', due: tomorrow, group: G_FAM, createdBy: U1, assignees: [U1, U2] },
    { desc: 'Pintar habitación pequeña', due: nextWeek, group: G_FAM, createdBy: U2, assignees: [] },
    { desc: 'Plan de pruebas regresivas', due: today, group: G_TRA, createdBy: U1, assignees: [U4] },
    { desc: 'Reunión semanal con equipo', due: today, group: G_TRA, createdBy: U3, assignees: [U1, U3] },
    { desc: 'Retirar material donado del almacén', due: tomorrow, group: G_VOL, createdBy: U5, assignees: [] },
    { desc: 'Preparar carteles del evento solidario (texto largo y revisión)', due: noDate, group: G_VOL, createdBy: U1, assignees: [U1] }
  ];

  // Transacción para insertar todo
  db.transaction(() => {
    for (const t of specs) {
      const res = insertTask.run(
        t.desc,
        t.due ?? null,
        t.group ?? null,
        t.createdBy,
        t.completed ?? 0,
        t.completed ? (t.completedAt ?? completedRecent) : null,
        t.completed ? (t.completedBy ?? t.createdBy) : null
      );
      const id = Number((res as any)?.lastInsertRowid ?? 0);
      if (id > 0 && Array.isArray(t.assignees)) {
        const seen = new Set<string>();
        for (const uid of t.assignees) {
          if (uid && !seen.has(uid)) {
            seen.add(uid);
            assignStmt.run(id, uid, t.createdBy);
          }
        }
      }
    }
  })();
}
