use crate::db::has_table;
use crate::model::{
    AliasRow, NearHit, Planet, PlanetSearchRow, RouteListRow, RoutingObstacleRow, UnknownNearHit,
    UnknownPlanet, Waypoint, WaypointLinkRow, WaypointListRow, WaypointPlanetLink,
    WaypointRouteRow,
};
pub(crate) use crate::model::{RouteDetourRow, RouteLoaded, RouteRow, RouteWaypointRow};
use crate::routing::router::{DetourDecision, Route as ComputedRoute, RouteOptions};
use anyhow::{Context, Result};
use rusqlite::{Connection, OptionalExtension, Row, params};
use sha2::{Digest, Sha256};

const PLANET_SELECT_CANON: &str = r#"
  p.FID         AS fid,
  p.Planet      AS planet,
  p.planet_norm AS planet_norm,
  p.Region      AS region,
  p.Sector      AS sector,
  p.System      AS system,
  p.Grid        AS grid,
  p.X           AS x,
  p.Y           AS y,
  p.Canon       AS canon,
  p.Legends     AS legends,
  p.zm          AS zm,
  p.name0       AS name0,
  p.name1       AS name1,
  p.name2       AS name2,
  p.lat         AS lat,
  p.long        AS long,
  p.ref         AS reference,
  p.status      AS status,
  p.CRegion     AS c_region,
  p.CRegion_li  AS c_region_li
"#;

const UNKNOWN_PLANET_SELECT: &str = r#"
  u.id          AS id,
  u.fid         AS fid,
  u.planet      AS planet,
  u.planet_norm AS planet_norm,
  u.region      AS region,
  u.sector      AS sector,
  u.system      AS system,
  u.grid        AS grid,
  u.x           AS x,
  u.y           AS y,
  u.arcgis_hash AS arcgis_hash,
  u.deleted     AS deleted,
  u.canon       AS canon,
  u.legends     AS legends,
  u.zm          AS zm,
  u.name0       AS name0,
  u.name1       AS name1,
  u.name2       AS name2,
  u.lat         AS lat,
  u.long        AS long,
  u.ref         AS reference,
  u.status      AS status,
  u.cregion     AS c_region,
  u.cregion_li  AS c_region_li,
  u.reason      AS reason,
  u.reviewed    AS reviewed,
  u.promoted    AS promoted,
  u.notes       AS notes
"#;

fn unknown_planet_from_row(r: &Row<'_>) -> rusqlite::Result<UnknownPlanet> {
    Ok(UnknownPlanet {
        id: r.get("id")?,
        fid: r.get::<_, Option<i64>>("fid")?,
        planet: r.get("planet")?,
        planet_norm: r.get("planet_norm")?,
        region: r.get("region")?,
        sector: r.get("sector")?,
        system: r.get("system")?,
        grid: r.get("grid")?,
        x: r.get("x")?,
        y: r.get("y")?,
        arcgis_hash: r.get("arcgis_hash")?,
        deleted: r.get("deleted")?,
        canon: r.get("canon")?,
        legends: r.get("legends")?,
        zm: r.get("zm")?,
        name0: r.get("name0")?,
        name1: r.get("name1")?,
        name2: r.get("name2")?,
        lat: r.get("lat")?,
        long: r.get("long")?,
        reference: r.get("reference")?,
        status: r.get("status")?,
        c_region: r.get("c_region")?,
        c_region_li: r.get("c_region_li")?,
        reason: r.get("reason")?,
        reviewed: r.get("reviewed")?,
        promoted: r.get("promoted")?,
        notes: r.get("notes")?,
    })
}

const ROUTE_SELECT: &str = r#"
  r.id             AS id,
  r.from_planet_fid AS from_planet_fid,
  r.to_planet_fid   AS to_planet_fid,
  pf.Planet         AS from_planet_name,
  pt.Planet         AS to_planet_name,
  r.algo_version    AS algo_version,
  r.options_json    AS options_json,
  r.length          AS length,
  r.iterations      AS iterations,
  r.status          AS status,
  r.error           AS error,
  r.created_at      AS created_at,
  r.updated_at      AS updated_at
"#;

pub fn find_planet_by_norm(con: &Connection, planet_norm: &str) -> Result<Option<Planet>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM planets p
        WHERE p.planet_norm = ?1
        LIMIT 1
        "#,
        select = PLANET_SELECT_CANON
    );

    let mut stmt = con.prepare(&sql)?;
    let planet = stmt.query_row([planet_norm], Planet::from_row).optional()?;

    Ok(planet)
}

pub fn get_planet_by_fid(con: &Connection, fid: i64) -> Result<Option<Planet>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM planets p
        WHERE p.FID = ?1
        LIMIT 1
        "#,
        select = PLANET_SELECT_CANON
    );

    let mut stmt = con.prepare(&sql)?;
    let planet = stmt.query_row([fid], Planet::from_row).optional()?;
    Ok(planet)
}

pub fn find_planet_by_alias_norm(con: &Connection, alias_norm: &str) -> Result<Option<Planet>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM planet_aliases a
        JOIN planets p ON p.FID = a.planet_fid
        WHERE a.alias_norm = ?1
        ORDER BY p.Planet COLLATE NOCASE
        LIMIT 1
        "#,
        select = PLANET_SELECT_CANON
    );

    let mut stmt = con.prepare(&sql)?;
    let planet = stmt.query_row([alias_norm], Planet::from_row).optional()?;

    Ok(planet)
}

pub fn find_planet_for_info(con: &Connection, key_norm: &str) -> Result<Option<Planet>> {
    if let Some(p) = find_planet_by_norm(con, key_norm)? {
        return Ok(Some(p));
    }
    find_planet_by_alias_norm(con, key_norm)
}

pub fn near_unknown_planets(
    con: &Connection,
    x: f64,
    y: f64,
    range: f64,
    limit: i64,
) -> rusqlite::Result<Vec<UnknownNearHit>> {
    let sql = r#"
        SELECT
            u.id,
            u.fid,
            u.planet,
            u.x,
            u.y,
            u.reason,
            u.reviewed,
            u.promoted,
            (((u.x - ?1) * (u.x - ?1)) +
             ((u.y - ?2) * (u.y - ?2))) AS distance_sq
        FROM planets_unknown u
        WHERE u.x IS NOT NULL
          AND u.y IS NOT NULL
          AND (((u.x - ?1) * (u.x - ?1)) +
               ((u.y - ?2) * (u.y - ?2))) <= (?3 * ?3)
        ORDER BY distance_sq ASC, u.planet ASC
        LIMIT ?4
    "#;

    let mut stmt = con.prepare(sql)?;
    let rows = stmt.query_map((x, y, range, limit), |r| {
        let distance_sq: f64 = r.get(8)?;

        Ok(UnknownNearHit {
            id: r.get(0)?,
            fid: r.get(1)?,
            planet: r.get(2)?,
            x: r.get(3)?,
            y: r.get(4)?,
            reason: r.get(5)?,
            reviewed: r.get(6)?,
            promoted: r.get(7)?,
            distance: distance_sq.sqrt(),
        })
    })?;

    rows.collect()
}

pub fn get_unknown_planet_by_id(con: &Connection, id: i64) -> Result<Option<UnknownPlanet>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM planets_unknown u
        WHERE u.id = ?1
        LIMIT 1
        "#,
        select = UNKNOWN_PLANET_SELECT
    );

    let mut stmt = con.prepare(&sql)?;
    let planet = stmt
        .query_row([id], unknown_planet_from_row)
        .optional()
        .context("Failed to query planets_unknown by id")?;
    Ok(planet)
}

