const { Client } = require('./node_modules/pg');

const HORUX_CONN = {
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'ZxHMrmnwanvLfLDdNJdRthFjWF2Lj1Rb',
  database: 'horux360',
};

const todayStart = new Date('2026-08-26T06:00:00.000Z'); // 00:00 CDMX approx
const todayEnd = new Date('2026-08-27T06:00:00.000Z');

async function query(client, sql, params = []) {
  const res = await client.query(sql, params);
  return res.rows;
}

async function main() {
  const client = new Client(HORUX_CONN);
  await client.connect();

  // Tenants activos y su FIEL legacy (tenant-level, Horux 360 antiguo)
  // Se excluyen cuentas demo/ficticias configuradas en SAT_SYNC_EXCLUDED_TENANTS.
  const excluded = new Set(
    (process.env.SAT_SYNC_EXCLUDED_TENANTS || '')
      .split(',')
      .map(s => s.trim().toUpperCase())
      .filter(Boolean)
  );

  const tenants = await query(client, `
    SELECT t.id, t.rfc, t.nombre, t.database_name,
      EXISTS (
        SELECT 1 FROM fiel_credentials fc
        WHERE fc.tenant_id = t.id AND fc.is_active = true AND fc.valid_until > NOW()
      ) AS has_legacy_fiel
    FROM tenants t
    WHERE t.active = true
    ORDER BY t.rfc
  `);

  const visibleTenants = tenants.filter(t => !excluded.has(t.id) && !excluded.has(t.rfc.toUpperCase()));

  // Daily jobs de hoy
  const dailyJobs = await query(client, `
    SELECT tenant_id, contribuyente_id, cfdis_found, status, created_at
    FROM sat_sync_jobs
    WHERE type = 'daily'
      AND created_at >= $1
      AND created_at < $2
  `, [todayStart, todayEnd]);

  const dailyByTenantContrib = new Map();
  for (const j of dailyJobs) {
    const key = j.contribuyente_id ? `${j.tenant_id}:${j.contribuyente_id}` : `${j.tenant_id}:`;
    dailyByTenantContrib.set(key, j);
  }

  const hadDaily = [];
  const missing = [];
  const noFiel = [];

  for (const t of visibleTenants) {
    if (!t.database_name) {
      // Legacy tenant-wide (sin contribuyentes)
      if (!t.has_legacy_fiel) {
        noFiel.push({ tenantRfc: t.rfc, tenantName: t.nombre });
        continue;
      }
      const key = `${t.id}:`;
      const job = dailyByTenantContrib.get(key);
      if (job) {
        hadDaily.push({ tenantRfc: t.rfc, tenantName: t.nombre, contribuyenteId: null, contribuyenteRfc: null, contribuyenteName: null, cfdisFound: job.cfdis_found, status: job.status });
      } else {
        missing.push({ tenantRfc: t.rfc, tenantName: t.nombre, contribuyenteId: null, contribuyenteRfc: null, contribuyenteName: null, reason: 'tenant legacy con FIEL pero sin daily' });
      }
      continue;
    }

    // Conectar a la BD del tenant
    const tenantClient = new Client({ ...HORUX_CONN, database: t.database_name });
    try {
      await tenantClient.connect();

      // Todos los contribuyentes con info de FIEL (per-contribuyente o legacy)
      const contribuyentes = await query(tenantClient, `
        SELECT c.entidad_id, c.rfc, eg.nombre AS razon_social,
          EXISTS (
            SELECT 1 FROM fiel_contribuyente f
            WHERE f.contribuyente_id = c.entidad_id
              AND f.is_active = true AND f.valid_until > NOW()
          ) AS has_fiel
        FROM contribuyentes c
        JOIN entidades_gestionadas eg ON eg.id = c.entidad_id
        ORDER BY eg.nombre
      `);

      for (const c of contribuyentes) {
        const eligible = c.has_fiel || t.has_legacy_fiel;
        const key = `${t.id}:${c.entidad_id}`;
        const job = dailyByTenantContrib.get(key);

        if (job) {
          hadDaily.push({ tenantRfc: t.rfc, tenantName: t.nombre, contribuyenteId: c.entidad_id, contribuyenteRfc: c.rfc, contribuyenteName: c.razon_social, cfdisFound: job.cfdis_found, status: job.status });
        } else if (eligible) {
          missing.push({ tenantRfc: t.rfc, tenantName: t.nombre, contribuyenteId: c.entidad_id, contribuyenteRfc: c.rfc, contribuyenteName: c.razon_social, reason: 'sin daily' });
        } else {
          noFiel.push({ tenantRfc: t.rfc, tenantName: t.nombre, contribuyenteId: c.entidad_id, contribuyenteRfc: c.rfc, contribuyenteName: c.razon_social });
        }
      }
    } catch (err) {
      missing.push({ tenantRfc: t.rfc, tenantName: t.nombre, contribuyenteId: null, contribuyenteRfc: null, contribuyenteName: null, reason: `error consultando BD: ${err.message}` });
    } finally {
      try { await tenantClient.end(); } catch {}
    }
  }

  await client.end();

  console.log('=== CONTRIBUYENTES/TENANTS CON DAILY HOY ===');
  console.log(`Total con daily: ${hadDaily.length}`);
  for (const h of hadDaily) {
    const name = h.contribuyenteName ? `${h.contribuyenteName} (${h.contribuyenteRfc})` : h.tenantName;
    console.log(`- ${h.tenantRfc}: ${name} | CFDIs: ${h.cfdisFound ?? 'N/A'} | status: ${h.status}`);
  }

  console.log('\n=== CONTRIBUYENTES/TENANTS SIN DAILY HOY (con FIEL) ===');
  console.log(`Total sin daily: ${missing.length}`);
  for (const m of missing) {
    const name = m.contribuyenteName ? `${m.contribuyenteName} (${m.contribuyenteRfc})` : m.tenantName;
    console.log(`- ${m.tenantRfc}: ${name} [${m.reason}]`);
  }

  console.log('\n=== CONTRIBUYENTES/TENANTS SIN FIEL CONFIGURADA ===');
  console.log(`Total sin FIEL: ${noFiel.length}`);
  for (const n of noFiel) {
    const name = n.contribuyenteName ? `${n.contribuyenteName} (${n.contribuyenteRfc})` : n.tenantName;
    console.log(`- ${n.tenantRfc}: ${name}`);
  }
}

main().catch(err => { console.error(err); process.exit(1); });