import "dotenv/config";
import { RowDataPacket } from "mysql2";
import pool from "../config/db";
import {
  VALENCIA_CENTER, VALENCIA_COVERAGE_RADIUS_KM,
  coverageSegment, haversineKm, slugifyMunicipality,
} from "../config/valenciaCoverage";

interface MunicipalityRow extends RowDataPacket {
  name: string;
  ascii_name: string | null;
  lat: number;
  lng: number;
  population: number;
}

interface CoverageTown {
  name: string;
  slug: string;
  lat: number;
  lng: number;
  population: number;
  distance: number;
  segment: string;
}

const LEGACY_SLUGS: Record<string, string> = {
  liria: "lliria",
  "ribarroja-del-turia": "riba-roja",
  vilamarxant: "villamarchante",
  "l-eliana": "eliana",
  "la-pobla-de-vallbona": "pobla-de-vallbona",
  "la-vall-d-uixo": "la-vall-duixo",
};

const OBSOLETE_ALIASES: Record<string, string> = {
  "la-pobla-de-vallbona": "pobla-de-vallbona",
  "la-vall-d-uixo": "la-vall-duixo",
};

function normalizedKey(value: string): string {
  return slugifyMunicipality(value).replace(/^(el|la|l)-/, "");
}

function toCoverage(rows: MunicipalityRow[]): CoverageTown[] {
  const deduplicated = new Map<string, CoverageTown>();
  for (const row of rows) {
    const lat = Number(row.lat);
    const lng = Number(row.lng);
    const distance = haversineKm(VALENCIA_CENTER.lat, VALENCIA_CENTER.lng, lat, lng);
    if (distance > VALENCIA_COVERAGE_RADIUS_KM) continue;
    if (normalizedKey(row.name) === "valencia") continue;
    const rawSlug = slugifyMunicipality(row.ascii_name || row.name);
    const candidate: CoverageTown = {
      name: row.name,
      slug: LEGACY_SLUGS[rawSlug] || rawSlug,
      lat, lng,
      population: Number(row.population) || 0,
      distance,
      segment: coverageSegment(lat, lng),
    };
    const previous = deduplicated.get(candidate.slug);
    if (!previous || candidate.population > previous.population) deduplicated.set(candidate.slug, candidate);
  }
  return [...deduplicated.values()].sort((a, b) => a.distance - b.distance || a.name.localeCompare(b.name));
}

async function main(): Promise<void> {
  const apply = process.argv.includes("--apply");
  const [rows] = await pool.query<MunicipalityRow[]>(
    `SELECT name, ascii_name, lat, lng, population
       FROM municipalities_es
      WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?`,
    [VALENCIA_CENTER.lat - 0.5, VALENCIA_CENTER.lat + 0.5, VALENCIA_CENTER.lng - 0.6, VALENCIA_CENTER.lng + 0.6]
  );
  const towns = toCoverage(rows);
  const segments = towns.reduce<Record<string, number>>((acc, town) => {
    acc[town.segment] = (acc[town.segment] || 0) + 1;
    return acc;
  }, {});

  console.log(JSON.stringify({ mode: apply ? "apply" : "dry-run", radius_km: 40, towns: towns.length, segments }, null, 2));
  if (!apply) {
    console.log("No se ha modificado la BD. Use --apply tras revisar este resumen.");
    return;
  }

  await pool.query(`ALTER TABLE zones ADD COLUMN IF NOT EXISTS distance_from_valencia_km DECIMAL(6,2) NULL AFTER radius_km`);
  await pool.query(`ALTER TABLE zones ADD COLUMN IF NOT EXISTS coverage_segment VARCHAR(40) NULL AFTER distance_from_valencia_km`);
  await pool.query(`ALTER TABLE zones ADD COLUMN IF NOT EXISTS coverage_scope VARCHAR(40) NULL AFTER coverage_segment`);

  await pool.query(
    `UPDATE zones SET name = 'Valencia', lat = ?, lng = ?, radius_km = 15, active = 1,
       distance_from_valencia_km = 0, coverage_segment = 'capital', coverage_scope = 'valencia-40km'
     WHERE slug = 'valencia'`,
    [VALENCIA_CENTER.lat, VALENCIA_CENTER.lng]
  );

  for (const [obsolete, canonical] of Object.entries(OBSOLETE_ALIASES)) {
    await pool.query(
      `UPDATE zones SET active = 0, coverage_scope = CONCAT('alias-de-', ?)
        WHERE slug = ? AND EXISTS (SELECT 1 FROM (SELECT id FROM zones WHERE slug = ?) canonical)`,
      [canonical, obsolete, canonical]
    );
  }

  for (const town of towns) {
    await pool.query(
      `INSERT INTO zones
        (name, slug, lat, lng, radius_km, active, pisos_slug, thinkspain_slug, globaliza_slug,
         distance_from_valencia_km, coverage_segment, coverage_scope)
       VALUES (?, ?, ?, ?, 5, 1, ?, ?, ?, ?, ?, 'valencia-40km')
       ON DUPLICATE KEY UPDATE
         name = VALUES(name), lat = VALUES(lat), lng = VALUES(lng), active = 1,
         distance_from_valencia_km = VALUES(distance_from_valencia_km),
         coverage_segment = VALUES(coverage_segment), coverage_scope = VALUES(coverage_scope)`,
      [town.name, town.slug, town.lat, town.lng, `pisos-${town.slug}`, town.slug, town.slug,
        town.distance.toFixed(2), town.segment]
    );
  }

  const [allZones] = await pool.query<RowDataPacket[]>(`SELECT id, slug, lat, lng FROM zones`);
  let deactivated = 0;
  for (const zone of allZones) {
    if (zone.slug === "valencia") continue;
    const distance = haversineKm(VALENCIA_CENTER.lat, VALENCIA_CENTER.lng, Number(zone.lat), Number(zone.lng));
    if (distance > VALENCIA_COVERAGE_RADIUS_KM) {
      const [result]: any = await pool.query(
        `UPDATE zones SET active = 0, distance_from_valencia_km = ?, coverage_segment = NULL,
           coverage_scope = 'fuera-valencia-40km' WHERE id = ? AND active = 1`,
        [distance.toFixed(2), zone.id]
      );
      deactivated += result.affectedRows || 0;
    }
  }
  console.log(`Cobertura aplicada: ${towns.length} municipios; ${deactivated} zonas fuera de radio desactivadas.`);
}

main()
  .catch(error => { console.error(error); process.exitCode = 1; })
  .finally(() => pool.end());