use log::error;
use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "inv_market_groups";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct MarketGroupDb {
  #[serde(rename = "marketGroupId")]
  pub market_group_id: i32,
  #[serde(rename = "parentGroupId", skip_serializing_if = "Option::is_none")]
  pub parent_group_id: Option<i32>,
  #[serde(rename = "marketGroupName")]
  pub market_group_name: String,
  #[serde(skip_serializing_if = "Option::is_none")]
  pub description: Option<String>,
  #[serde(rename = "iconId")]
  pub icon_id: Option<i32>,
  #[serde(rename = "hasTypes")]
  pub has_types: bool,
}

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

    Ok(market_group)
  }

  pub async fn count(params: &MarketGroupFilter) -> DbResult<i64> {
    let pool = crate::pool();
    let mut query = QueryBuilder::new(format!("SELECT COUNT(*) FROM {}", TABLE_NAME));
    query = Self::build_query(query, params);
    let query = query.build_query_scalar();

    match query.fetch_one(pool).await {
      Ok(count) => Ok(count),
      Err(err) => {
        error!("Error counting market groups: {}", err);
        return Err(Error::new(
          500,
          format!("Error counting market groups: {}", err),
        ));
      }
    }
  }

  pub async fn get_multiple(filter: &MarketGroupFilter) -> 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 market_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(market_groups) => Ok(market_groups),
      Err(err) => {
        error!("Error fetching market groups: {}", err);
        return Err(Error::new(
          500,
          format!("Error fetching market groups: {}", err),
        ));
      }
    }
  }

  fn build_query<'a>(
    mut query: QueryBuilder<'a, Postgres>,
    filter: &'a MarketGroupFilter,
  ) -> QueryBuilder<'a, Postgres> {
    let mut has_where = false;

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

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

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "INSERT INTO {} (
        market_group_id,
        parent_group_id,
        market_group_name,
        description,
        icon_id,
        has_types
      ) VALUES (
        $1, $2, $3, $4, $5, $6
      )",
      TABLE_NAME
    ))
    .bind(self.market_group_id)
    .bind(self.parent_group_id)
    .bind(&self.market_group_name)
    .bind(&self.description)
    .bind(self.icon_id)
    .bind(self.has_types)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn insert_multiple(market_groups: &Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..market_groups.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (market_group_id, parent_group_id, market_group_name, description, icon_id, has_types)
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= market_groups.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let market_group = &market_groups[i + j];
        query
          .push(" (")
          .push_bind(market_group.market_group_id)
          .push(", ")
          .push_bind(market_group.parent_group_id)
          .push(", ")
          .push_bind(&market_group.market_group_name)
          .push(", ")
          .push_bind(&market_group.description)
          .push(", ")
          .push_bind(market_group.icon_id)
          .push(", ")
          .push_bind(market_group.has_types)
          .push(") ");
      }

      query
        .push("ON CONFLICT (market_group_id) DO UPDATE SET ")
        .push("parent_group_id = EXCLUDED.parent_group_id, ")
        .push("market_group_name = EXCLUDED.market_group_name, ")
        .push("description = EXCLUDED.description, ")
        .push("icon_id = EXCLUDED.icon_id, ")
        .push("has_types = EXCLUDED.has_types");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "UPDATE {} SET
        parent_group_id = $2,
        market_group_name = $3,
        description = $4,
        icon_id = $5,
        has_types = $6
      WHERE market_group_id = $1",
      TABLE_NAME
    ))
    .bind(self.market_group_id)
    .bind(&self.parent_group_id)
    .bind(&self.market_group_name)
    .bind(&self.description)
    .bind(&self.icon_id)
    .bind(&self.has_types)
    .execute(pool)
    .await?;
    Ok(())
  }

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

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct MarketGroupFilter {
  #[serde(rename = "marketGroupId")]
  pub market_group_id: Option<String>,
  #[serde(rename = "marketGroupName")]
  pub market_group_name: Option<String>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}