use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "map_regions";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct RegionDb {
  #[serde(rename = "regionId")]
  pub region_id: i32,
  #[serde(rename = "regionName")]
  pub region_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,
  #[serde(rename = "factionId", skip_serializing_if = "Option::is_none")]
  pub faction_id: Option<i32>,
  pub nebula: i32,
  #[serde(skip_serializing_if = "Option::is_none")]
  pub radius: Option<f32>,
}

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct RegionFilter {
  #[serde(rename = "regionId")]
  pub region_id: Option<Vec<i32>>,
  #[serde(rename = "regionName")]
  pub region_name: Option<Vec<String>>,
  pub include_extra: Option<bool>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

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

  pub async fn count(filter: &RegionFilter) -> 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: &RegionFilter) -> 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 region_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 RegionFilter,
  ) -> QueryBuilder<'a, Postgres> {
    let mut has_where = false;

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

    // Parse the region_id query parameter
    if let Some(region_ids) = &filter.region_id {
      if !has_where {
        query.push(" WHERE");
        has_where = true;
      } 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(" )");
    }

    // Parse the include_extra query parameter
    let include_extra = filter.include_extra.unwrap_or(false);
    if !include_extra {
      if !has_where {
        query.push(" WHERE");
      } else {
        query.push(" AND");
      }
      // New Eden regions have IDs from 10000000 to 10999999
      query.push(" region_id < 11000000");
    }

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "INSERT INTO {} (
        region_id,
        region_name,
        x,
        y,
        z,
        x_min,
        x_max,
        y_min,
        y_max,
        z_min,
        z_max,
        faction_id,
        nebula,
        radius
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14
      )",
      TABLE_NAME
    ))
    .bind(self.region_id)
    .bind(&self.region_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.faction_id)
    .bind(self.nebula)
    .bind(self.radius)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn insert_multiple(regions: &Vec<RegionDb>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..regions.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (region_id, region_name, x, y, z, x_min, x_max, y_min, y_max, z_min, z_max, 
          faction_id, nebula, radius)
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= regions.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let region = &regions[i + j];
        query
          .push(" (")
          .push_bind(region.region_id)
          .push(", ")
          .push_bind(&region.region_name)
          .push(", ")
          .push_bind(region.x)
          .push(", ")
          .push_bind(region.y)
          .push(", ")
          .push_bind(region.z)
          .push(", ")
          .push_bind(region.x_min)
          .push(", ")
          .push_bind(region.x_max)
          .push(", ")
          .push_bind(region.y_min)
          .push(", ")
          .push_bind(region.y_max)
          .push(", ")
          .push_bind(region.z_min)
          .push(", ")
          .push_bind(region.z_max)
          .push(", ")
          .push_bind(region.faction_id)
          .push(", ")
          .push_bind(region.nebula)
          .push(", ")
          .push_bind(region.radius)
          .push(") ");
      }

      query
        .push("ON CONFLICT (region_id) DO UPDATE SET ")
        .push("region_name = EXCLUDED.region_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("faction_id = EXCLUDED.faction_id, ")
        .push("nebula = EXCLUDED.nebula, ")
        .push("radius = EXCLUDED.radius");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "UPDATE {} SET
        region_name = $2,
        x = $3,
        y = $4,
        z = $5,
        x_min = $6,
        x_max = $7,
        y_min = $8,
        y_max = $9,
        z_min = $10,
        z_max = $11,
        faction_id = $12,
        nebula = $13,
        radius = $14
      WHERE region_id = $1",
      TABLE_NAME
    ))
    .bind(self.region_id)
    .bind(&self.region_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.faction_id)
    .bind(self.nebula)
    .bind(self.radius)
    .execute(pool)
    .await?;
    Ok(())
  }

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