use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "map_constellations";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct ConstellationDb {
  #[serde(rename = "regionId")]
  pub region_id: i32,
  #[serde(rename = "constellationId")]
  pub constellation_id: i32,
  #[serde(rename = "constellationName")]
  pub constellation_name: String,
  pub x: f64,
  pub y: f64,
  pub z: f64,
  #[serde(rename = "xMin")]
  pub x_min: f64,
  #[serde(rename = "xMax")]
  pub x_max: f64,
  #[serde(rename = "yMin")]
  pub y_min: f64,
  #[serde(rename = "yMax")]
  pub y_max: f64,
  #[serde(rename = "zMin")]
  pub z_min: f64,
  #[serde(rename = "zMax")]
  pub z_max: f64,
  #[serde(rename = "factionId", skip_serializing_if = "Option::is_none")]
  pub faction_id: Option<i32>,
  pub radius: f64,
}

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

impl ConstellationDb {
  pub async fn get_by_id(id: i32) -> DbResult<Self> {
    let pool: &sqlx::Pool<sqlx::Postgres> = crate::pool();
    let row = sqlx::query_as::<_, Self>(&format!(
      "SELECT * FROM {} WHERE constellation_id = $1",
      TABLE_NAME
    ))
    .bind(id)
    .fetch_one(pool)
    .await?;
    Ok(row)
  }

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

    // Parse the constellation_id query parameter
    if let Some(constellation_ids) = &filter.constellation_id {
      if !has_where {
        query.push(" WHERE");
        has_where = true;
      } else {
        query.push(" AND");
      }
      query.push(" (");
      for (i, constellation_id) in constellation_ids.iter().enumerate() {
        if i > 0 {
          query.push(" OR");
        }
        query.push(" constellation_id ILIKE ");
        query.push_bind(format!("%{}%", constellation_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, constellation_name, x, y, z, 
        x_min, x_max, y_min, y_max, z_min, z_max, faction_id, radius
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14
      )",
      TABLE_NAME
    ))
    .bind(&self.region_id)
    .bind(&self.constellation_id)
    .bind(&self.constellation_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.radius)
    .execute(pool)
    .await?;
    Ok(())
  }

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

      for j in 0..step {
        if i + j >= constellations.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let constellation = &constellations[i + j];
        query
          .push(" (")
          .push_bind(constellation.region_id)
          .push(", ")
          .push_bind(constellation.constellation_id)
          .push(", ")
          .push_bind(&constellation.constellation_name)
          .push(", ")
          .push_bind(constellation.x)
          .push(", ")
          .push_bind(constellation.y)
          .push(", ")
          .push_bind(constellation.z)
          .push(", ")
          .push_bind(constellation.x_min)
          .push(", ")
          .push_bind(constellation.x_max)
          .push(", ")
          .push_bind(constellation.y_min)
          .push(", ")
          .push_bind(constellation.y_max)
          .push(", ")
          .push_bind(constellation.z_min)
          .push(", ")
          .push_bind(constellation.z_max)
          .push(", ")
          .push_bind(constellation.faction_id)
          .push(", ")
          .push_bind(constellation.radius)
          .push(") ");
      }
      query
        .push("ON CONFLICT (constellation_id) DO UPDATE SET ")
        .push("region_id = EXCLUDED.region_id, ")
        .push("constellation_name = EXCLUDED.constellation_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("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_id = $2,
        constellation_name = $3,
        x = $4, 
        y = $5,
        z = $6, 
        x_min = $7,
        x_max = $8,
        y_min = $9,
        y_max = $10,
        z_min = $11
        z_max = $12,
        faction_id = $13,
        radius = $14
      WHERE constellation_id = $1",
      TABLE_NAME
    ))
    .bind(&self.constellation_id)
    .bind(&self.region_id)
    .bind(&self.constellation_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.radius)
    .fetch_one(pool)
    .await?;
    Ok(())
  }

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