geolite 0.0.12

use geo::Geometry;
use rusqlite::Connection;

use super::entity::house_number_link;
use super::policy::house_number_policy;
use super::value::house_number;
use crate::admin_level::geometry::admin_geometry;
use crate::database::table;

const SQL_CREATE: &str = "
  CREATE TABLE IF NOT EXISTS house_numbers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id INTEGER NOT NULL UNIQUE,
    admin_level_id INTEGER NOT NULL REFERENCES admin_levels(id) ON DELETE CASCADE,
    number VARCHAR(12) NOT NULL,
    wkb BLOB,
    strategy INTEGER NOT NULL
  );
";

const SQL_CREATE_INDEXES: &str = "
  CREATE INDEX IF NOT EXISTS house_numbers_search_by_admin_level_id ON house_numbers(admin_level_id);
";

const SQL_DROP: &str = "DROP TABLE IF EXISTS house_numbers;";

const SQL_DROP_INDEXES: &str = "
  DROP INDEX IF EXISTS house_numbers_search_by_admin_level_id;
";

pub struct house_numbers;

impl table for house_numbers {
  const CREATE: &str = SQL_CREATE;
  const INDEXES: &str = SQL_CREATE_INDEXES;
}

pub(crate) fn drop_table(conn: &Connection) {
  conn
    .execute_batch(SQL_DROP)
    .expect("failed to drop house_numbers");
}

pub fn drop_indexes(conn: &Connection) {
  conn
    .execute_batch(SQL_DROP_INDEXES)
    .expect("failed to drop house_numbers indexes");
}

pub struct candidate_row {
  pub id: u64,
  pub number: house_number,
  pub addr_street: Option<String>,
  pub lon: f64,
  pub lat: f64,
}

pub fn load_all_candidates(conn: &Connection, policy: &house_number_policy) -> Vec<candidate_row> {
  const SQL_LOAD_ALL_CANDIDATES: &str = "
    SELECT
      id,
      CAST({number_select} AS TEXT) AS number,
      {street_select} AS addr_street,
      CAST(payload->>'lon' AS REAL) AS lon,
      CAST(payload->>'lat' AS REAL) AS lat
    FROM osm_data.osm_nodes
    WHERE {number_select} IS NOT NULL
  ";

  debug_assert!(
    !policy.number_tags.is_empty(),
    "number_tags must not be empty"
  );
  let number_select = crate::osm_tag::select::coalesce_of("payload", policy.number_tags);
  let street_select = crate::osm_tag::select::coalesce_of("payload", policy.street_tags);
  let sql = SQL_LOAD_ALL_CANDIDATES
    .replace("{number_select}", &number_select)
    .replace("{street_select}", &street_select);
  let mut stmt = conn
    .prepare(&sql)
    .expect("failed to prepare load all candidates");
  stmt
    .query_map([], |row| {
      Ok((
        row.get::<_, u64>(0)?,
        row.get::<_, String>(1)?,
        row.get::<_, Option<String>>(2)?,
        row.get::<_, f64>(3)?,
        row.get::<_, f64>(4)?,
      ))
    })
    .expect("failed to query candidates")
    .map(|r| r.expect("failed to read candidate row"))
    .filter_map(|(id, raw, addr_street, lon, lat)| {
      house_number::normalize(&raw, policy).map(|number| candidate_row {
        id,
        number,
        addr_street,
        lon,
        lat,
      })
    })
    .collect()
}

pub struct hn_for_street {
  pub admin_level_id: i64,
  pub number: house_number,
  pub wkb: Option<admin_geometry>,
}

pub fn by_admin_level_ids(conn: &Connection, ids: &[i64]) -> Vec<hn_for_street> {
  const SQL_BY_ADMIN_LEVEL_IDS: &str = "
    SELECT admin_level_id, number, wkb
    FROM house_numbers
    WHERE admin_level_id IN
  ";

  if ids.is_empty() {
    return vec![];
  }
  let sql = format!(
    "{} ({})\n    ORDER BY node_id",
    SQL_BY_ADMIN_LEVEL_IDS.trim(),
    crate::database::placeholders_for(ids.len())
  );
  crate::database::query_by_ids(conn, &sql, ids.iter().copied(), |row| {
    Ok(hn_for_street {
      admin_level_id: row.get(0)?,
      number: house_number::from_stored(&row.get::<_, String>(1)?),
      wkb: row.get(2)?,
    })
  })
}

pub fn batch_insert_links(conn: &Connection, links: &[house_number_link]) -> i64 {
  const SQL_INSERT: &str = "
    INSERT OR IGNORE INTO house_numbers (
      node_id,
      admin_level_id,
      number,
      wkb,
      strategy
    ) VALUES (
      ?1,
      ?2,
      ?3,
      ?4,
      ?5
    );
  ";

  if links.is_empty() {
    return 0;
  }
  let tx = conn
    .unchecked_transaction()
    .expect("failed to begin transaction");
  let mut total: i64 = 0;
  {
    let mut stmt = tx
      .prepare(SQL_INSERT)
      .expect("failed to prepare house_numbers insert");
    for link in links {
      let wkb: admin_geometry = Geometry::Point(link.point).into();
      let changes = stmt
        .execute(rusqlite::params![
          link.node_id,
          link.street_id.raw() as i64,
          link.number.stored_form(),
          wkb,
          link.strategy.code(),
        ])
        .expect("failed to insert house_number");
      total += changes as i64;
    }
  }
  tx.commit().expect("failed to commit");
  total
}