use sqlx::{Postgres, QueryBuilder};

use crate::{DbResult, Error};

const TABLE_NAME: &str = "npc_stations";

#[derive(Debug, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
pub struct StationDb {
  #[serde(rename = "stationId")]
  pub station_id: i64,
  pub security: f32,
  #[serde(rename = "operationType")]
  pub operation_type: String,
  #[serde(rename = "services")]
  pub services: Vec<String>,
  #[serde(rename = "corporationId")]
  pub corporation_id: i32,
  #[serde(rename = "solarSystemId")]
  pub system_id: i32,
  #[serde(rename = "constellationId")]
  pub constellation_id: i32,
  #[serde(rename = "regionId")]
  pub region_id: i32,
  #[serde(rename = "stationName")]
  pub station_name: String,
  pub x: f32,
  pub y: f32,
  pub z: f32,
  #[serde(rename = "reprocessingEfficiency")]
  pub reprocessing_efficiency: f32,
  #[serde(rename = "reprocessingStationsTake")]
  pub reprocessing_stations_take: f32,
  #[serde(rename = "reprocessingHangarFlag")]
  pub reprocessing_hangar_flag: i32,
}

#[derive(Debug, serde::Serialize, serde::Deserialize)]
pub struct StationFilter {
  #[serde(rename = "stationId")]
  pub station_id: Option<Vec<i64>>,
  #[serde(rename = "stationName")]
  pub station_name: Option<Vec<String>>,
  #[serde(rename = "systemId")]
  pub system_id: Option<Vec<i32>>,
  pub page: Option<i64>,
  pub limit: Option<i64>,
}

impl StationDb {
  pub async fn get_by_id(station_id: i64) -> DbResult<Option<Self>> {
    let pool = crate::pool();
    let station = sqlx::query_as::<_, Self>(&format!(
      r#"
      SELECT * FROM {} WHERE station_id = $1
      "#,
      TABLE_NAME
    ))
    .bind(station_id)
    .fetch_optional(pool)
    .await?;

    Ok(station)
  }

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

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

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

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

    query
  }

  pub async fn insert(&self) -> DbResult<()> {
    let pool = crate::pool();
    sqlx::query(&format!(
      r#"
      INSERT INTO {} (
        station_id,
        security,
        operation_type,
        services,
        corporation_id,
        system_id,
        constellation_id,
        region_id,
        station_name,
        x: f32,
        y: f32,
        z: f32,
        reprocessing_efficiency,
        reprocessing_stations_take,
        reprocessing_hangar_flag,
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
      "#,
      TABLE_NAME
    ))
    .bind(self.station_id)
    .bind(self.security)
    .bind(&self.operation_type)
    .bind(&self.services)
    .bind(self.corporation_id)
    .bind(self.system_id)
    .bind(self.constellation_id)
    .bind(self.region_id)
    .bind(&self.station_name)
    .bind(self.x)
    .bind(self.y)
    .bind(self.z)
    .bind(self.reprocessing_efficiency)
    .bind(self.reprocessing_stations_take)
    .bind(self.reprocessing_hangar_flag)
    .execute(pool)
    .await?;

    Ok(())
  }

  pub async fn insert_multiple(stations: &Vec<Self>) -> DbResult<()> {
    let pool = crate::pool();
    let step = 1000;
    for i in (0..stations.len()).step_by(step) {
      let mut query = sqlx::QueryBuilder::new(format!(
        r#"
        INSERT INTO {} (
          station_id,
          security,
          operation_type,
          services,
          corporation_id,
          system_id,
          constellation_id,
          region_id,
          station_name,
          x,
          y,
          z,
          reprocessing_efficiency,
          reprocessing_stations_take,
          reprocessing_hangar_flag
        )
        VALUES
        "#,
        TABLE_NAME
      ));

      for j in 0..step {
        if i + j >= stations.len() {
          break;
        }
        if j > 0 {
          query.push(", ");
        }
        let station = &stations[i + j];
        query
          .push(" (")
          .push_bind(station.station_id)
          .push(", ")
          .push_bind(station.security)
          .push(", ")
          .push_bind(&station.operation_type)
          .push(", ")
          .push_bind(&station.services)
          .push(", ")
          .push_bind(station.corporation_id)
          .push(", ")
          .push_bind(station.system_id)
          .push(", ")
          .push_bind(station.constellation_id)
          .push(", ")
          .push_bind(station.region_id)
          .push(", ")
          .push_bind(&station.station_name)
          .push(", ")
          .push_bind(station.x)
          .push(", ")
          .push_bind(station.y)
          .push(", ")
          .push_bind(station.z)
          .push(", ")
          .push_bind(station.reprocessing_efficiency)
          .push(", ")
          .push_bind(station.reprocessing_stations_take)
          .push(", ")
          .push_bind(station.reprocessing_hangar_flag)
          .push(") ");
      }
      query
        .push(" ON CONFLICT (station_id) DO UPDATE SET ")
        .push("security = EXCLUDED.security, ")
        .push("operation_type = EXCLUDED.operation_type, ")
        .push("services = EXCLUDED.services, ")
        .push("corporation_id = EXCLUDED.corporation_id, ")
        .push("system_id = EXCLUDED.system_id, ")
        .push("constellation_id = EXCLUDED.constellation_id, ")
        .push("region_id = EXCLUDED.region_id, ")
        .push("station_name = EXCLUDED.station_name, ")
        .push("x = EXCLUDED.x, ")
        .push("y = EXCLUDED.y, ")
        .push("z = EXCLUDED.z, ")
        .push("reprocessing_efficiency = EXCLUDED.reprocessing_efficiency, ")
        .push("reprocessing_stations_take = EXCLUDED.reprocessing_stations_take, ")
        .push("reprocessing_hangar_flag = EXCLUDED.reprocessing_hangar_flag");
      query.build().execute(pool).await?;
    }

    Ok(())
  }
}