use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "npc_factions";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct FactionDb {
  #[serde(rename = "factionId")]
  pub faction_id: i32,
  #[serde(rename = "factionName")]
  pub faction_name: String,
  pub description: String,
  #[serde(rename = "raceId")]
  pub race_id: i32,
  #[serde(rename = "solarSystemId")]
  pub system_id: i32,
  #[serde(rename = "corporationId", skip_serializing_if = "Option::is_none")]
  pub corporation_id: Option<i32>,
  #[serde(rename = "sizeFactor")]
  pub size_factor: f64,
  #[serde(rename = "stationCount", skip_serializing_if = "Option::is_none")]
  pub station_count: Option<i32>,
  #[serde(rename = "stationSystemCount", skip_serializing_if = "Option::is_none")]
  pub station_system_count: Option<i32>,
  #[serde(
    rename = "militiaCorporationId",
    skip_serializing_if = "Option::is_none"
  )]
  pub militia_corporation_id: Option<i32>,
  #[serde(rename = "iconId")]
  pub icon_id: i32,
}

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct FactionFilter {
  #[serde(rename = "factionId")]
  pub faction_id: Option<Vec<i32>>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

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

    Ok(faction)
  }

  pub async fn count(filter: &FactionFilter) -> 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: &FactionFilter) -> 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 faction_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(factions) => Ok(factions),
      Err(err) => {
        log::error!("Error getting items: {}", err);
        return Err(Error::new(500, format!("Error getting items: {}", err)));
      }
    }
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    let query = format!(
      r#"
      INSERT INTO {} (
        faction_id,
        faction_name,
        description,
        race_id,
        system_id,
        corporation_id,
        size_factor,
        station_count,
        station_system_count,
        militia_corporation_id,
        icon_id
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
      "#,
      TABLE_NAME
    );
    sqlx::query(&query)
      .bind(&self.faction_id)
      .bind(&self.faction_name)
      .bind(&self.description)
      .bind(&self.race_id)
      .bind(&self.system_id)
      .bind(&self.corporation_id)
      .bind(&self.size_factor)
      .bind(&self.station_count)
      .bind(&self.station_system_count)
      .bind(&self.militia_corporation_id)
      .bind(&self.icon_id)
      .execute(pool)
      .await?;
    Ok(())
  }

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

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

  pub async fn insert_multiple(factions: &Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..factions.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (faction_id, faction_name, description, race_id, system_id, corporation_id, 
          size_factor, station_count, station_system_count, militia_corporation_id, icon_id)
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= factions.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let faction = &factions[i + j];
        query
          .push(" (")
          .push_bind(faction.faction_id)
          .push(", ")
          .push_bind(&faction.faction_name)
          .push(", ")
          .push_bind(&faction.description)
          .push(", ")
          .push_bind(faction.race_id)
          .push(", ")
          .push_bind(faction.system_id)
          .push(", ")
          .push_bind(faction.corporation_id)
          .push(", ")
          .push_bind(faction.size_factor)
          .push(", ")
          .push_bind(faction.station_count)
          .push(", ")
          .push_bind(faction.station_system_count)
          .push(", ")
          .push_bind(faction.militia_corporation_id)
          .push(", ")
          .push_bind(faction.icon_id)
          .push(") ");
      }
      query
        .push("ON CONFLICT (faction_id) DO UPDATE SET ")
        .push("faction_name = EXCLUDED.faction_name, ")
        .push("description = EXCLUDED.description, ")
        .push("race_id = EXCLUDED.race_id, ")
        .push("system_id = EXCLUDED.system_id, ")
        .push("corporation_id = EXCLUDED.corporation_id, ")
        .push("size_factor = EXCLUDED.size_factor, ")
        .push("station_count = EXCLUDED.station_count, ")
        .push("station_system_count = EXCLUDED.station_system_count, ")
        .push("militia_corporation_id = EXCLUDED.militia_corporation_id, ")
        .push("icon_id = EXCLUDED.icon_id");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    let query = format!(
      r#"
      UPDATE {} SET
        faction_name = $2,
        description = $3,
        race_id = $4,
        system_id = $5,
        corporation_id = $6,
        size_factor = $7,
        station_count = $8,
        station_system_count = $9,
        militia_corporation_id = $10,
        icon_id = $11
      WHERE faction_id = $1
      "#,
      TABLE_NAME
    );
    sqlx::query(&query)
      .bind(&self.faction_id)
      .bind(&self.faction_name)
      .bind(&self.description)
      .bind(&self.race_id)
      .bind(&self.system_id)
      .bind(&self.corporation_id)
      .bind(&self.size_factor)
      .bind(&self.station_count)
      .bind(&self.station_system_count)
      .bind(&self.militia_corporation_id)
      .bind(&self.icon_id)
      .execute(pool)
      .await?;
    Ok(())
  }

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