use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "map_systems";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct SystemDb {
  #[serde(rename = "regionId")]
  pub region_id: i32,
  #[serde(rename = "constellationId")]
  pub constellation_id: i32,
  #[serde(rename = "solarSystemId")]
  pub system_id: i32,
  #[serde(rename = "solarSystemName")]
  pub system_name: String,
  pub x: f32,
  pub y: f32,
  pub z: f32,
  #[serde(rename = "xMin")]
  pub x_min: f32,
  #[serde(rename = "xMax")]
  pub x_max: f32,
  #[serde(rename = "yMin")]
  pub y_min: f32,
  #[serde(rename = "yMax")]
  pub y_max: f32,
  #[serde(rename = "zMin")]
  pub z_min: f32,
  #[serde(rename = "zMax")]
  pub z_max: f32,
  pub luminosity: f32,
  pub border: bool,
  pub fringe: bool,
  pub corridor: bool,
  pub hub: bool,
  pub international: bool,
  pub regional: bool,
  #[serde(skip_serializing_if = "Option::is_none")]
  pub constellation: Option<bool>,
  pub security: f32,
  #[serde(rename = "factionId", skip_serializing_if = "Option::is_none")]
  pub faction_id: Option<i32>,
  pub radius: f32,
  #[serde(rename = "sunTypeId", skip_serializing_if = "Option::is_none")]
  pub sun_type_id: Option<i32>,
  #[serde(rename = "securityClass", skip_serializing_if = "Option::is_none")]
  pub security_class: Option<String>,
}

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct SystemFilter {
  #[serde(rename = "systemID")]
  pub system_id: Option<Vec<i32>>,
  #[serde(rename = "systemName")]
  pub system_name: Option<Vec<String>>,
  #[serde(rename = "regionId")]
  pub region_id: Option<Vec<i32>>,
  #[serde(rename = "constellationId")]
  pub constellation_id: Option<Vec<i32>>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

impl SystemDb {
  pub async fn get_by_id(system_id: i32) -> DbResult<Option<Self>> {
    let pool = crate::pool();
    let item = sqlx::query_as::<_, Self>(&format!(
      "SELECT * FROM {} WHERE system_id = $1",
      TABLE_NAME
    ))
    .bind(system_id)
    .fetch_optional(pool)
    .await?;
    Ok(item)
  }

  pub async fn count(filter: &SystemFilter) -> DbResult<i64> {
    let pool = crate::pool();
    let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
    query = Self::build_query(query, filter);
    let query = query.build_query_scalar();

    match query.fetch_one(pool).await {
      Ok(count) => Ok(count),
      Err(err) => {
        log::error!("Error counting items: {}", err);
        return Err(Error::new(500, format!("Error counting items: {}", err)));
      }
    }
  }

  pub async fn get(filter: &SystemFilter) -> DbResult<Vec<Self>> {
    let page = filter.page.unwrap_or(1);
    let limit = filter.limit.unwrap_or(100);

    let pool = crate::pool();
    let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
    query = Self::build_query(query, filter);
    query.push(" ORDER BY system_id LIMIT ");
    query.push_bind(limit);
    query.push(" OFFSET ");
    query.push_bind((page - 1) * limit);
    let query = query.build_query_as();
    match query.fetch_all(pool).await {
      Ok(items) => Ok(items),
      Err(err) => {
        log::error!("Error fetching items: {}", err);
        return Err(Error::new(500, format!("Error fetching items: {}", err)));
      }
    }
  }

  fn build_query<'a>(
    mut query: QueryBuilder<'a, Postgres>,
    filter: &'a SystemFilter,
  ) -> QueryBuilder<'a, Postgres> {
    let mut has_where = false;

    // Parse the type_name query parameter
    if let Some(system_names) = &filter.system_name {
      if !has_where {
        query.push(" WHERE");
        has_where = true;
      } else {
        query.push(" AND");
      }
      query.push(" (");
      for (i, system_name) in system_names.iter().enumerate() {
        if i > 0 {
          query.push(" OR");
        }
        query.push(" system_name ILIKE ");
        query.push_bind(format!("%{}%", system_name));
      }
      query.push(" )");
    }

    // Parse the system_id query parameter
    if let Some(system_ids) = &filter.system_id {
      if !has_where {
        query.push(" WHERE");
        has_where = true;
      } else {
        query.push(" AND");
      }
      query.push(" (");
      for (i, system_id) in system_ids.iter().enumerate() {
        if i > 0 {
          query.push(" OR");
        }
        query.push(" system_id = ");
        query.push_bind(system_id);
      }
      query.push(" )");
    }

    // Parse the region_id query parameter
    if let Some(region_ids) = &filter.region_id {
      if !has_where {
        query.push(" WHERE");
      } else {
        query.push(" AND");
      }
      query.push(" (");
      for (i, region_id) in region_ids.iter().enumerate() {
        if i > 0 {
          query.push(" OR");
        }
        query.push(" region_id = ");
        query.push_bind(region_id);
      }
      query.push(" )");
    }

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "INSERT INTO {} (
        region_id,
        constellation_id,
        system_id,
        system_name,
        x,
        y,
        z,
        x_min,
        x_max,
        y_min,
        y_max,
        z_min,
        z_max,
        luminosity,
        border,
        fringe,
        corridor,
        hub,
        international,
        regional,
        constellation,
        security,
        faction_id,
        radius,
        sun_type_id,
        security_class
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26
      )",
      TABLE_NAME
    ))
    .bind(self.region_id)
    .bind(self.constellation_id)
    .bind(&self.system_id)
    .bind(&self.system_name)
    .bind(self.x)
    .bind(self.y)
    .bind(self.z)
    .bind(self.x_min)
    .bind(self.x_max)
    .bind(self.y_min)
    .bind(self.y_max)
    .bind(self.z_min)
    .bind(self.z_max)
    .bind(self.luminosity)
    .bind(self.border)
    .bind(self.fringe)
    .bind(self.corridor)
    .bind(self.hub)
    .bind(self.international)
    .bind(self.regional)
    .bind(self.constellation)
    .bind(self.security)
    .bind(self.faction_id)
    .bind(self.radius)
    .bind(&self.sun_type_id)
    .bind(&self.security_class)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn insert_multiple(systems: &Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..systems.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (region_id, constellation_id, system_id, system_name, x, y, z, x_min, x_max, y_min, y_max, z_min, z_max, luminosity, border, fringe, corridor, hub, international, regional, constellation, security, faction_id, radius, sun_type_id, security_class)
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= systems.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let system = &systems[i + j];
        query
          .push(" (")
          .push_bind(system.region_id)
          .push(", ")
          .push_bind(system.constellation_id)
          .push(", ")
          .push_bind(system.system_id)
          .push(", ")
          .push_bind(&system.system_name)
          .push(", ")
          .push_bind(system.x)
          .push(", ")
          .push_bind(system.y)
          .push(", ")
          .push_bind(system.z)
          .push(", ")
          .push_bind(system.x_min)
          .push(", ")
          .push_bind(system.x_max)
          .push(", ")
          .push_bind(system.y_min)
          .push(", ")
          .push_bind(system.y_max)
          .push(", ")
          .push_bind(system.z_min)
          .push(", ")
          .push_bind(system.z_max)
          .push(", ")
          .push_bind(system.luminosity)
          .push(", ")
          .push_bind(system.border)
          .push(", ")
          .push_bind(system.fringe)
          .push(", ")
          .push_bind(system.corridor)
          .push(", ")
          .push_bind(system.hub)
          .push(", ")
          .push_bind(system.international)
          .push(", ")
          .push_bind(system.regional)
          .push(", ")
          .push_bind(system.constellation)
          .push(", ")
          .push_bind(system.security)
          .push(", ")
          .push_bind(system.faction_id)
          .push(", ")
          .push_bind(system.radius)
          .push(", ")
          .push_bind(system.sun_type_id)
          .push(", ")
          .push_bind(&system.security_class)
          .push(") ");
      }
      query
        .push("ON CONFLICT (system_id) DO UPDATE SET ")
        .push("region_id = EXCLUDED.region_id, ")
        .push("constellation_id = EXCLUDED.constellation_id, ")
        .push("system_name = EXCLUDED.system_name, ")
        .push("x = EXCLUDED.x, ")
        .push("y = EXCLUDED.y, ")
        .push("z = EXCLUDED.z, ")
        .push("x_min = EXCLUDED.x_min, ")
        .push("x_max = EXCLUDED.x_max, ")
        .push("y_min = EXCLUDED.y_min, ")
        .push("y_max = EXCLUDED.y_max, ")
        .push("z_min = EXCLUDED.z_min, ")
        .push("z_max = EXCLUDED.z_max, ")
        .push("luminosity = EXCLUDED.luminosity, ")
        .push("border = EXCLUDED.border, ")
        .push("fringe = EXCLUDED.fringe, ")
        .push("corridor = EXCLUDED.corridor, ")
        .push("hub = EXCLUDED.hub, ")
        .push("international = EXCLUDED.international, ")
        .push("regional = EXCLUDED.regional, ")
        .push("constellation = EXCLUDED.constellation, ")
        .push("security = EXCLUDED.security, ")
        .push("faction_id = EXCLUDED.faction_id, ")
        .push("radius = EXCLUDED.radius, ")
        .push("sun_type_id = EXCLUDED.sun_type_id, ")
        .push("security_class = EXCLUDED.security_class");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "UPDATE {} SET
        region_id = $2,
        constellation_id = $3,
        system_name = $4,
        x = $5,
        y = $6,
        z = $7,
        x_min = $8,
        x_max = $9,
        y_min = $10,
        y_max = $11,
        z_min = $12,
        z_max = $13,
        luminosity = $14,
        border = $15,
        fringe = $16,
        corridor = $17,
        hub = $18,
        international = $19,
        regional = $20,
        constellation = $21,
        security = $22,
        faction_id = $23,
        radius = $24,
        sun_type_id = $25,
        security_class = $26
      WHERE system_id = $1",
      TABLE_NAME
    ))
    .bind(&self.system_id)
    .bind(self.region_id)
    .bind(self.constellation_id)
    .bind(&self.system_name)
    .bind(self.x)
    .bind(self.y)
    .bind(self.z)
    .bind(self.x_min)
    .bind(self.x_max)
    .bind(self.y_min)
    .bind(self.y_max)
    .bind(self.z_min)
    .bind(self.z_max)
    .bind(self.luminosity)
    .bind(self.border)
    .bind(self.fringe)
    .bind(self.corridor)
    .bind(self.hub)
    .bind(self.international)
    .bind(self.regional)
    .bind(self.constellation)
    .bind(self.security)
    .bind(self.faction_id)
    .bind(self.radius)
    .bind(&self.sun_type_id)
    .bind(&self.security_class)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn delete(system_id: i64) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!("DELETE FROM {} WHERE system_id = $1", TABLE_NAME))
      .bind(system_id)
      .execute(pool)
      .await?;
    Ok(())
  }
}