use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "inv_groups";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct GroupDb {
  #[serde(rename = "groupId")]
  pub group_id: i32,
  #[serde(rename = "categoryId")]
  pub category_id: i32,
  #[serde(rename = "groupName")]
  pub group_name: String,
  #[serde(rename = "iconId")]
  pub icon_id: Option<i32>,
  #[serde(rename = "useBasePrice")]
  pub use_base_price: bool,
  pub anchored: bool,
  pub anchorable: bool,
  #[serde(rename = "fittableNonSingleton")]
  pub fittable_non_singleton: bool,
  pub published: bool,
}

#[derive(Debug, Default, serde::Serialize, serde::Deserialize)]
pub struct GroupFilter {
  #[serde(rename = "groupID")]
  pub group_id: Option<String>,
  #[serde(rename = "categoryID")]
  pub category_id: Option<String>,
  #[serde(rename = "groupName")]
  pub group_name: Option<String>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

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

    Ok(group)
  }

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

    // Parse the group_name query parameter
    if let Some(group_names) = &filter.group_name {
      let group_names: Vec<&str> = group_names.split(',').collect();
      if !has_where {
        query.push(" WHERE");
        has_where = true;
      } else {
        query.push(" AND");
      }
      query.push(" (");
      for (i, group_name) in group_names.iter().enumerate() {
        if i > 0 {
          query.push(" OR");
        }
        query.push(" group_name ILIKE ");
        query.push_bind(format!("%{}%", group_name));
      }
      query.push(" )");
    }

    // Parse the group_id query parameter
    if let Some(ids) = &filter.group_id {
      let ids: Vec<&str> = ids.split(',').collect();
      if !has_where {
        query.push(" WHERE");
        has_where = true;
      } else {
        query.push(" AND");
      }
      query.push(" (");
      for (i, group_id) in ids.iter().enumerate() {
        // Parse the ID as an integer
        let group_id = match group_id.parse::<i32>() {
          Ok(group_id) => group_id,
          Err(_) => continue,
        };
        if i > 0 {
          query.push(" OR");
        }
        query.push(" group_id = ");
        query.push_bind(group_id);
      }
      query.push(" )");
    }

    // Parse the category_id query parameter
    if let Some(ids) = &filter.category_id {
      let ids: Vec<&str> = ids.split(',').collect();
      if !has_where {
        query.push(" WHERE");
      } else {
        query.push(" AND");
      }
      query.push(" (");
      for (i, category_id) in ids.iter().enumerate() {
        // Parse the ID as an integer
        let category_id = match category_id.parse::<i32>() {
          Ok(category_id) => category_id,
          Err(_) => continue,
        };
        if i > 0 {
          query.push(" OR");
        }
        query.push(" category_id = ");
        query.push_bind(category_id);
      }
      query.push(" )");
    }

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "INSERT INTO {} (
        group_id,
        category_id,
        group_name,
        icon_id,
        use_base_price,
        anchored,
        fittable_non_singleton,
        published
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8
      )",
      TABLE_NAME
    ))
    .bind(self.group_id)
    .bind(self.category_id)
    .bind(&self.group_name)
    .bind(&self.icon_id)
    .bind(self.use_base_price)
    .bind(self.anchored)
    .bind(self.fittable_non_singleton)
    .bind(self.published)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn insert_multiple(groups: &Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..groups.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
          INSERT INTO {} (group_id, category_id, group_name, icon_id, use_base_price, anchored, 
          anchorable, fittable_non_singleton, published)
          VALUES
          "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= groups.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let group = &groups[i + j];
        query
          .push(" (")
          .push_bind(group.group_id)
          .push(", ")
          .push_bind(group.category_id)
          .push(", ")
          .push_bind(&group.group_name)
          .push(", ")
          .push_bind(group.icon_id)
          .push(", ")
          .push_bind(group.use_base_price)
          .push(", ")
          .push_bind(group.anchored)
          .push(", ")
          .push_bind(group.anchorable)
          .push(", ")
          .push_bind(group.fittable_non_singleton)
          .push(", ")
          .push_bind(group.published)
          .push(") ");
      }
      query
        .push("ON CONFLICT (group_id) DO UPDATE SET ")
        .push("category_id = EXCLUDED.category_id, ")
        .push("group_name = EXCLUDED.group_name, ")
        .push("icon_id = EXCLUDED.icon_id, ")
        .push("use_base_price = EXCLUDED.use_base_price, ")
        .push("anchored = EXCLUDED.anchored, ")
        .push("anchorable = EXCLUDED.anchorable, ")
        .push("fittable_non_singleton = EXCLUDED.fittable_non_singleton, ")
        .push("published = EXCLUDED.published");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "UPDATE {} SET
        category_id = $2,
        group_name = $3,
        icon_id = $4,
        use_base_price = $5,
        anchored = $6,
        fittable_non_singleton = $7,
        published = $8
      WHERE group_id = $1",
      TABLE_NAME
    ))
    .bind(self.group_id)
    .bind(self.category_id)
    .bind(&self.group_name)
    .bind(&self.icon_id)
    .bind(self.use_base_price)
    .bind(self.anchored)
    .bind(self.fittable_non_singleton)
    .bind(self.published)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn delete(group_id: i32) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query("DELETE FROM item_groups WHERE group_id = $1")
      .bind(group_id)
      .execute(pool)
      .await?;
    Ok(())
  }
}