pub fn get_unknown_planet_by_fid(con: &Connection, fid: i64) -> Result<Option<UnknownPlanet>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM planets_unknown u
        WHERE u.fid = ?1
        ORDER BY u.id
        LIMIT 1
        "#,
        select = UNKNOWN_PLANET_SELECT
    );

    let mut stmt = con.prepare(&sql)?;
    let planet = stmt
        .query_row([fid], unknown_planet_from_row)
        .optional()
        .context("Failed to query planets_unknown by fid")?;
    Ok(planet)
}

/// Returns all unknown planets ordered by internal id.
/// Returns all unknown planets.
pub fn list_unknown_planets(conn: &Connection) -> rusqlite::Result<Vec<UnknownPlanet>> {
    let sql = format!(
        r#"
        SELECT
          {}
        FROM planets_unknown u
        ORDER BY u.id
        "#,
        UNKNOWN_PLANET_SELECT
    );

    let mut stmt = conn.prepare(&sql)?;
    let rows = stmt.query_map([], unknown_planet_from_row)?;

    rows.collect()
}

/// Returns the total number of unknown planets.
pub fn count_unknown_planets(conn: &Connection) -> rusqlite::Result<i64> {
    conn.query_row("SELECT COUNT(*) FROM planets_unknown u", [], |row| {
        row.get::<_, i64>(0)
    })
}

/// Returns a paginated list of unknown planets.
pub fn list_unknown_planets_paginated(
    conn: &Connection,
    page: usize,
    page_size: usize,
) -> rusqlite::Result<Vec<UnknownPlanet>> {
    let safe_page = page.max(1);
    let safe_page_size = page_size.max(1);

    let offset = ((safe_page - 1) * safe_page_size) as i64;
    let limit = safe_page_size as i64;

    let sql = format!(
        r#"
        SELECT
          {}
        FROM planets_unknown u
        ORDER BY u.id
        LIMIT ?1 OFFSET ?2
        "#,
        UNKNOWN_PLANET_SELECT
    );

    let mut stmt = conn.prepare(&sql)?;
    let rows = stmt.query_map([limit, offset], unknown_planet_from_row)?;

    rows.collect()
}

/// Returns known planets near the given unknown planet id using squared-distance SQL.
pub fn near_planets_for_unknown_id(
    con: &Connection,
    unknown_id: i64,
    radius: f64,
    limit: i64,
) -> Result<(UnknownPlanet, Vec<NearHit>)> {
    let unknown = get_unknown_planet_by_id(con, unknown_id)?
        .ok_or_else(|| anyhow::anyhow!("No unknown planet found for id {}", unknown_id))?;

    let origin_x = unknown.x.ok_or_else(|| {
        rusqlite::Error::InvalidQuery // oppure meglio errore custom, vedi sotto
    })?;

    let origin_y = unknown.y.ok_or_else(|| rusqlite::Error::InvalidQuery)?;

    let rows = near_planets(con, origin_x, origin_y, radius, limit)?;

    Ok((unknown, rows))
}

#[derive(Debug, Clone, Default)]
pub struct UnknownPlanetUpdate {
    pub planet: Option<String>,
    pub region: Option<Option<String>>,
    pub sector: Option<Option<String>>,
    pub system: Option<Option<String>>,
    pub grid: Option<Option<String>>,
    pub canon: Option<Option<i64>>,
    pub legends: Option<Option<i64>>,
    pub c_region: Option<Option<String>>,
    pub c_region_li: Option<Option<String>>,
    pub reviewed: Option<i64>,
    pub notes: Option<Option<String>>,
}

pub fn update_unknown_planet(
    con: &Connection,
    id: i64,
    update: &UnknownPlanetUpdate,
) -> Result<UnknownPlanet> {
    let Some(current) = get_unknown_planet_by_id(con, id)? else {
        anyhow::bail!("No unknown planet found for id {}", id);
    };

    let planet = update
        .planet
        .as_ref()
        .map(|s| s.trim())
        .filter(|s| !s.is_empty())
        .map(str::to_string)
        .unwrap_or_else(|| current.planet.clone());
    let planet_norm = planet.to_lowercase();

    let region = update.region.clone().unwrap_or(current.region.clone());
    let sector = update.sector.clone().unwrap_or(current.sector.clone());
    let system = update.system.clone().unwrap_or(current.system.clone());
    let grid = update.grid.clone().unwrap_or(current.grid.clone());
    let canon = update.canon.unwrap_or(current.canon);
    let legends = update.legends.unwrap_or(current.legends);
    let c_region = update.c_region.clone().unwrap_or(current.c_region.clone());
    let c_region_li = update
        .c_region_li
        .clone()
        .unwrap_or(current.c_region_li.clone());
    let reviewed = update.reviewed.unwrap_or(current.reviewed);
    let notes = update.notes.clone().unwrap_or(current.notes.clone());

    con.execute(
        r#"
        UPDATE planets_unknown
        SET planet = ?2,
            planet_norm = ?3,
            region = ?4,
            sector = ?5,
            system = ?6,
            grid = ?7,
            canon = ?8,
            legends = ?9,
            cregion = ?10,
            cregion_li = ?11,
            reviewed = ?12,
            notes = ?13
        WHERE id = ?1
        "#,
        params![
            id,
            planet,
            planet_norm,
            region,
            sector,
            system,
            grid,
            canon,
            legends,
            c_region,
            c_region_li,
            reviewed,
            notes,
        ],
    )
    .with_context(|| format!("Failed to update planets_unknown record id={id}"))?;

    get_unknown_planet_by_id(con, id)?
        .ok_or_else(|| anyhow::anyhow!("Unknown planet disappeared after update: id={id}"))
}

pub fn get_aliases(con: &Connection, fid: i64) -> Result<Vec<AliasRow>> {
    let mut stmt = con.prepare(
        r#"
        SELECT alias, source
        FROM planet_aliases
        WHERE planet_fid = ?1
        ORDER BY source, alias
        "#,
    )?;

    let rows = stmt
        .query_map(params![fid], |r| {
            Ok(AliasRow {
                alias: r.get(0)?,
                source: r.get(1)?,
            })
        })?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    Ok(rows)
}

pub fn search_planets(
    con: &Connection,
    query_norm: &str,
    limit: i64,
) -> Result<Vec<PlanetSearchRow>> {
    if limit <= 0 {
        return Ok(Vec::new());
    }

    let query_norm = query_norm.trim();
    if query_norm.is_empty() {
        return Ok(Vec::new());
    }

    if has_table(con, "planets_fts")? {
        return search_planets_fts(con, query_norm, limit);
    }

    search_planets_like(con, query_norm, limit)
}

fn search_planets_like(
    con: &Connection,
    query_norm: &str,
    limit: i64,
) -> Result<Vec<PlanetSearchRow>> {
    let like = format!("%{}%", query_norm);

    let mut stmt = con
        .prepare(
            r#"
            SELECT p.FID, p.Planet, p.Region, p.Sector, p.System, p.Grid, p.Canon, p.Legends
            FROM planet_search s
            JOIN planets p ON p.FID = s.planet_fid
            WHERE p.deleted = 0 AND s.search_norm LIKE ?1
            ORDER BY p.Planet COLLATE NOCASE
            LIMIT ?2
            "#,
        )
        .context("Failed to prepare LIKE search query")?;

    let rows = stmt
        .query_map((like, limit), |r| {
            Ok(PlanetSearchRow {
                fid: r.get::<_, i64>(0)?,
                name: r.get::<_, String>(1)?,
                region: r.get::<_, Option<String>>(2)?,
                sector: r.get::<_, Option<String>>(3)?,
                system: r.get::<_, Option<String>>(4)?,
                grid: r.get::<_, Option<String>>(5)?,
                x: r.get(6)?,
                y: r.get(7)?,
                canon: r.get(8)?,
                legends: r.get(9)?,
            })
        })
        .context("Failed to execute LIKE search query")?;

    let mut out = Vec::new();
    for row in rows {
        out.push(row?);
    }
    Ok(out)
}

