package com.sigem.gis.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/api/gis")
public class GisController {

    @Autowired
    @Qualifier("gisJdbcTemplate")
    private JdbcTemplate gisJdbcTemplate;

    @Autowired
    @Qualifier("masterJdbcTemplate")
    private JdbcTemplate masterJdbcTemplate;

    @GetMapping("/morosidad/{entidad}/{ccc}")
    public ResponseEntity<?> getMorosidad(@PathVariable String entidad, @PathVariable String ccc) {
        try {
            // Consulta a la vista unificada vw_lotes_morosidad_XXX
            String viewName = "public.vw_lotes_morosidad_" + entidad;
            String sql = "SELECT ccc, inm_ficha, inm_ctacatastral, trb_total_deuda, trb_total_pago, ultimo_pago " +
                         "FROM " + viewName + " WHERE ccc = ? LIMIT 1";
            
            List<Map<String, Object>> results = gisJdbcTemplate.queryForList(sql, ccc);
            
            if (results.isEmpty()) {
                // Si no hay datos en la vista (quizás lote sin deuda), buscamos solo datos de lote
                return ResponseEntity.ok(Map.of("ccc", ccc, "trb_total_deuda", 0, "status", "Sin Deuda"));
            }
            
            return ResponseEntity.ok(results.get(0));
        } catch (Exception e) {
            return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
        }
    }

    @GetMapping("/entidad/{id}/percentiles")
    public ResponseEntity<?> getPercentiles(@PathVariable String id) {
        try {
            String sql = "SELECT * FROM public.vw_percentiles_morosidad_" + id + " LIMIT 1";
            List<Map<String, Object>> results = gisJdbcTemplate.queryForList(sql);
            if (results.isEmpty()) return ResponseEntity.notFound().build();
            return ResponseEntity.ok(results.get(0));
        } catch (Exception e) {
            return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
        }
    }

    @GetMapping("/entidad/{id}/resumen")
    public ResponseEntity<?> getResumen(@PathVariable String id) {
        try {
            String viewName = "public.vw_lotes_morosidad_" + id;
            String sql = "SELECT " +
                         "  COUNT(*) as total_lotes, " +
                         "  COUNT(CASE WHEN trb_total_deuda > 0 THEN 1 END) as lotes_con_deuda " +
                         "FROM " + viewName;
            
            return ResponseEntity.ok(gisJdbcTemplate.queryForList(sql).get(0));
        } catch (Exception e) {
            return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
        }
    }

    @GetMapping("/entidad/{id}")
    public ResponseEntity<?> getEntidadData(@PathVariable String id) {
        try {
            String sql = "SELECT * FROM public.entidades WHERE activo = TRUE AND entidad = ?";
            // Parse int as the FDW might require integer for 'entidad' column
            int entidadId = Integer.parseInt(id);
            List<Map<String, Object>> results = masterJdbcTemplate.queryForList(sql, entidadId);
            
            if (results.isEmpty()) {
                return ResponseEntity.notFound().build();
            }
            
            return ResponseEntity.ok(results.get(0));
        } catch (NumberFormatException e) {
             return ResponseEntity.badRequest().body(Map.of("error", "ID de entidad inválido"));
        } catch (Exception e) {
            return ResponseEntity.status(500).body(Map.of("error", e.getMessage()));
        }
    }
}