import { NextResponse } from "next/server";
import { db } from "@/lib/db";

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const neighborhood = searchParams.get("neighborhood");
  const section = searchParams.get("section");
  const leaderId = searchParams.get("leaderId");

  try {
    let mConditions = [];
    let mParams: any[] = [];

    // Filtros dinámicos
    if (neighborhood) { 
      mConditions.push("m.neighborhood = ?"); 
      mParams.push(neighborhood); 
    }
    if (section) { 
      mConditions.push("m.section_number = ?"); 
      mParams.push(section); 
    }
    if (leaderId) { 
      // Filtramos por el responsable (líder o quien lo registró)
      mConditions.push("(m.leader_id = ? OR m.created_by = ?)"); 
      mParams.push(leaderId, leaderId); 
    }

    const whereClause = mConditions.length > 0 ? `WHERE ${mConditions.join(" AND ")}` : "";

    // 1. ESTADÍSTICAS GENERALES
    const [statsResult]: any = await db.query(`
      SELECT 
        COUNT(m.id) as total_militants,
        COUNT(DISTINCT CASE WHEN u.role = 'leader' THEN m.id END) as leader_count,
        COUNT(DISTINCT CASE WHEN u.role = 'brigadist' THEN m.id END) as brigadist_count
      FROM militants m
      LEFT JOIN users u ON m.id = u.militant_id
      ${whereClause}
    `, mParams);

    const stats = statsResult[0];

    // 2. RADAR SECCIONAL (COMPARATIVA)
    const [attackZones]: any = await db.query(`
      SELECT 
        s.section_number,
        (SELECT COUNT(*) FROM militants m ${whereClause ? whereClause + ' AND' : 'WHERE'} m.section_number = s.section_number) as total
      FROM (SELECT DISTINCT section_number FROM sections) s
      ORDER BY total ASC
    `, mParams);

    // 3. PUNTOS GEOGRÁFICOS CON DATA PARA POPUP (CORREGIDO)
    const [pointsResult]: any = await db.query(`
      SELECT 
        latitude, 
        longitude, 
        first_name, 
        last_name_paternal, 
        neighborhood, 
        section_number 
      FROM militants m
      ${whereClause} ${whereClause ? 'AND' : 'WHERE'} latitude IS NOT NULL AND longitude IS NOT NULL
    `, mParams);

    // 4. CATÁLOGOS PARA FILTROS Y FORMULARIOS (NO FILTRADOS)
    const [leaders]: any = await db.query(`SELECT id, full_name FROM users WHERE role = 'leader' ORDER BY full_name ASC`);
    const [neighborhoodsMilitants]: any = await db.query(`SELECT DISTINCT neighborhood FROM militants WHERE neighborhood IS NOT NULL ORDER BY neighborhood ASC`);
    const [allNeighborhoods]: any = await db.query(`SELECT DISTINCT colonia FROM sections WHERE colonia IS NOT NULL ORDER BY colonia ASC`);
    const [sectionsList]: any = await db.query(`SELECT DISTINCT section_number FROM sections ORDER BY CAST(section_number AS UNSIGNED) ASC`);

    return NextResponse.json({
      stats: {
        total: stats.total_militants || 0,
        leaders: stats.leader_count || 0,
        brigadists: stats.brigadist_count || 0,
        militants: stats.total_militants || 0
      },
      leaders: leaders || [],
      sections: sectionsList || [],
      neighborhoods: neighborhoodsMilitants,
      allNeighborhoods: allNeighborhoods,
      missing: attackZones.map((z: any) => ({ 
        name: `Sección ${z.section_number}`, 
        count: z.total 
      })),
      // Formateamos los puntos para que el Mapa los procese fácilmente
      points: pointsResult.map((p: any) => ({
        lat: parseFloat(p.latitude),
        lng: parseFloat(p.longitude),
        name: `${p.first_name} ${p.last_name_paternal}`,
        colonia: p.neighborhood || "Sin Colonia",
        seccion: p.section_number || "S/N"
      }))
    });

  } catch (error) {
    console.error("ERROR_REPORTE_FULL:", error);
    return NextResponse.json({ error: "Error al generar el reporte" }, { status: 500 });
  }
}