fn search_planets_fts(
    con: &Connection,
    query_norm: &str,
    limit: i64,
) -> Result<Vec<PlanetSearchRow>> {
    let mut stmt = con
        .prepare(
            r#"
            SELECT p.FID, p.Planet, p.Region, p.Sector, p.System, p.Grid, p.X, p.Y, p.Canon, p.Legends
            FROM planets_fts f
            JOIN planets p ON p.FID = f.planet_fid
            WHERE p.deleted = 0 AND planets_fts MATCH ?1
            ORDER BY bm25(planets_fts)
            LIMIT ?2
            "#,
        )
        .context("Failed to prepare FTS search query")?;

    let rows = stmt
        .query_map((query_norm, limit), |r| {
            Ok(PlanetSearchRow {
                fid: r.get::<_, i64>(0)?,
                name: r.get::<_, String>(1)?,
                region: r.get::<_, Option<String>>(2)?,
                sector: r.get::<_, Option<String>>(3)?,
                system: r.get::<_, Option<String>>(4)?,
                grid: r.get::<_, Option<String>>(5)?,
                x: r.get(6)?,
                y: r.get(7)?,
                canon: r.get(8)?,
                legends: r.get(9)?,
            })
        })
        .context("Failed to execute FTS search query")?;

    let mut out = Vec::new();
    for row in rows {
        out.push(row?);
    }
    Ok(out)
}

pub fn near_planets(con: &Connection, x: f64, y: f64, r: f64, limit: i64) -> Result<Vec<NearHit>> {
    if !x.is_finite() || !y.is_finite() {
        anyhow::bail!("Center coordinates must be finite numbers");
    }
    if !r.is_finite() || r < 0.0 {
        anyhow::bail!("Radius must be a finite number >= 0");
    }
    if limit <= 0 {
        return Ok(Vec::new());
    }

    let r2 = r * r;

    let mut stmt = con.prepare(
        r#"
        SELECT FID, Planet, X, Y,
               ((X - ?1)*(X - ?1) + (Y - ?2)*(Y - ?2)) AS d2
        FROM planets
        WHERE ((X - ?1)*(X - ?1) + (Y - ?2)*(Y - ?2)) <= ?3
        ORDER BY d2 ASC
        LIMIT ?4
        "#,
    )?;

    let rows = stmt
        .query_map(params![x, y, r2, limit], |r| {
            let fid: i64 = r.get(0)?;
            let planet: String = r.get(1)?;
            let px: f64 = r.get(2)?;
            let py: f64 = r.get(3)?;
            let d2: f64 = r.get(4)?;
            Ok(NearHit {
                fid,
                planet,
                x: px,
                y: py,
                distance: d2.sqrt(),
            })
        })?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    Ok(rows)
}

pub fn near_planets_excluding_fid(
    con: &Connection,
    center_fid: i64,
    x: f64,
    y: f64,
    r: f64,
    limit: i64,
) -> Result<Vec<NearHit>> {
    if !x.is_finite() || !y.is_finite() {
        anyhow::bail!("Center coordinates must be finite numbers");
    }
    if !r.is_finite() || r < 0.0 {
        anyhow::bail!("Radius must be a finite number >= 0");
    }
    if limit <= 0 {
        return Ok(Vec::new());
    }

    let r2 = r * r;

    let mut stmt = con.prepare(
        r#"
        SELECT FID, Planet, X, Y,
               ((X - ?2)*(X - ?2) + (Y - ?3)*(Y - ?3)) AS d2
        FROM planets
        WHERE FID != ?1
          AND ((X - ?2)*(X - ?2) + (Y - ?3)*(Y - ?3)) <= ?4
        ORDER BY d2 ASC
        LIMIT ?5
        "#,
    )?;

    let rows = stmt
        .query_map(params![center_fid, x, y, r2, limit], |r| {
            let fid: i64 = r.get(0)?;
            let planet: String = r.get(1)?;
            let px: f64 = r.get(2)?;
            let py: f64 = r.get(3)?;
            let d2: f64 = r.get(4)?;
            Ok(NearHit {
                fid,
                planet,
                x: px,
                y: py,
                distance: d2.sqrt(),
            })
        })?
        .collect::<std::result::Result<Vec<_>, _>>()?;

    Ok(rows)
}

const WAYPOINT_SELECT: &str = r#"
  w.id          AS id,
  w.name        AS name,
  w.name_norm   AS name_norm,
  w.x           AS x,
  w.y           AS y,
  w.kind        AS kind,
  w.fingerprint AS fingerprint,
  w.note        AS note,
  w.created_at  AS created_at,
  w.updated_at  AS updated_at
"#;

fn waypoint_from_row(r: &Row<'_>) -> rusqlite::Result<Waypoint> {
    Ok(Waypoint {
        id: r.get("id")?,
        name: r.get("name")?,
        name_norm: r.get("name_norm")?,
        x: r.get("x")?,
        y: r.get("y")?,
        kind: r.get("kind")?,
        fingerprint: r.get("fingerprint")?,
        note: r.get("note")?,
        created_at: r.get("created_at")?,
        updated_at: r.get("updated_at")?,
    })
}

pub fn insert_waypoint(
    con: &Connection,
    name: &str,
    name_norm: &str,
    x: f64,
    y: f64,
    kind: &str,
    note: Option<&str>,
) -> Result<i64> {
    con.execute(
        r#"
        INSERT INTO waypoints (name, name_norm, x, y, kind, note)
        VALUES (?1, ?2, ?3, ?4, ?5, ?6)
        "#,
        params![name, name_norm, x, y, kind, note],
    )?;

    Ok(con.last_insert_rowid())
}

pub fn find_waypoint_by_norm(con: &Connection, name_norm: &str) -> Result<Option<Waypoint>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM waypoints w
        WHERE w.name_norm = ?1
        LIMIT 1
        "#,
        select = WAYPOINT_SELECT
    );

    let mut stmt = con.prepare(&sql)?;
    let wp = stmt.query_row([name_norm], waypoint_from_row).optional()?;
    Ok(wp)
}

pub fn list_waypoints(
    con: &Connection,
    limit: usize,
    offset: usize,
) -> Result<(Vec<WaypointListRow>, usize)> {
    use rusqlite::params;

    // 1) total count (no limit/offset)
    let total: i64 = con.query_row(r#"SELECT COUNT(*) FROM waypoints"#, [], |row| row.get(0))?;

    // 2) list page with links_count via CTE
    let sql = format!(
        r#"
        WITH
          lp AS (
            SELECT waypoint_id, COUNT(*) AS cnt
            FROM waypoint_planets
            GROUP BY waypoint_id
          ),
          rw AS (
            SELECT waypoint_id, COUNT(*) AS cnt
            FROM route_waypoints
            WHERE waypoint_id IS NOT NULL
            GROUP BY waypoint_id
          )
        SELECT
          {select},
          COALESCE(lp.cnt, 0) AS links_count,
          COALESCE(rw.cnt, 0) AS routes_count
        FROM waypoints w
        LEFT JOIN lp ON lp.waypoint_id = w.id
        LEFT JOIN rw ON rw.waypoint_id = w.id
        ORDER BY w.name COLLATE NOCASE
        LIMIT ?1 OFFSET ?2
        "#,
        select = WAYPOINT_SELECT
    );

    let mut stmt = con.prepare(&sql)?;

    let rows = stmt.query_map(params![limit as i64, offset as i64], |row| {
        let wp = waypoint_from_row(row)?;
        let links_count: i64 = row.get("links_count")?;
        let routes_count: i64 = row.get("routes_count")?;

        Ok(WaypointListRow {
            waypoint: wp,
            links_count,
            routes_count,
        })
    })?;

    let mut out: Vec<WaypointListRow> = Vec::new();
    for r in rows {
        out.push(r?);
    }

    Ok((out, total.max(0) as usize))
}

pub fn delete_waypoint(con: &Connection, id: i64) -> Result<usize> {
    let n = con.execute("DELETE FROM waypoints WHERE id = ?1", [id])?;
    Ok(n)
}

pub fn find_waypoint_by_id(con: &Connection, id: i64) -> Result<Option<Waypoint>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM waypoints w
        WHERE w.id = ?1
        LIMIT 1
        "#,
        select = WAYPOINT_SELECT
    );

    let mut stmt = con.prepare(&sql)?;
    let wp = stmt.query_row([id], waypoint_from_row).optional()?;
    Ok(wp)
}

