use crate::utils::normalize::normalize_text;
use anyhow::{Context, Result};
use rusqlite::{Connection, OptionalExtension, Transaction, params};
use serde_json::Value;
use sha2::{Digest, Sha256};

pub struct BuildMeta {
    pub imported_at_utc: String,
    pub source_service_item_id: String,
    pub dataset_version: String,
    pub importer_version: String,
}

#[derive(Debug, Clone)]
struct SearchRow {
    planet_fid: i64,
    planet: String,
    planet_norm: String,
    aliases: Option<String>,
    aliases_norm: Option<String>,
    search_text: String,
    search_norm: String,
}

/// Represents a skipped planet row to be inserted into `planets_unknown`.
struct SkippedUnknownRow {
    fid: Option<i64>,
    planet: String,
    planet_norm: String,
    x: Option<f64>,
    y: Option<f64>,
    reason: String,
}

pub fn create_schema(con: &Connection, enable_fts: bool) -> Result<()> {
    con.execute_batch(
        r#"
        PRAGMA journal_mode=WAL;
        PRAGMA synchronous=NORMAL;
        PRAGMA foreign_keys=ON;

        -- =========================
        -- META
        -- =========================
        CREATE TABLE IF NOT EXISTS meta (
            key   TEXT PRIMARY KEY,
            value TEXT NOT NULL
        );

        -- =========================
        -- CORE
        -- =========================
        DROP TABLE IF EXISTS planets;

        CREATE TABLE planets (
            FID         INTEGER PRIMARY KEY,
            Planet      TEXT NOT NULL,
            planet_norm TEXT NOT NULL,

            Region      TEXT,
            Sector      TEXT,
            System      TEXT,
            Grid        TEXT,

            X           REAL NOT NULL,
            Y           REAL NOT NULL,

            -- v0.4.0
            arcgis_hash TEXT NOT NULL,
            deleted     INTEGER NOT NULL DEFAULT 0 CHECK (deleted IN (0,1)),

            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,

            CHECK (Canon   IS NULL OR Canon   IN (0, 1)),
            CHECK (Legends IS NULL OR Legends IN (0, 1))
        );

        CREATE INDEX IF NOT EXISTS idx_planets             ON planets(Planet,Region,Sector,System,X,Y);
        CREATE INDEX IF NOT EXISTS idx_planets_planet      ON planets(Planet);
        CREATE INDEX IF NOT EXISTS idx_planets_planet_norm ON planets(planet_norm);
        CREATE INDEX IF NOT EXISTS idx_planets_region      ON planets(Region);
        CREATE INDEX IF NOT EXISTS idx_planets_sector      ON planets(Sector);
        CREATE INDEX IF NOT EXISTS idx_planets_system      ON planets(System);
        CREATE INDEX IF NOT EXISTS idx_planets_grid        ON planets(Grid);
        CREATE INDEX IF NOT EXISTS idx_planets_xy          ON planets(X, Y);

        -- =========================
        -- PLANETS UNKNOWN
        -- =========================
        DROP TABLE IF EXISTS planets_unknown;

        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 CHECK (deleted IN (0,1)),
            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 CHECK (reviewed IN (0,1)),
            promoted    INTEGER NOT NULL DEFAULT 0 CHECK (promoted IN (0,1)),
            notes       TEXT,
            CHECK (canon IS NULL OR canon IN (0,1)),
            CHECK (legends IS NULL OR legends IN (0,1))
        );

        CREATE INDEX IF NOT EXISTS idx_planets_unknown_fid         ON planets_unknown(fid);
        CREATE INDEX IF NOT EXISTS idx_planets_unknown_planet      ON planets_unknown(planet);
        CREATE INDEX IF NOT EXISTS idx_planets_unknown_planet_norm ON planets_unknown(planet_norm);
        CREATE INDEX IF NOT EXISTS idx_planets_unknown_sector      ON planets_unknown(sector);
        CREATE INDEX IF NOT EXISTS idx_planets_unknown_system      ON planets_unknown(system);
        CREATE INDEX IF NOT EXISTS idx_planets_unknown_xy          ON planets_unknown(x, y);
        CREATE INDEX IF NOT EXISTS idx_planets_unknown_reviewed    ON planets_unknown(reviewed);
        CREATE INDEX IF NOT EXISTS idx_planets_unknown_promoted    ON planets_unknown(promoted);

        -- =========================
        -- WAYPOINTS (catalog)
        -- =========================
        DROP TABLE IF EXISTS waypoints;

        CREATE TABLE waypoints (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            name        TEXT NOT NULL,
            name_norm   TEXT NOT NULL,
            x           REAL NOT NULL,
            y           REAL NOT NULL,
            kind        TEXT NOT NULL DEFAULT 'manual',
            fingerprint TEXT NOT NULL DEFAULT '',
            note        TEXT,
            created_at  TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at  TEXT
        );

        CREATE UNIQUE INDEX IF NOT EXISTS idx_waypoints_name_norm ON waypoints(name_norm);
        CREATE INDEX IF NOT EXISTS idx_waypoints_xy ON waypoints(x, y);
        CREATE UNIQUE INDEX IF NOT EXISTS idx_waypoints_fingerprint
            ON waypoints(fingerprint);

        CREATE TRIGGER IF NOT EXISTS trg_waypoints_updated_at
        AFTER UPDATE ON waypoints
        FOR EACH ROW
        BEGIN
            UPDATE waypoints SET updated_at = datetime('now') WHERE id = OLD.id;
        END;

        -- =========================
        -- WAYPOINT ↔ PLANETS LINKS
        -- =========================
        DROP TABLE IF EXISTS waypoint_planets;

        CREATE TABLE waypoint_planets (
            waypoint_id  INTEGER NOT NULL,
            planet_fid   INTEGER NOT NULL,
            role         TEXT NOT NULL DEFAULT 'via',
            distance     REAL,
            PRIMARY KEY (waypoint_id, planet_fid, role),
            FOREIGN KEY (waypoint_id) REFERENCES waypoints(id) ON DELETE CASCADE,
            FOREIGN KEY (planet_fid) REFERENCES planets(FID) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_wp_planets_planet ON waypoint_planets(planet_fid);
        CREATE INDEX IF NOT EXISTS idx_wp_planets_waypoint ON waypoint_planets(waypoint_id);
        CREATE INDEX IF NOT EXISTS idx_wp_planets_role ON waypoint_planets(role);

        -- =========================
        -- ROUTES (computed runs)
        -- =========================
        DROP TABLE IF EXISTS route_detours;
        DROP TABLE IF EXISTS route_waypoints;
        DROP TABLE IF EXISTS routes;

        CREATE TABLE routes (
          id              INTEGER PRIMARY KEY AUTOINCREMENT,
          from_planet_fid INTEGER NOT NULL,
          to_planet_fid   INTEGER NOT NULL,
          algo_version    TEXT NOT NULL,
          options_json    TEXT NOT NULL,
          length          REAL,
          iterations      INTEGER,
          status          TEXT NOT NULL DEFAULT 'ok' CHECK(status IN ('ok','failed')),
          error           TEXT,
          created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
          updated_at      TEXT,
          FOREIGN KEY(from_planet_fid) REFERENCES planets(FID),
          FOREIGN KEY(to_planet_fid)   REFERENCES planets(FID)
        );

        CREATE INDEX IF NOT EXISTS idx_routes_from_to
          ON routes(from_planet_fid, to_planet_fid, created_at);
        CREATE UNIQUE INDEX IF NOT EXISTS ux_routes_from_to
          ON routes(from_planet_fid, to_planet_fid);
        CREATE INDEX IF NOT EXISTS idx_routes_status
          ON routes(status);

        -- =========================
        -- ROUTE WAYPOINTS (polyline)
        -- =========================
        CREATE TABLE route_waypoints (
          route_id     INTEGER NOT NULL,
          seq          INTEGER NOT NULL,
          x            REAL NOT NULL,
          y            REAL NOT NULL,
          waypoint_id  INTEGER,
          PRIMARY KEY(route_id, seq),
          FOREIGN KEY(route_id) REFERENCES routes(id) ON DELETE CASCADE,
          FOREIGN KEY(waypoint_id) REFERENCES waypoints(id) ON DELETE SET NULL
        );

        CREATE INDEX IF NOT EXISTS idx_route_waypoints_route
          ON route_waypoints(route_id);

        -- =========================
        -- ROUTE DETOURS (decisions + score)
        -- =========================
        CREATE TABLE route_detours (
          route_id          INTEGER NOT NULL,
          idx               INTEGER NOT NULL,
          iteration         INTEGER NOT NULL,
          segment_index     INTEGER NOT NULL,
          obstacle_id       INTEGER NOT NULL,
          obstacle_x        REAL NOT NULL,
          obstacle_y        REAL NOT NULL,
          obstacle_radius   REAL NOT NULL,
          closest_t         REAL NOT NULL,
          closest_qx        REAL NOT NULL,
          closest_qy        REAL NOT NULL,
          closest_dist      REAL NOT NULL,
          offset_used       REAL NOT NULL,
          wp_x              REAL NOT NULL,
          wp_y              REAL NOT NULL,
          waypoint_id       INTEGER,
          score_base        REAL NOT NULL,
          score_turn        REAL NOT NULL,
          score_back        REAL NOT NULL,
          score_proximity   REAL NOT NULL,
          score_total       REAL NOT NULL,
          tries_used        INTEGER,
          tries_exhausted   INTEGER NOT NULL DEFAULT 0,
          PRIMARY KEY(route_id, idx),
          FOREIGN KEY(route_id) REFERENCES routes(id) ON DELETE CASCADE,
          FOREIGN KEY(waypoint_id) REFERENCES waypoints(id) ON DELETE SET NULL
        );

        CREATE INDEX IF NOT EXISTS idx_route_detours_route
          ON route_detours(route_id);

        -- =========================
        -- ALIASES (N per planet)
        -- =========================
        DROP TABLE IF EXISTS planet_aliases;

        CREATE TABLE planet_aliases (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            planet_fid  INTEGER NOT NULL,
            alias       TEXT NOT NULL,
            alias_norm  TEXT NOT NULL,
            source      TEXT,
            UNIQUE(planet_fid, alias_norm),
            FOREIGN KEY (planet_fid) REFERENCES planets(FID) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_alias_norm      ON planet_aliases(alias_norm);
        CREATE INDEX IF NOT EXISTS idx_alias_planetfid ON planet_aliases(planet_fid);

        -- =========================
        -- DENORMALIZED SEARCH TABLE
        -- =========================
        DROP TABLE IF EXISTS planet_search;

        CREATE TABLE planet_search (
            planet_fid    INTEGER PRIMARY KEY,
            planet        TEXT NOT NULL,
            planet_norm   TEXT NOT NULL,
            aliases       TEXT,
            aliases_norm  TEXT,
            search_text   TEXT NOT NULL,
            search_norm   TEXT NOT NULL,
            FOREIGN KEY (planet_fid) REFERENCES planets(FID) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_search_planet_norm ON planet_search(planet_norm);
        CREATE INDEX IF NOT EXISTS idx_search_norm        ON planet_search(search_norm);

        -- =========================
        -- CLEAN VIEW
        -- =========================
        DROP VIEW IF EXISTS v_planets_clean;

        CREATE VIEW v_planets_clean AS
        SELECT
            p.FID,
            p.Planet,
            p.Region,
            p.Sector,
            p.System,
            p.Grid,
            p.X AS x_parsec,
            p.Y AS y_parsec,
            p.Canon,
            p.Legends,
            p.status,
            p.ref
        FROM planets p
        WHERE p.status NOT IN ('deleted', 'skipped', 'invalid')
        ORDER BY p.Planet COLLATE NOCASE;

        -- =========================
        -- OPTIONAL FTS5
        -- =========================
        "#,
    )?;

    if enable_fts {
        con.execute_batch(
            r#"
            DROP TABLE IF EXISTS planets_fts;

            CREATE VIRTUAL TABLE planets_fts USING fts5(
                planet_fid UNINDEXED,
                search_norm,
                tokenize = 'unicode61'
            );
            "#,
        )?;
    } else {
        con.execute_batch("DROP TABLE IF EXISTS planets_fts;")?;
    }

    Ok(())
}

fn build_search_row(tx: &Transaction<'_>, fid: i64) -> Result<Option<SearchRow>> {
    let row = tx
        .query_row(
            r#"
            SELECT
                p.FID,
                p.Planet,
                p.planet_norm,
                group_concat(a.alias, ' | ') AS aliases,
                group_concat(a.alias_norm, ' ') AS aliases_norm
            FROM planets p
            LEFT JOIN planet_aliases a ON a.planet_fid = p.FID
            WHERE p.FID = ? AND p.status NOT IN ('deleted', 'skipped', 'invalid')
            GROUP BY p.FID
            "#,
            [fid],
            |r| {
                Ok((
                    r.get::<_, i64>(0)?,
                    r.get::<_, String>(1)?,
                    r.get::<_, String>(2)?,
                    r.get::<_, Option<String>>(3)?,
                    r.get::<_, Option<String>>(4)?,
                ))
            },
        )
        .optional()?;

    let Some((planet_fid, planet, planet_norm, aliases, aliases_norm)) = row else {
        return Ok(None);
    };

    let search_text = format!("{} {}", planet, aliases.clone().unwrap_or_default())
        .trim()
        .to_lowercase();

    let search_norm = format!(
        "{} {}",
        planet_norm,
        aliases_norm.clone().unwrap_or_default()
    )
    .trim()
    .to_string();

    Ok(Some(SearchRow {
        planet_fid,
        planet,
        planet_norm,
        aliases,
        aliases_norm,
        search_text,
        search_norm,
    }))
}

fn compute_arcgis_hash(a: &Value) -> String {
    let keys = [
        "FID",
        "Planet",
        "Region",
        "Sector",
        "System",
        "Grid",
        "X",
        "Y",
        "Canon",
        "Legends",
        "zm",
        "name0",
        "name1",
        "name2",
        "lat",
        "long",
        "ref",
        "status",
        "CRegion",
        "CRegion_li",
    ];

    let mut s = String::new();
    for k in keys {
        s.push_str(k);
        s.push('=');

        match a.get(k) {
            None | Some(Value::Null) => {}
            Some(Value::String(v)) => s.push_str(v.trim()),
            Some(Value::Number(n)) => s.push_str(&n.to_string()),
            Some(Value::Bool(b)) => s.push_str(if *b { "1" } else { "0" }),
            Some(other) => s.push_str(&other.to_string()),
        }

        s.push('\n');
    }

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

fn rebuild_planet_search(tx: &Transaction<'_>) -> Result<()> {
    tx.execute("DELETE FROM planet_search", [])?;

    let mut ins = tx.prepare(
        r#"
        INSERT INTO planet_search(
            planet_fid, planet, planet_norm, aliases, aliases_norm, search_text, search_norm
        ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
        ON CONFLICT(planet_fid) DO UPDATE SET
            planet=excluded.planet,
            planet_norm=excluded.planet_norm,
            aliases=excluded.aliases,
            aliases_norm=excluded.aliases_norm,
            search_text=excluded.search_text,
            search_norm=excluded.search_norm
        "#,
    )?;

    let mut q = tx
        .prepare("SELECT FID FROM planets WHERE status NOT IN ('deleted', 'skipped', 'invalid')")?;
    let fids = q.query_map([], |r| r.get::<_, i64>(0))?;

    for fid in fids {
        let fid = fid?;
        if let Some(sr) = build_search_row(tx, fid)? {
            ins.execute(rusqlite::params![
                sr.planet_fid,
                sr.planet,
                sr.planet_norm,
                sr.aliases,
                sr.aliases_norm,
                sr.search_text,
                sr.search_norm
            ])?;
        }
    }

    Ok(())
}

fn meta_upsert(con: &Connection, key: &str, value: &str) -> Result<()> {
    con.execute(
        r#"
        INSERT INTO meta(key, value) VALUES (?1, ?2)
        ON CONFLICT(key) DO UPDATE SET value=excluded.value
        "#,
        params![key, value],
    )?;
    Ok(())
}

pub fn insert_all(
    con: &mut Connection,
    meta: BuildMeta,
    rows: &[Value],
    enable_fts: bool,
) -> Result<()> {
    let tx = con.transaction()?;

    meta_upsert(&tx, "imported_at_utc", &meta.imported_at_utc)?;
    meta_upsert(&tx, "source_serviceItemId", &meta.source_service_item_id)?;
    meta_upsert(&tx, "dataset_version", &meta.dataset_version)?;
    meta_upsert(&tx, "importer_version", &meta.importer_version)?;
    meta_upsert(&tx, "fts_enabled", if enable_fts { "1" } else { "0" })?;
    meta_upsert(&tx, "schema_version", "12")?;

    let mut skipped_rows: Vec<SkippedUnknownRow> = Vec::new();

    {
        let mut stmt = tx.prepare(
            r#"
            INSERT INTO planets(
                FID, Planet, planet_norm, Region, Sector, System, Grid,
                X, Y, arcgis_hash,
                Canon, Legends, zm,
                name0, name1, name2,
                lat, long, ref, status, CRegion, CRegion_li
            ) VALUES (
                ?1, ?2, ?3, ?4, ?5, ?6, ?7,
                ?8, ?9,
                ?10, ?11, ?12, ?13,
                ?14, ?15, ?16,
                ?17, ?18, ?19, ?20, ?21, ?22
            )
            "#,
        )?;

        let mut stmt_alias = tx.prepare(
            r#"
            INSERT OR IGNORE INTO planet_aliases(planet_fid, alias, alias_norm, source)
            VALUES (?1, ?2, ?3, ?4)
            "#,
        )?;

        for a in rows {
            let fid = a.get("FID").and_then(|v| v.as_i64());
            let planet = a
                .get("Planet")
                .and_then(|v| v.as_str())
                .unwrap_or("")
                .trim()
                .to_string();
            let x = a.get("X").and_then(|v| v.as_f64());
            let y = a.get("Y").and_then(|v| v.as_f64());

            let mut reasons = Vec::new();
            if fid.is_none() {
                reasons.push("missing_fid");
            }
            if planet.is_empty() {
                reasons.push("missing_planet");
            }
            if x.is_none() {
                reasons.push("missing_x");
            }
            if y.is_none() {
                reasons.push("missing_y");
            }

            if !reasons.is_empty() {
                let safe_planet = if planet.is_empty() {
                    "(unknown)".to_string()
                } else {
                    planet.clone()
                };
                let planet_norm = normalize_text(&safe_planet);

                skipped_rows.push(SkippedUnknownRow {
                    fid,
                    planet: safe_planet,
                    planet_norm,
                    x,
                    y,
                    reason: reasons.join(","),
                });

                continue;
            }

            let fid = fid.expect("validated fid");
            let x = x.expect("validated x");
            let y = y.expect("validated y");
            let planet_norm = normalize_text(&planet);

            let get_s = |k: &str| a.get(k).and_then(|v| v.as_str()).map(|s| s.to_string());
            let get_i = |k: &str| a.get(k).and_then(|v| v.as_i64());
            let get_f = |k: &str| a.get(k).and_then(|v| v.as_f64());
            let arcgis_hash = compute_arcgis_hash(a);

            stmt.execute(params![
                fid,
                planet,
                planet_norm,
                get_s("Region"),
                get_s("Sector"),
                get_s("System"),
                get_s("Grid"),
                x,
                y,
                arcgis_hash,
                get_i("Canon"),
                get_i("Legends"),
                get_i("zm"),
                get_s("name0"),
                get_s("name1"),
                get_s("name2"),
                get_f("lat"),
                get_f("long"),
                get_s("ref"),
                get_s("status"),
                get_s("CRegion"),
                get_s("CRegion_li"),
            ])?;

            for (src, key) in [("name0", "name0"), ("name1", "name1"), ("name2", "name2")] {
                if let Some(val) = a.get(key).and_then(|v| v.as_str()) {
                    let val = val.trim();
                    if !val.is_empty() {
                        stmt_alias.execute(params![fid, val, normalize_text(val), src])?;
                    }
                }
            }
        }
    }

    if !skipped_rows.is_empty() {
        let mut stmt_unknown = tx.prepare(
            r#"
            INSERT INTO planets_unknown(fid, planet, planet_norm, x, y, reason)
            VALUES (?1, ?2, ?3, ?4, ?5, ?6)
            "#,
        )?;

        for row in skipped_rows {
            stmt_unknown.execute(params![
                row.fid,
                row.planet,
                row.planet_norm,
                row.x,
                row.y,
                row.reason
            ])?;
        }
    }

    rebuild_planet_search(&tx)?;
    if enable_fts {
        rebuild_planets_fts(&tx)?;
    }

    tx.commit()?;
    Ok(())
}

pub fn has_fts5(con: &Connection) -> bool {
    let ddl = r#"
        CREATE VIRTUAL TABLE IF NOT EXISTS __fts5_test USING fts5(x);
        DROP TABLE __fts5_test;
    "#;

    con.execute_batch(ddl).is_ok()
}

fn rebuild_planets_fts(tx: &Transaction<'_>) -> Result<()> {
    tx.execute("DELETE FROM planets_fts", [])?;
    tx.execute(
        r#"
        INSERT INTO planets_fts(planet_fid, search_norm)
        SELECT s.planet_fid, s.search_norm
        FROM planet_search s
        JOIN planets p ON p.FID = s.planet_fid
        WHERE p.status NOT IN ("deleted", "skipped", "invalid")
        "#,
        [],
    )?;
    Ok(())
}

pub(crate) fn rebuild_planet_search_public(tx: &Transaction<'_>) -> Result<()> {
    rebuild_planet_search(tx)
}

pub(crate) fn rebuild_planets_fts_if_enabled(tx: &Transaction<'_>) -> Result<()> {
    let enabled: Option<String> = tx
        .query_row(
            "SELECT value FROM meta WHERE key = 'fts_enabled'",
            [],
            |r| r.get(0),
        )
        .optional()?;
    if matches!(enabled.as_deref(), Some("1")) {
        rebuild_planets_fts(tx)?;
    }
    Ok(())
}

pub(crate) fn meta_upsert_public(con: &Connection, key: &str, value: &str) -> Result<()> {
    meta_upsert(con, key, value)
}

/// Rebuild the `planet_search` table and (if enabled) the `planets_fts` FTS index.
///
/// This is the public entry-point for `db rebuild-search`.
pub fn rebuild_search_indexes(con: &mut Connection) -> Result<()> {
    let tx = con
        .transaction()
        .context("Failed to start rebuild-search transaction")?;

    rebuild_planet_search_public(&tx)?;
    rebuild_planets_fts_if_enabled(&tx)?;

    tx.commit()
        .context("Failed to commit rebuild-search transaction")?;

    Ok(())
}
