use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "inv_types";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct TypeDb {
  #[serde(rename = "typeId")]
  pub type_id: i32,
  #[serde(rename = "groupId")]
  pub group_id: i32,
  #[serde(rename = "typeName")]
  pub type_name: String,
  #[serde(rename = "description", skip_serializing_if = "Option::is_none")]
  pub description: Option<String>,
  pub mass: f32,
  pub volume: f32,
  pub capacity: f32,
  #[serde(rename = "portionSize")]
  pub portion_size: i32,
  #[serde(rename = "raceId", skip_serializing_if = "Option::is_none")]
  pub race_id: Option<i32>,
  #[serde(rename = "basePrice", skip_serializing_if = "Option::is_none")]
  pub base_price: Option<f32>,
  pub published: bool,
  #[serde(rename = "marketGroupId", skip_serializing_if = "Option::is_none")]
  pub market_group_id: Option<i32>,
  #[serde(rename = "iconId", skip_serializing_if = "Option::is_none")]
  pub icon_id: Option<i32>,
  #[serde(rename = "soundId", skip_serializing_if = "Option::is_none")]
  pub sound_id: Option<i32>,
  #[serde(rename = "graphicId", skip_serializing_if = "Option::is_none")]
  pub graphic_id: Option<i32>,
}

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

    Ok(item)
  }

  pub async fn get_by_names(type_names: Vec<String>) -> DbResult<Vec<Self>> {
    let pool = crate::pool();
    let mut query = QueryBuilder::new(format!("SELECT * FROM {}", TABLE_NAME));
    query.push(" WHERE");
    // Do a && overlaps query
    query.push(" LOWER(type_name) IN (");
    for (i, type_name) in type_names.iter().enumerate() {
      if i > 0 {
        query.push(", ");
      }
      query.push_bind(type_name.to_lowercase());
    }
    query.push(")");
    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)));
      }
    }
  }

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

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

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

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

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      "INSERT INTO {} (
        type_id,
        group_id,
        type_name,
        description,
        mass,
        volume,
        capacity,
        portion_size,
        race_id,
        base_price,
        published,
        market_group_id,
        icon_id,
        sound_id,
        graphic_id
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15
      )",
      TABLE_NAME
    ))
    .bind(self.type_id)
    .bind(self.group_id)
    .bind(&self.type_name)
    .bind(&self.description)
    .bind(self.mass)
    .bind(self.volume)
    .bind(self.capacity)
    .bind(self.portion_size)
    .bind(self.race_id)
    .bind(self.base_price)
    .bind(self.published)
    .bind(self.market_group_id)
    .bind(self.icon_id)
    .bind(self.sound_id)
    .bind(self.graphic_id)
    .execute(pool)
    .await?;
    Ok(())
  }

  pub async fn insert_multiple(types: &Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..types.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (type_id, group_id, type_name, description, mass, volume, capacity, 
        portion_size, race_id, base_price, published, market_group_id, icon_id, sound_id, graphic_id)
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= types.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let type_ = &types[i + j];
        query
          .push(" (")
          .push_bind(type_.type_id)
          .push(", ")
          .push_bind(type_.group_id)
          .push(", ")
          .push_bind(&type_.type_name)
          .push(", ")
          .push_bind(&type_.description)
          .push(", ")
          .push_bind(type_.mass)
          .push(", ")
          .push_bind(type_.volume)
          .push(", ")
          .push_bind(type_.capacity)
          .push(", ")
          .push_bind(type_.portion_size)
          .push(", ")
          .push_bind(type_.race_id)
          .push(", ")
          .push_bind(type_.base_price)
          .push(", ")
          .push_bind(type_.published)
          .push(", ")
          .push_bind(type_.market_group_id)
          .push(", ")
          .push_bind(type_.icon_id)
          .push(", ")
          .push_bind(type_.sound_id)
          .push(", ")
          .push_bind(type_.graphic_id)
          .push(") ");
      }
      query
        .push("ON CONFLICT (type_id) DO UPDATE SET ")
        .push("group_id = EXCLUDED.group_id, ")
        .push("type_name = EXCLUDED.type_name, ")
        .push("description = EXCLUDED.description, ")
        .push("mass = EXCLUDED.mass, ")
        .push("volume = EXCLUDED.volume, ")
        .push("capacity = EXCLUDED.capacity, ")
        .push("portion_size = EXCLUDED.portion_size, ")
        .push("race_id = EXCLUDED.race_id, ")
        .push("base_price = EXCLUDED.base_price, ")
        .push("published = EXCLUDED.published, ")
        .push("market_group_id = EXCLUDED.market_group_id, ")
        .push("icon_id = EXCLUDED.icon_id, ")
        .push("sound_id = EXCLUDED.sound_id, ")
        .push("graphic_id = EXCLUDED.graphic_id");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(
      "UPDATE items SET
        group_id = $2,
        type_name = $3,
        description = $4,
        mass = $5,
        volume = $6,
        capacity = $7,
        portion_size = $8,
        race_id = $9,
        base_price = $10,
        published = $11,
        market_group_id = $12,
        icon_id = $13,
        sound_id = $14,
        graphic_id = $15
      WHERE type_id = $1",
    )
    .bind(self.type_id)
    .bind(self.group_id)
    .bind(&self.type_name)
    .bind(&self.description)
    .bind(self.mass)
    .bind(self.volume)
    .bind(self.capacity)
    .bind(self.portion_size)
    .bind(self.race_id)
    .bind(self.base_price)
    .bind(self.published)
    .bind(self.market_group_id)
    .bind(self.icon_id)
    .bind(self.sound_id)
    .bind(self.graphic_id)
    .execute(pool)
    .await?;
    Ok(())
  }

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

#[derive(Debug, Default, serde::Serialize, serde::Deserialize)]
pub struct TypeFilter {
  #[serde(rename = "typeID")]
  pub type_id: Option<Vec<i32>>,
  #[serde(rename = "typeName")]
  pub type_name: Option<Vec<String>>,
  #[serde(rename = "groupID")]
  pub group_id: Option<Vec<i32>>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}