// ---------- Waypoint <-> Planet links ----------

pub fn link_waypoint_to_planet(
    con: &Connection,
    waypoint_id: i64,
    planet_fid: i64,
    role: &str,
    distance: Option<f64>,
) -> Result<()> {
    con.execute(
        r#"
        INSERT INTO waypoint_planets(waypoint_id, planet_fid, role, distance)
        VALUES (?1, ?2, ?3, ?4)
        ON CONFLICT(waypoint_id, planet_fid) DO UPDATE SET
          role=excluded.role,
          distance=excluded.distance
        "#,
        params![waypoint_id, planet_fid, role, distance],
    )?;
    Ok(())
}

pub fn unlink_waypoint_from_planet(
    con: &Connection,
    waypoint_id: i64,
    planet_fid: i64,
) -> Result<usize> {
    let n = con.execute(
        "DELETE FROM waypoint_planets WHERE waypoint_id = ?1 AND planet_fid = ?2",
        params![waypoint_id, planet_fid],
    )?;
    Ok(n)
}

pub fn delete_waypoint_links(con: &Connection, waypoint_id: i64) -> Result<usize> {
    let n = con.execute(
        "DELETE FROM waypoint_planets WHERE waypoint_id = ?1",
        [waypoint_id],
    )?;
    Ok(n)
}

fn link_from_row(r: &Row<'_>) -> rusqlite::Result<WaypointPlanetLink> {
    Ok(WaypointPlanetLink {
        waypoint_id: r.get("waypoint_id")?,
        planet_fid: r.get("planet_fid")?,
        role: r.get("role")?,
        distance: r.get("distance")?,
    })
}

pub fn list_waypoint_links(con: &Connection, waypoint_id: i64) -> Result<Vec<WaypointLinkRow>> {
    let mut stmt = con.prepare(
        r#"
        SELECT
          wp.planet_fid AS planet_fid,
          p.Planet      AS planet_name,
          COALESCE(wp.role, '') AS role,
          wp.distance   AS distance
        FROM waypoint_planets wp
        JOIN planets p ON p.FID = wp.planet_fid
        WHERE wp.waypoint_id = ?1
        ORDER BY p.Planet COLLATE NOCASE
        "#,
    )?;

    let rows = stmt.query_map([waypoint_id], |row| {
        Ok(WaypointLinkRow {
            planet_fid: row.get("planet_fid")?,
            planet_name: row.get("planet_name")?,
            role: row.get("role")?,
            distance: row.get("distance")?,
        })
    })?;

    let mut out = Vec::new();
    for r in rows {
        out.push(r?);
    }
    Ok(out)
}

pub fn list_routes_for_waypoint(
    con: &Connection,
    waypoint_id: i64,
) -> Result<Vec<WaypointRouteRow>> {
    let mut stmt = con.prepare(
        r#"
        SELECT
          r.id AS id,

          r.from_planet_fid AS from_planet_fid,
          pf.Planet AS from_planet_name,

          r.to_planet_fid AS to_planet_fid,
          pt.Planet AS to_planet_name,

          r.status AS status,
          r.length AS length,

          COALESCE(r.updated_at, r.created_at) AS updated_at,

          COUNT(*) AS occurrences
        FROM route_waypoints rw
        JOIN routes r ON r.id = rw.route_id
        JOIN planets pf ON pf.FID = r.from_planet_fid
        JOIN planets pt ON pt.FID = r.to_planet_fid
        WHERE rw.waypoint_id = ?1
        GROUP BY
          r.id, r.from_planet_fid, pf.Planet, r.to_planet_fid, pt.Planet,
          r.status, r.length, COALESCE(r.updated_at, r.created_at)
        ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC
        "#,
    )?;

    let rows = stmt.query_map([waypoint_id], |row| {
        Ok(WaypointRouteRow {
            id: row.get("id")?,
            from_planet_fid: row.get("from_planet_fid")?,
            from_planet_name: row.get("from_planet_name")?,
            to_planet_fid: row.get("to_planet_fid")?,
            to_planet_name: row.get("to_planet_name")?,
            status: row.get("status")?,
            length: row.get("length")?,
            updated_at: row.get("updated_at")?,
            occurrences: row.get("occurrences")?,
        })
    })?;

    let mut out = Vec::new();
    for r in rows {
        out.push(r?);
    }
    Ok(out)
}
#[allow(dead_code)]
pub fn list_links_for_planet(con: &Connection, planet_fid: i64) -> Result<Vec<WaypointPlanetLink>> {
    let mut stmt = con.prepare(
        r#"
        SELECT
          waypoint_id AS waypoint_id,
          planet_fid  AS planet_fid,
          role        AS role,
          distance    AS distance
        FROM waypoint_planets
        WHERE planet_fid = ?1
        ORDER BY role, waypoint_id
        "#,
    )?;

    let rows = stmt.query_map([planet_fid], link_from_row)?;
    let mut out = Vec::new();
    for r in rows {
        out.push(r?);
    }
    Ok(out)
}

// ---------- List waypoints for a given planet ----------

pub fn list_waypoints_for_planet(
    con: &Connection,
    planet_fid: i64,
    role: Option<&str>, // filter optional
    limit: usize,
    offset: usize,
) -> Result<Vec<Waypoint>> {
    // If role is provided, filter by role; else list all.
    let sql = if role.is_some() {
        format!(
            r#"
            SELECT {select}
            FROM waypoint_planets wp
            JOIN waypoints w ON w.id = wp.waypoint_id
            WHERE wp.planet_fid = ?1 AND wp.role = ?2
            ORDER BY w.name COLLATE NOCASE
            LIMIT ?3 OFFSET ?4
            "#,
            select = WAYPOINT_SELECT
        )
    } else {
        format!(
            r#"
            SELECT {select}
            FROM waypoint_planets wp
            JOIN waypoints w ON w.id = wp.waypoint_id
            WHERE wp.planet_fid = ?1
            ORDER BY w.name COLLATE NOCASE
            LIMIT ?2 OFFSET ?3
            "#,
            select = WAYPOINT_SELECT
        )
    };

    let mut stmt = con.prepare(&sql)?;

    let rows = if let Some(role) = role {
        stmt.query_map(
            params![planet_fid, role, limit as i64, offset as i64],
            waypoint_from_row,
        )?
    } else {
        stmt.query_map(
            params![planet_fid, limit as i64, offset as i64],
            waypoint_from_row,
        )?
    };

    let mut out = Vec::new();
    for r in rows {
        out.push(r?);
    }
    Ok(out)
}

