use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "inv_categories";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct CategoryDb {
  #[serde(rename = "categoryID")]
  pub category_id: i32,
  #[serde(rename = "categoryName")]
  pub category_name: String,
  #[serde(rename = "iconID")]
  pub icon_id: Option<i32>,
  pub published: bool,
}

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct CategoryFilter {
  pub category_id: Option<Vec<i32>>,
  pub category_name: Option<Vec<String>>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

impl CategoryDb {
  /**
   * Get a category by its ID.
   */
  pub async fn get_by_id(category_id: i32) -> DbResult<Self> {
    let pool = crate::pool();
    let category = sqlx::query_as::<_, Self>(&format!(
      r#"
      SELECT * FROM {}
      WHERE category_id = $1
      "#,
      TABLE_NAME
    ))
    .bind(category_id)
    .fetch_one(pool)
    .await?;

    Ok(category)
  }

  /**
   * Count the number of categories in the database.
   */
  pub async fn count(filter: &CategoryFilter) -> 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)));
      }
    }
  }

  /**
   * Fetch categories from the database.
   */
  pub async fn get_multiple(params: &CategoryFilter) -> DbResult<Vec<Self>> {
    let page = params.page.unwrap_or(1);
    let limit = params.limit.unwrap_or(100);

    let pool = crate::pool();
    let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
    query = Self::build_query(query, params);
    query.push(" ORDER BY category_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 categories: {}", err);
        return Err(Error::new(
          500,
          format!("Error fetching categories: {}", err),
        ));
      }
    }
  }

  /**
   * Insert a category into the database.
   */
  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "INSERT INTO {} (
        category_id,
        category_name,
        published
      ) VALUES (
        $1, $2, $3
      )",
      TABLE_NAME
    ))
    .bind(self.category_id)
    .bind(&self.category_name)
    .bind(&self.published)
    .execute(pool)
    .await?;
    Ok(())
  }

  /**
   * Insert multiple categories into the database.
   */
  pub async fn insert_multiple(categories: Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..categories.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (category_id, category_name, icon_id, published)
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= categories.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let category = &categories[i + j];
        query
          .push(" (")
          .push_bind(category.category_id)
          .push(", ")
          .push_bind(&category.category_name)
          .push(", ")
          .push_bind(category.icon_id)
          .push(", ")
          .push_bind(category.published)
          .push(") ");
      }
      query
        .push("ON CONFLICT (category_id) DO UPDATE SET ")
        .push("category_name = EXCLUDED.category_name, ")
        .push("icon_id = EXCLUDED.icon_id, ")
        .push("published = EXCLUDED.published");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  /**
   * Update a category in the database.
   */
  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "UPDATE {} SET
        category_name = $2,
        published = $3
      WHERE category_id = $1",
      TABLE_NAME
    ))
    .bind(self.category_id)
    .bind(&self.category_name)
    .bind(&self.published)
    .execute(pool)
    .await?;
    Ok(())
  }

  /**
   * Delete a category from the database.
   */
  pub async fn delete(category_id: i32) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "DELETE FROM {} WHERE category_id = $1",
      TABLE_NAME
    ))
    .bind(category_id)
    .execute(pool)
    .await?;
    Ok(())
  }

  /**
   * Build the query for fetching categories.
   */
  fn build_query<'a>(
    mut query: QueryBuilder<'a, Postgres>,
    filter: &'a CategoryFilter,
  ) -> QueryBuilder<'a, Postgres> {
    let mut has_where = false;

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

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

    query
  }
}