use sqlx::{Postgres, QueryBuilder};

use crate::DbResult;

const TABLE_NAME: &str = "npc_corporations";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct CorporationDb {
  #[serde(rename = "corporationId")]
  pub corporation_id: i32,
  #[serde(rename = "corporationName")]
  pub corporation_name: String,
  pub size: String,
  pub extent: String,
  #[serde(rename = "solarSystemId", skip_serializing_if = "Option::is_none")]
  pub system_id: Option<i32>,
  #[serde(rename = "friendId", skip_serializing_if = "Option::is_none")]
  pub friend_id: Option<i32>,
  #[serde(rename = "enemyId", skip_serializing_if = "Option::is_none")]
  pub enemy_id: Option<i32>,
  #[serde(rename = "publicShares")]
  pub public_shares: i32,
  #[serde(rename = "initialPrice")]
  pub initial_price: i32,
  #[serde(rename = "minSecurity")]
  pub min_security: f32,
  #[serde(rename = "factionId", skip_serializing_if = "Option::is_none")]
  pub faction_id: Option<i32>,
  #[serde(rename = "sizeFactor", skip_serializing_if = "Option::is_none")]
  pub size_factor: Option<f32>,
  #[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>,
  pub description: String,
  #[serde(rename = "iconId", skip_serializing_if = "Option::is_none")]
  pub icon_id: Option<i32>,
}

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct CorporationFilter {
  #[serde(rename = "corporationId")]
  pub corporation_id: Option<String>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

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

    Ok(corporation)
  }

  pub async fn count(filter: &CorporationFilter) -> 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();

    let count = query.fetch_one(pool).await?;
    Ok(count)
  }

  pub async fn get_multiple(filter: &CorporationFilter) -> 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 corporation_id LIMIT ");
    query.push_bind(limit);
    query.push(" OFFSET ");
    query.push_bind((page - 1) * limit);
    let query = query.build_query_as();

    let corporations = query.fetch_all(pool).await?;
    Ok(corporations)
  }

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

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

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      r#"
        INSERT INTO {} (
          corporation_id,
          corporation_name,
          size,
          extent,
          system_id,
          friend_id,
          enemy_id,
          public_shares,
          initial_price,
          min_security,
          faction_id,
          size_factor,
          station_count,
          station_system_count,
          description,
          icon_id
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, 
          $13, $14, $15, $16
        )
        "#,
      TABLE_NAME
    ))
    .bind(&self.corporation_id)
    .bind(&self.corporation_name)
    .bind(&self.size)
    .bind(&self.extent)
    .bind(&self.system_id)
    .bind(&self.friend_id)
    .bind(&self.enemy_id)
    .bind(&self.public_shares)
    .bind(&self.initial_price)
    .bind(&self.min_security)
    .bind(&self.faction_id)
    .bind(&self.size_factor)
    .bind(&self.station_count)
    .bind(&self.station_system_count)
    .bind(&self.description)
    .bind(&self.icon_id)
    .execute(pool)
    .await?;
    Ok(())
  }

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

      for j in 0..step {
        if i + j >= corporations.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let corporation = &corporations[i + j];
        query
          .push(" (")
          .push_bind(corporation.corporation_id)
          .push(", ")
          .push_bind(&corporation.corporation_name)
          .push(", ")
          .push_bind(&corporation.size)
          .push(", ")
          .push_bind(&corporation.extent)
          .push(", ")
          .push_bind(corporation.system_id)
          .push(", ")
          .push_bind(corporation.friend_id)
          .push(", ")
          .push_bind(corporation.enemy_id)
          .push(", ")
          .push_bind(corporation.public_shares)
          .push(", ")
          .push_bind(corporation.initial_price)
          .push(", ")
          .push_bind(corporation.min_security)
          .push(", ")
          .push_bind(corporation.faction_id)
          .push(", ")
          .push_bind(corporation.size_factor)
          .push(", ")
          .push_bind(corporation.station_count)
          .push(", ")
          .push_bind(corporation.station_system_count)
          .push(", ")
          .push_bind(&corporation.description)
          .push(", ")
          .push_bind(corporation.icon_id)
          .push(") ");
      }
      query
        .push("ON CONFLICT (corporation_id) DO UPDATE SET ")
        .push("corporation_name = EXCLUDED.corporation_name, ")
        .push("size = EXCLUDED.size, ")
        .push("extent = EXCLUDED.extent, ")
        .push("system_id = EXCLUDED.system_id, ")
        .push("friend_id = EXCLUDED.friend_id, ")
        .push("enemy_id = EXCLUDED.enemy_id, ")
        .push("public_shares = EXCLUDED.public_shares, ")
        .push("initial_price = EXCLUDED.initial_price, ")
        .push("min_security = EXCLUDED.min_security, ")
        .push("faction_id = EXCLUDED.faction_id, ")
        .push("size_factor = EXCLUDED.size_factor, ")
        .push("station_count = EXCLUDED.station_count, ")
        .push("station_system_count = EXCLUDED.station_system_count, ")
        .push("description = EXCLUDED.description, ")
        .push("icon_id = EXCLUDED.icon_id");
      query.build().execute(pool).await?;
    }
    Ok(())
  }

  pub async fn update(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      r#"
        UPDATE {} SET
          corporation_name = $2,
          size = $3,
          extent = $4,
          system_id = $5,
          friend_id = $6,
          enemy_id = $7,
          public_shares = $8,
          initial_price = $9,
          min_security = $10,
          faction_id = $11,
          size_factor = $12,
          station_count = $13,
          station_system_count = $14,
          description = $15,
          icon_id = $16
        WHERE corporation_id = $1
        "#,
      TABLE_NAME
    ))
    .bind(&self.corporation_id)
    .bind(&self.corporation_name)
    .bind(&self.size)
    .bind(&self.extent)
    .bind(&self.system_id)
    .bind(&self.friend_id)
    .bind(&self.enemy_id)
    .bind(&self.public_shares)
    .bind(&self.initial_price)
    .bind(&self.min_security)
    .bind(&self.faction_id)
    .bind(&self.size_factor)
    .bind(&self.station_count)
    .bind(&self.station_system_count)
    .bind(&self.description)
    .bind(&self.icon_id)
    .execute(pool)
    .await?;
    Ok(())
  }

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