#[allow(dead_code)]
pub fn find_waypoint_by_fingerprint(con: &Connection, fp: &str) -> Result<Option<Waypoint>> {
    let sql = format!(
        r#"
        SELECT {select}
        FROM waypoints w
        WHERE w.fingerprint = ?1
        LIMIT 1
        "#,
        select = WAYPOINT_SELECT
    );

    let mut stmt = con.prepare(&sql)?;
    let wp = stmt.query_row([fp], waypoint_from_row).optional()?;
    Ok(wp)
}

/// Insert a computed waypoint if not already present (by fingerprint).
/// Returns (waypoint_id, created_new).
#[allow(dead_code, clippy::too_many_arguments)]
pub fn upsert_computed_waypoint(
    con: &Connection,
    name: &str,
    name_norm: &str,
    x: f64,
    y: f64,
    kind: &str, // e.g. "computed"
    note: Option<&str>,
    fingerprint: &str,
) -> Result<(i64, bool)> {
    if let Some(existing) = find_waypoint_by_fingerprint(con, fingerprint)? {
        return Ok((existing.id, false));
    }

    con.execute(
        r#"
        INSERT INTO waypoints (name, name_norm, x, y, kind, note, fingerprint)
        VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
        "#,
        params![name, name_norm, x, y, kind, note, fingerprint],
    )?;

    Ok((con.last_insert_rowid(), true))
}

pub fn list_planets_in_bbox(
    con: &Connection,
    min_x: f64,
    max_x: f64,
    min_y: f64,
    max_y: f64,
    limit: usize,
) -> Result<Vec<(i64, String, f64, f64)>> {
    let mut stmt = con.prepare(
        r#"
        SELECT FID, Planet, X, Y
        FROM planets
        WHERE deleted = 0
          AND X BETWEEN ?1 AND ?2
          AND Y BETWEEN ?3 AND ?4
        ORDER BY Planet COLLATE NOCASE
        LIMIT ?5
        "#,
    )?;

    let rows = stmt.query_map(
        rusqlite::params![min_x, max_x, min_y, max_y, limit as i64],
        |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?, r.get(3)?)),
    )?;

    let mut out = Vec::new();
    for row in rows {
        out.push(row?);
    }
    Ok(out)
}

/// Returns routing obstacles marked via `waypoint_planets.role`.
///
/// Project choice ("option 2"): **any** waypoint may mark a planet as an obstacle.
///
/// Roles considered obstacles: `avoid`, `danger`, `interdiction` (case-insensitive).
/// Radius is computed as `MAX(COALESCE(waypoint_planets.distance, default_radius))`.
///
/// The bbox is a cheap prefilter for performance.
pub fn list_routing_obstacles_in_bbox(
    con: &Connection,
    min_x: f64,
    max_x: f64,
    min_y: f64,
    max_y: f64,
    limit: usize,
    default_radius: f64,
) -> Result<Vec<RoutingObstacleRow>> {
    let mut stmt = con.prepare(
        r#"
        SELECT
          p.FID   AS fid,
          p.Planet AS planet,
          p.X     AS x,
          p.Y     AS y,
          MAX(COALESCE(wpl.distance, ?6)) AS radius
        FROM waypoint_planets wpl
        JOIN planets p ON p.FID = wpl.planet_fid
        WHERE p.deleted = 0
          AND lower(wpl.role) IN ('avoid','danger','interdiction')
          AND p.X BETWEEN ?1 AND ?2
          AND p.Y BETWEEN ?3 AND ?4
        GROUP BY p.FID, p.Planet, p.X, p.Y
        ORDER BY radius DESC, p.Planet COLLATE NOCASE
        LIMIT ?5
        "#,
    )?;

    let rows = stmt.query_map(
        rusqlite::params![min_x, max_x, min_y, max_y, limit as i64, default_radius],
        |r| {
            Ok(RoutingObstacleRow {
                fid: r.get(0)?,
                planet: r.get(1)?,
                x: r.get(2)?,
                y: r.get(3)?,
                radius: r.get(4)?,
            })
        },
    )?;

    let mut out = Vec::new();
    for row in rows {
        out.push(row?);
    }
    Ok(out)
}

fn round4(v: f64) -> f64 {
    (v * 10_000.0).round() / 10_000.0
}

fn detour_fingerprint(from_fid: i64, to_fid: i64, d: &DetourDecision) -> String {
    // fingerprint deterministico (con rounding), sufficiente per deduplica dei computed-waypoints
    let s = format!(
        "detour|from={}|to={}|ob={}|it={}|seg={}|x={:.4}|y={:.4}",
        from_fid,
        to_fid,
        d.obstacle_id,
        d.iteration,
        d.segment_index,
        round4(d.waypoint.x),
        round4(d.waypoint.y)
    );

    let mut h = Sha256::new();
    h.update(s.as_bytes());
    hex::encode(h.finalize())
}

pub fn insert_route_waypoint(
    con: &Connection,
    route_id: i64,
    seq: usize,
    x: f64,
    y: f64,
    waypoint_id: Option<i64>,
) -> Result<()> {
    con.execute(
        r#"
        INSERT INTO route_waypoints(route_id, seq, x, y, waypoint_id)
        VALUES (?1, ?2, ?3, ?4, ?5)
        "#,
        params![route_id, seq as i64, x, y, waypoint_id],
    )?;
    Ok(())
}

pub fn insert_route_detour(
    con: &Connection,
    route_id: i64,
    idx: usize,
    d: &DetourDecision,
    waypoint_id: Option<i64>,
) -> Result<()> {
    let total = d.score.total();

    con.execute(
        r#"
        INSERT INTO route_detours(
          route_id, idx,
          iteration, segment_index,
          obstacle_id, obstacle_x, obstacle_y, obstacle_radius,
          closest_t, closest_qx, closest_qy, closest_dist,
          offset_used,
          wp_x, wp_y, waypoint_id,
          score_base, score_turn, score_back, score_proximity, score_total,
          tries_used, tries_exhausted
        ) VALUES (
          ?1, ?2,
          ?3, ?4,
          ?5, ?6, ?7, ?8,
          ?9, ?10, ?11, ?12,
          ?13,
          ?14, ?15, ?16,
          ?17, ?18, ?19, ?20, ?21,
          ?22, ?23
        )
        "#,
        params![
            route_id,
            idx as i64,
            d.iteration as i64,
            d.segment_index as i64,
            d.obstacle_id,
            d.obstacle_center.x,
            d.obstacle_center.y,
            d.obstacle_radius,
            d.closest_t,
            d.closest_q.x,
            d.closest_q.y,
            d.closest_dist,
            d.offset_used,
            d.waypoint.x,
            d.waypoint.y,
            waypoint_id,
            d.score.base,
            d.score.turn,
            d.score.back,
            d.score.proximity,
            total,
            d.tries_used as i64,
            d.tries_exhausted as i64,
        ],
    )?;

    Ok(())
}

