use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "map_system_jumps";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct SystemJumpDb {
  #[serde(rename = "fromRegionId")]
  pub from_region_id: i32,
  #[serde(rename = "fromConstellationId")]
  pub from_constellation_id: i32,
  #[serde(rename = "fromSystemId")]
  pub from_system_id: i32,
  #[serde(rename = "toSystemId")]
  pub to_system_id: i32,
  #[serde(rename = "toConstellationId")]
  pub to_constellation_id: i32,
  #[serde(rename = "toRegionId")]
  pub to_region_id: i32,
}

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct SystemJumpFilter {
  #[serde(rename = "fromRegionId")]
  pub from_system_id: Option<Vec<i32>>,
  #[serde(rename = "toRegionId")]
  pub to_system_id: Option<Vec<i32>>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

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

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

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

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

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "INSERT INTO {} (
        from_system_id, to_system_id, from_constellation_id, to_constellation_id, 
        from_region_id, to_region_id
      ) VALUES ($1, $2, $3, $4, $5, $6)",
      TABLE_NAME
    ))
    .bind(self.from_system_id)
    .bind(self.to_system_id)
    .bind(self.from_constellation_id)
    .bind(self.to_constellation_id)
    .bind(self.from_region_id)
    .bind(self.to_region_id)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn insert_multiple(system_jumps: &Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..system_jumps.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (from_region_id, from_constellation_id, from_solar_system_id, 
          to_solar_system_id, to_constellation_id, to_region_id)
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= system_jumps.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }

        let system_jump = &system_jumps[i + j];
        query
          .push(" (")
          .push_bind(system_jump.from_region_id)
          .push(", ")
          .push_bind(system_jump.from_constellation_id)
          .push(", ")
          .push_bind(system_jump.from_system_id)
          .push(", ")
          .push_bind(system_jump.to_system_id)
          .push(", ")
          .push_bind(system_jump.to_constellation_id)
          .push(", ")
          .push_bind(system_jump.to_region_id)
          .push(") ");
      }
      query
        .push(" ON CONFLICT (from_solar_system_id, to_solar_system_id) DO UPDATE SET ")
        .push("from_constellation_id = EXCLUDED.from_constellation_id, ")
        .push("from_region_id = EXCLUDED.from_region_id, ")
        .push("to_constellation_id = EXCLUDED.to_constellation_id, ")
        .push("to_region_id = EXCLUDED.to_region_id");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "UPDATE {} SET
        from_system_id = $1, to_system_id = $2, from_constellation_id = $3, to_constellation_id = $4, 
        from_region_id = $5, to_region_id = $6
      WHERE from_system_id = $7",
      TABLE_NAME
    ))
    .bind(self.from_system_id)
    .bind(self.to_system_id)
    .bind(self.from_constellation_id)
    .bind(self.to_constellation_id)
    .bind(self.from_region_id)
    .bind(self.to_region_id)
    .bind(self.from_system_id)
    .execute(pool)
    .await?;
    Ok(())
  }

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