/// Persist a computed route and its detours.
/// - Creates a routes row
/// - Upserts detour waypoints into catalog (waypoints) using fingerprint
/// - Stores polyline (route_waypoints) and detour details (route_detours)
pub fn persist_route(
    con: &mut Connection,
    from_planet_fid: i64,
    to_planet_fid: i64,
    opts: RouteOptions,
    route: &ComputedRoute,
) -> Result<i64> {
    let tx = con
        .transaction()
        .context("Failed to start route persistence transaction")?;

    let options_json = serde_json::to_string(&serde_json::json!({
        "clearance": opts.clearance,
        "max_iters": opts.max_iters,
        "max_offset_tries": opts.max_offset_tries,
        "offset_growth": opts.offset_growth,
        "turn_weight": opts.turn_weight,
        "back_weight": opts.back_weight,
        "proximity_weight": opts.proximity_weight,
        "proximity_margin": opts.proximity_margin,
    }))?;

    let route_id = upsert_route_id(
        &tx,
        from_planet_fid,
        to_planet_fid,
        "router_v1",
        &options_json,
        route.length,
        route.iterations,
    )?;

    // IMPORTANT: replace existing polyline/detours for this from->to
    delete_route_children(&tx, route_id)?;

    // 1) Detours: upsert computed waypoint + store detour row.
    // Build a small lookup (rounded) to attach waypoint_id to the polyline too.
    use std::collections::HashMap;
    let mut detour_wp_ids: HashMap<String, i64> = HashMap::new();

    for (idx, d) in route.detours.iter().enumerate() {
        let fp = detour_fingerprint(from_planet_fid, to_planet_fid, d);

        let wp_name = format!("Detour {}", fp.get(0..8).unwrap_or("detour"));
        let wp_norm = crate::utils::normalize::normalize_text(&wp_name);

        let (wp_id, _created) = upsert_computed_waypoint(
            &tx,
            &wp_name,
            &wp_norm,
            d.waypoint.x,
            d.waypoint.y,
            "computed",
            Some("Computed detour waypoint"),
            &fp,
        )?;

        // Optional but useful: link computed waypoint to the obstacle planet as "avoid"
        // (obstacle_id is a planet fid in your current model)
        // distance = dist between waypoint and obstacle center
        let dist_to_ob = crate::routing::geometry::dist(d.waypoint, d.obstacle_center);
        // Ignore errors if already linked (depending on your UNIQUE/PK constraints you may want INSERT OR REPLACE)
        let _ = link_waypoint_to_planet(&tx, wp_id, d.obstacle_id, "avoid", Some(dist_to_ob));

        insert_route_detour(&tx, route_id, idx, d, Some(wp_id))?;

        let key = format!("{:.4},{:.4}", round4(d.waypoint.x), round4(d.waypoint.y));
        detour_wp_ids.insert(key, wp_id);
    }

    // 2) Route polyline
    for (seq, p) in route.waypoints.iter().enumerate() {
        let key = format!("{:.4},{:.4}", round4(p.x), round4(p.y));
        let waypoint_id = detour_wp_ids.get(&key).copied();
        insert_route_waypoint(&tx, route_id, seq, p.x, p.y, waypoint_id)?;
    }

    tx.commit()
        .context("Failed to commit route persistence transaction")?;
    Ok(route_id)
}

pub fn upsert_route_id(
    con: &Connection,
    from_planet_fid: i64,
    to_planet_fid: i64,
    algo_version: &str,
    options_json: &str,
    length: f64,
    iterations: usize,
) -> Result<i64> {
    con.execute(
        r#"
        INSERT INTO routes(
          from_planet_fid, to_planet_fid, algo_version, options_json,
          length, iterations, status, error, created_at, updated_at
        )
        VALUES (
          ?1, ?2, ?3, ?4,
          ?5, ?6, 'ok', NULL,
          strftime('%Y-%m-%dT%H:%M:%fZ','now'),
          strftime('%Y-%m-%dT%H:%M:%fZ','now')
        )
        ON CONFLICT(from_planet_fid, to_planet_fid) DO UPDATE SET
          algo_version = excluded.algo_version,
          options_json = excluded.options_json,
          length       = excluded.length,
          iterations   = excluded.iterations,
          status       = 'ok',
          error        = NULL,
          updated_at   = excluded.updated_at
        "#,
        params![
            from_planet_fid,
            to_planet_fid,
            algo_version,
            options_json,
            length,
            iterations as i64
        ],
    )?;

    let id: i64 = con.query_row(
        r#"
        SELECT id
        FROM routes
        WHERE from_planet_fid = ?1 AND to_planet_fid = ?2
        "#,
        params![from_planet_fid, to_planet_fid],
        |r| r.get(0),
    )?;

    Ok(id)
}

fn delete_route_children(con: &Connection, route_id: i64) -> Result<()> {
    con.execute(
        "DELETE FROM route_waypoints WHERE route_id = ?1",
        [route_id],
    )?;
    con.execute("DELETE FROM route_detours WHERE route_id = ?1", [route_id])?;
    Ok(())
}

fn route_from_row(r: &Row<'_>) -> rusqlite::Result<RouteRow> {
    Ok(RouteRow {
        id: r.get("id")?,
        from_planet_fid: r.get("from_planet_fid")?,
        to_planet_fid: r.get("to_planet_fid")?,
        from_planet_name: r.get("from_planet_name")?,
        to_planet_name: r.get("to_planet_name")?,
        algo_version: r.get("algo_version")?,
        options_json: r.get("options_json")?,
        length: r.get("length")?,
        iterations: r.get("iterations")?,
        status: r.get("status")?,
        error: r.get("error")?,
        created_at: r.get("created_at")?,
        updated_at: r.get("updated_at")?,
    })
}

fn route_waypoint_from_row(r: &Row<'_>) -> rusqlite::Result<RouteWaypointRow> {
    Ok(RouteWaypointRow {
        seq: r.get("seq")?,
        x: r.get("x")?,
        y: r.get("y")?,
        waypoint_id: r.get("waypoint_id")?,
        waypoint_name: r.get("waypoint_name")?,
        waypoint_kind: r.get("waypoint_kind")?,
    })
}

fn route_detour_from_row(r: &Row<'_>) -> rusqlite::Result<RouteDetourRow> {
    Ok(RouteDetourRow {
        idx: r.get("idx")?,
        iteration: r.get("iteration")?,
        segment_index: r.get("segment_index")?,

        obstacle_id: r.get("obstacle_id")?,
        obstacle_name: r.get("obstacle_name")?,
        obstacle_x: r.get("obstacle_x")?,
        obstacle_y: r.get("obstacle_y")?,
        obstacle_radius: r.get("obstacle_radius")?,

        closest_t: r.get("closest_t")?,
        closest_qx: r.get("closest_qx")?,
        closest_qy: r.get("closest_qy")?,
        closest_dist: r.get("closest_dist")?,

        offset_used: r.get("offset_used")?,

        wp_x: r.get("wp_x")?,
        wp_y: r.get("wp_y")?,
        waypoint_id: r.get("waypoint_id")?,

        score_base: r.get("score_base")?,
        score_turn: r.get("score_turn")?,
        score_back: r.get("score_back")?,
        score_proximity: r.get("score_proximity")?,
        score_total: r.get("score_total")?,

        tries_used: r.get("tries_used")?,
        tries_exhausted: r.get("tries_exhausted")?,
    })
}

pub fn get_route_by_from_to(
    con: &Connection,
    from_planet_fid: i64,
    to_planet_fid: i64,
) -> Result<Option<RouteRow>> {
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM routes r
        JOIN planets pf ON pf.FID = r.from_planet_fid
        JOIN planets pt ON pt.FID = r.to_planet_fid
        WHERE r.from_planet_fid = ?1 AND r.to_planet_fid = ?2
        LIMIT 1
        "#,
        select = ROUTE_SELECT
    );

    let mut stmt = con.prepare(&sql)?;
    let row = stmt
        .query_row(params![from_planet_fid, to_planet_fid], route_from_row)
        .optional()?;

    Ok(row)
}

pub fn load_route(con: &Connection, route_id: i64) -> Result<Option<RouteLoaded>> {
    // 1) Route header
    let sql = format!(
        r#"
        SELECT
          {select}
        FROM routes r
        JOIN planets pf ON pf.FID = r.from_planet_fid
        JOIN planets pt ON pt.FID = r.to_planet_fid
        WHERE r.id = ?1
        LIMIT 1
        "#,
        select = ROUTE_SELECT
    );

    let mut stmt_route = con.prepare(&sql)?;
    let route = stmt_route
        .query_row([route_id], route_from_row)
        .optional()?;

    let Some(route) = route else {
        return Ok(None);
    };

    // 2) Polyline
    let mut stmt_wp = con.prepare(
        r#"
        SELECT
          rw.seq         AS seq,
          rw.x           AS x,
          rw.y           AS y,
          rw.waypoint_id AS waypoint_id,

          w.name         AS waypoint_name,
          w.kind         AS waypoint_kind
        FROM route_waypoints rw
        LEFT JOIN waypoints w ON w.id = rw.waypoint_id
        WHERE rw.route_id = ?1
        ORDER BY rw.seq ASC
        "#,
    )?;

    let mut waypoints: Vec<RouteWaypointRow> = Vec::new();
    let rows = stmt_wp.query_map([route_id], route_waypoint_from_row)?;
    for r in rows {
        waypoints.push(r?);
    }

    // 3) Detours
    let mut stmt_det = con.prepare(
        r#"
        SELECT
          d.idx              AS idx,
          d.iteration        AS iteration,
          d.segment_index    AS segment_index,
    
          d.obstacle_id      AS obstacle_id,
          COALESCE(p.Planet, '') AS obstacle_name,
    
          d.obstacle_x       AS obstacle_x,
          d.obstacle_y       AS obstacle_y,
          d.obstacle_radius  AS obstacle_radius,
    
          d.closest_t        AS closest_t,
          d.closest_qx       AS closest_qx,
          d.closest_qy       AS closest_qy,
          d.closest_dist     AS closest_dist,
    
          d.offset_used      AS offset_used,
    
          d.wp_x             AS wp_x,
          d.wp_y             AS wp_y,
          d.waypoint_id      AS waypoint_id,
    
          d.score_base       AS score_base,
          d.score_turn       AS score_turn,
          d.score_back       AS score_back,
          d.score_proximity  AS score_proximity,
          d.score_total      AS score_total,
          d.tries_used       AS tries_used,
          d.tries_exhausted  AS tries_exhausted
        FROM route_detours d
        LEFT JOIN planets p ON p.FID = d.obstacle_id
        WHERE d.route_id = ?1
        ORDER BY d.idx ASC
        "#,
    )?;

    let mut detours: Vec<RouteDetourRow> = Vec::new();
    let rows = stmt_det.query_map([route_id], route_detour_from_row)?;
    for r in rows {
        detours.push(r?);
    }

    Ok(Some(RouteLoaded {
        route,
        waypoints,
        detours,
    }))
}

pub fn list_routes(
    con: &Connection,
    limit: usize,
    status: Option<&str>,
    from: Option<i64>,
    to: Option<i64>,
    wp: Option<usize>,
    sort: crate::domain::RouteListSort,
) -> Result<(Vec<RouteListRow>, usize)> {
    use rusqlite::types::Value;

    // ---------- Build WHERE + params ----------
    let mut where_parts: Vec<&'static str> = Vec::new();
    let mut params: Vec<Value> = Vec::new();

    if let Some(s) = status {
        where_parts.push("r.status = ? COLLATE NOCASE");
        params.push(Value::Text(s.to_string()));
    }
    if let Some(fid) = from {
        where_parts.push("r.from_planet_fid = ?");
        params.push(Value::Integer(fid));
    }
    if let Some(fid) = to {
        where_parts.push("r.to_planet_fid = ?");
        params.push(Value::Integer(fid));
    }

    let order_sql = match sort {
        crate::domain::RouteListSort::Updated => {
            "ORDER BY COALESCE(r.updated_at, r.created_at) DESC, r.id DESC"
        }
        crate::domain::RouteListSort::Id => "ORDER BY r.id DESC",
        crate::domain::RouteListSort::Length => {
            "ORDER BY (r.length IS NULL) ASC, r.length ASC, r.id DESC"
        }
    };

    // Helper to render WHERE
    let mut where_sql = if where_parts.is_empty() {
        String::new()
    } else {
        format!("WHERE {}", where_parts.join(" AND "))
    };

    // If --wp is specified, we need wp.cnt in WHERE -> build CTE + add filter
    let use_wp = wp.is_some();
    if let Some(n) = wp {
        // add wp filter (uses CTE alias)
        if where_sql.is_empty() {
            where_sql = "WHERE COALESCE(wp.cnt, 0) = ?".to_string();
        } else {
            where_sql.push_str(" AND COALESCE(wp.cnt, 0) = ?");
        }
        params.push(Value::Integer(n as i64));
    }

    // ---------- COUNT query ----------
    let total: usize = if use_wp {
        // With --wp we must include wp CTE + LEFT JOIN wp to filter by count
        let sql_count = format!(
            r#"
            WITH
              wp AS (
                SELECT route_id, COUNT(*) AS cnt
                FROM route_waypoints
                GROUP BY route_id
              )
            SELECT COUNT(*)
            FROM routes r
            JOIN planets fp ON fp.FID = r.from_planet_fid
            JOIN planets tp ON tp.FID = r.to_planet_fid
            LEFT JOIN wp ON wp.route_id = r.id
            {where_sql}
            "#,
            where_sql = where_sql
        );

        let n: i64 = con.query_row(
            &sql_count,
            rusqlite::params_from_iter(params.iter()),
            |row| row.get(0),
        )?;
        n.max(0) as usize
    } else {
        // No --wp: lightweight count is enough (no correlated subqueries needed)
        let sql_count = format!(
            r#"
            SELECT COUNT(*)
            FROM routes r
            JOIN planets fp ON fp.FID = r.from_planet_fid
            JOIN planets tp ON tp.FID = r.to_planet_fid
            {where_sql}
            "#,
            where_sql = where_sql
        );

        let n: i64 = con.query_row(
            &sql_count,
            rusqlite::params_from_iter(params.iter()),
            |row| row.get(0),
        )?;
        n.max(0) as usize
    };

    // ---------- LIST query ----------
    // limit always last parameter
    let mut list_params = params.clone();
    list_params.push(Value::Integer(limit as i64));

    let sql_list = if use_wp {
        format!(
            r#"
            WITH
              wp AS (
                SELECT route_id, COUNT(*) AS cnt
                FROM route_waypoints
                GROUP BY route_id
              ),
              dt AS (
                SELECT route_id, COUNT(*) AS cnt
                FROM route_detours
                GROUP BY route_id
              )
            SELECT
              r.id AS id,
              r.from_planet_fid AS from_planet_fid,
              fp.Planet AS from_planet_name,
              r.to_planet_fid AS to_planet_fid,
              tp.Planet AS to_planet_name,

              r.status AS status,
              r.length AS length,
              r.iterations AS iterations,

              r.created_at AS created_at,
              r.updated_at AS updated_at,

              COALESCE(wp.cnt, 0) AS waypoints_count,
              COALESCE(dt.cnt, 0) AS detours_count

            FROM routes r
            JOIN planets fp ON fp.FID = r.from_planet_fid
            JOIN planets tp ON tp.FID = r.to_planet_fid
            LEFT JOIN wp ON wp.route_id = r.id
            LEFT JOIN dt ON dt.route_id = r.id
            {where_sql}
            {order_sql}
            LIMIT ?
            "#,
            where_sql = where_sql,
            order_sql = order_sql
        )
    } else {
        format!(
            r#"
            SELECT
              r.id AS id,
              r.from_planet_fid AS from_planet_fid,
              fp.Planet AS from_planet_name,
              r.to_planet_fid AS to_planet_fid,
              tp.Planet AS to_planet_name,

              r.status AS status,
              r.length AS length,
              r.iterations AS iterations,

              r.created_at AS created_at,
              r.updated_at AS updated_at,

              (SELECT COUNT(*) FROM route_waypoints w WHERE w.route_id = r.id) AS waypoints_count,
              (SELECT COUNT(*) FROM route_detours d WHERE d.route_id = r.id) AS detours_count

            FROM routes r
            JOIN planets fp ON fp.FID = r.from_planet_fid
            JOIN planets tp ON tp.FID = r.to_planet_fid
            {where_sql}
            {order_sql}
            LIMIT ?
            "#,
            where_sql = where_sql,
            order_sql = order_sql
        )
    };

    let mut stmt = con.prepare(&sql_list)?;
    let rows = stmt
        .query_map(rusqlite::params_from_iter(list_params.iter()), |row| {
            Ok(RouteListRow {
                id: row.get("id")?,
                from_planet_fid: row.get("from_planet_fid")?,
                from_planet_name: row.get("from_planet_name")?,
                to_planet_fid: row.get("to_planet_fid")?,
                to_planet_name: row.get("to_planet_name")?,
                status: row.get("status")?,
                length: row.get("length")?,
                iterations: row.get("iterations")?,
                created_at: row.get("created_at")?,
                updated_at: row.get("updated_at")?,
                waypoints_count: row.get("waypoints_count")?,
                detours_count: row.get("detours_count")?,
            })
        })?
        .collect::<rusqlite::Result<Vec<_>>>()?;

    Ok((rows, total))
}

#[cfg(test)]
mod tests {
    use super::{
        UnknownPlanetUpdate, near_planets, near_planets_excluding_fid, search_planets,
        update_unknown_planet,
    };
    use rusqlite::Connection;

    fn setup_search_db() -> Connection {
        let con = Connection::open_in_memory().expect("in-memory sqlite");
        con.execute_batch(
            r#"
            CREATE TABLE planets (
                FID INTEGER PRIMARY KEY,
                Planet TEXT NOT NULL,
                Region TEXT,
                Sector TEXT,
                System TEXT,
                Grid TEXT,
                X REAL NOT NULL,
                Y REAL NOT NULL,
                deleted INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE planet_search (
                planet_fid INTEGER NOT NULL,
                search_norm TEXT NOT NULL
            );
            INSERT INTO planets (FID, Planet, Region, Sector, System, Grid, X, Y, deleted) VALUES
                (1, 'Alderaan', 'Core Worlds', 'Alderaan', 'Alderaan', 'L-4', 10.0, 10.0, 0),
                (2, 'Tatooine', 'Outer Rim', 'Arkanis', 'Tatoo', 'R-16', 20.0, 25.0, 0),
                (3, 'Deleted', 'Unknown', NULL, NULL, NULL, 50.0, 50.0, 1);
            INSERT INTO planet_search (planet_fid, search_norm) VALUES
                (1, 'alderaan house organa'),
                (2, 'tatooine luke skywalker'),
                (3, 'deleted hidden');
            "#,
        )
        .expect("schema setup");
        con
    }

    fn setup_unknown_db() -> Connection {
        let con = Connection::open_in_memory().expect("in-memory sqlite");
        con.execute_batch(
            r#"
            CREATE TABLE planets_unknown (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                fid         INTEGER,
                planet      TEXT NOT NULL,
                planet_norm TEXT NOT NULL,
                region      TEXT,
                sector      TEXT,
                system      TEXT,
                grid        TEXT,
                x           REAL,
                y           REAL,
                arcgis_hash TEXT,
                deleted     INTEGER NOT NULL DEFAULT 0,
                canon       INTEGER,
                legends     INTEGER,
                zm          INTEGER,
                name0       TEXT,
                name1       TEXT,
                name2       TEXT,
                lat         REAL,
                long        REAL,
                ref         TEXT,
                status      TEXT,
                cregion     TEXT,
                cregion_li  TEXT,
                reason      TEXT,
                reviewed    INTEGER NOT NULL DEFAULT 0,
                promoted    INTEGER NOT NULL DEFAULT 0,
                notes       TEXT
            );
            INSERT INTO planets_unknown (
                id, fid, planet, planet_norm, region, sector, system, grid, x, y, canon,
                legends, cregion, cregion_li, reviewed, promoted, notes
            ) VALUES (
                7, 1007, 'TBD World', 'tbd world', 'Unknown Regions', 'Sector A',
                'System A', 'A-1', 1.0, 2.0, NULL, NULL, NULL, NULL, 0, 0, 'draft'
            );
            "#,
        )
        .expect("unknown schema setup");
        con
    }

    #[test]
    fn search_planets_ignores_empty_query_and_non_positive_limit() {
        let con = setup_search_db();

        assert!(
            search_planets(&con, "", 10)
                .expect("empty query")
                .is_empty()
        );
        assert!(
            search_planets(&con, "   ", 10)
                .expect("blank query")
                .is_empty()
        );
        assert!(
            search_planets(&con, "alderaan", 0)
                .expect("zero limit")
                .is_empty()
        );
    }

    #[test]
    fn near_planets_validates_inputs_and_filters_results() {
        let con = setup_search_db();

        let rows = near_planets(&con, 9.0, 9.0, 2.0, 10).expect("near query");
        assert_eq!(rows.len(), 1);
        assert_eq!(rows[0].planet, "Alderaan");

        assert!(near_planets(&con, 0.0, 0.0, -1.0, 10).is_err());
        assert!(near_planets(&con, f64::NAN, 0.0, 1.0, 10).is_err());
        assert!(
            near_planets(&con, 0.0, 0.0, 1.0, 0)
                .expect("zero limit")
                .is_empty()
        );
    }

    #[test]
    fn near_planets_excluding_fid_excludes_origin_planet() {
        let con = setup_search_db();

        let rows =
            near_planets_excluding_fid(&con, 1, 10.0, 10.0, 30.0, 10).expect("excluding fid");
        assert_eq!(rows.len(), 1);
        assert_eq!(rows[0].planet, "Tatooine");
    }

    #[test]
    fn update_unknown_planet_updates_requested_fields_and_planet_norm() {
        let con = setup_unknown_db();

        let updated = update_unknown_planet(
            &con,
            7,
            &UnknownPlanetUpdate {
                planet: Some("Ord Mantell".to_string()),
                region: Some(Some("Mid Rim".to_string())),
                sector: Some(None),
                system: Some(Some("Bright Jewel".to_string())),
                grid: Some(Some("M-12".to_string())),
                canon: Some(Some(1)),
                legends: Some(Some(0)),
                c_region: Some(Some("Mid Rim".to_string())),
                c_region_li: Some(Some("Mid Rim Territories".to_string())),
                reviewed: Some(1),
                notes: Some(Some("verified manually".to_string())),
            },
        )
        .expect("update succeeds");

        assert_eq!(updated.planet, "Ord Mantell");
        assert_eq!(updated.planet_norm, "ord mantell");
        assert_eq!(updated.region.as_deref(), Some("Mid Rim"));
        assert_eq!(updated.sector, None);
        assert_eq!(updated.system.as_deref(), Some("Bright Jewel"));
        assert_eq!(updated.grid.as_deref(), Some("M-12"));
        assert_eq!(updated.canon, Some(1));
        assert_eq!(updated.legends, Some(0));
        assert_eq!(updated.c_region.as_deref(), Some("Mid Rim"));
        assert_eq!(updated.c_region_li.as_deref(), Some("Mid Rim Territories"));
        assert_eq!(updated.reviewed, 1);
        assert_eq!(updated.notes.as_deref(), Some("verified manually"));
    }
}
