use crate::db_object::{DBObject, OrderTypes};
use crate::df_world::{DBDFWorld, Entity, Region, River, UndergroundRegion, WorldConstruction};
use crate::schema::{coordinates, paths, rectangles};
use crate::DbConnection;
use df_st_core::fillable::{Fillable, Filler};
use df_st_core::item_count::ItemCount;
use df_st_derive::{Fillable, Filler, HashAndPartialEqById};
use diesel::expression_methods::ExpressionMethods;
use diesel::prelude::*;
use diesel::query_dsl::RunQueryDsl;
use diesel::{Identifiable, Queryable};
use failure::Error;
use std::collections::HashMap;
use std::fmt;

#[derive(
    Clone,
    Identifiable,
    HashAndPartialEqById,
    Queryable,
    Insertable,
    Fillable,
    Filler,
    Default,
    Associations,
)]
#[table_name = "coordinates"]
#[belongs_to(Region)]
#[belongs_to(UndergroundRegion)]
#[belongs_to(WorldConstruction)]
#[belongs_to(Entity)]
pub struct Coordinate {
    pub id: i32,
    pub world_id: i32,
    pub x: i32,
    pub y: i32,
    pub region_id: Option<i32>,
    pub underground_region_id: Option<i32>,
    pub world_construction_id: Option<i32>,
    pub entity_id: Option<i32>,
}

#[derive(
    Clone,
    Identifiable,
    HashAndPartialEqById,
    Queryable,
    Insertable,
    Fillable,
    Filler,
    Default,
    Debug,
)]
#[table_name = "rectangles"]
pub struct Rectangle {
    pub id: i32,
    pub world_id: i32,
    pub x1: i32,
    pub y1: i32,
    pub x2: i32,
    pub y2: i32,
}

#[derive(
    Clone,
    Identifiable,
    HashAndPartialEqById,
    Associations,
    Queryable,
    Insertable,
    Fillable,
    Filler,
    Default,
    Debug,
)]
#[table_name = "paths"]
#[belongs_to(River)]
pub struct Path {
    pub id: i32,
    pub world_id: i32,
    pub x: i32,
    pub y: i32,
    pub flow: i32,
    pub exit_tile: i32,
    pub elevation: i32,
    pub river_id: Option<i32>,
}

impl Coordinate {
    pub fn new() -> Self {
        Self::default()
    }
}

impl Rectangle {
    pub fn new() -> Self {
        Self::default()
    }
}

impl Path {
    pub fn new() -> Self {
        Self::default()
    }
}

impl DBObject<df_st_core::Coordinate, Coordinate> for Coordinate {
    fn add_missing_data_advanced(_core_world: &df_st_core::DFWorld, _world: &mut DBDFWorld) {
        // Handled by all the individual objects
    }

    #[cfg(feature = "postgres")]
    fn insert_into_db(conn: &DbConnection, coordinates: &[Coordinate]) {
        use diesel::pg::upsert::excluded;
        diesel::insert_into(coordinates::table)
            .values(coordinates)
            .on_conflict((coordinates::id, coordinates::world_id))
            .do_update()
            .set((
                coordinates::x.eq(excluded(coordinates::x)),
                coordinates::y.eq(excluded(coordinates::y)),
                coordinates::region_id.eq(excluded(coordinates::region_id)),
                coordinates::underground_region_id.eq(excluded(coordinates::underground_region_id)),
                coordinates::world_construction_id.eq(excluded(coordinates::world_construction_id)),
                coordinates::entity_id.eq(excluded(coordinates::entity_id)),
            ))
            .execute(conn)
            .expect("Error saving coordinates");
    }

    #[cfg(not(feature = "postgres"))]
    fn insert_into_db(conn: &DbConnection, coordinates: &[Coordinate]) {
        diesel::insert_into(coordinates::table)
            .values(coordinates)
            .execute(conn)
            .expect("Error saving coordinates");
    }

    /// Get a filtered list of coordinates from the database
    fn find_db_list(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
        _string_filter: HashMap<String, String>,
        offset: i64,
        limit: i64,
        order: Option<OrderTypes>,
        order_by: Option<String>,
        id_list: Option<Vec<i32>>,
    ) -> Result<Vec<Coordinate>, Error> {
        use crate::schema::coordinates::dsl::*;
        let (order_by, asc) = Self::get_order(order, order_by);
        let query = coordinates.limit(limit).offset(offset);
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        optional_filter! {
            query, id_filter,
            id_list => id,
            [
                "id" => id,
                "region_id" => region_id,
                "underground_region_id" => underground_region_id,
                "world_construction_id" => world_construction_id,
                "entity_id" => entity_id,
            ],
            {Ok(order_by!{
                order_by, asc, query, conn,
                "id" => id,
                "x" => x,
                "y" => y,
                "region_id" => region_id,
                "underground_region_id" => underground_region_id,
                "world_construction_id" => world_construction_id,
                "entity_id" => entity_id,
            })},
        }
    }

    fn find_db_item(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
    ) -> Result<Option<Coordinate>, Error> {
        use crate::schema::coordinates::dsl::*;
        let query = coordinates;
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        let query = query.filter(id.eq(id_filter.get("id").unwrap_or(&0)));
        Ok(query.first::<Coordinate>(conn).optional()?)
    }

    fn match_field_by(field: String) -> String {
        match field.as_ref() {
            "x" => "x",
            "y" => "y",
            "region_id" => "region_id",
            "underground_region_id" => "underground_region_id",
            "world_construction_id" => "world_construction_id",
            "entity_id" => "entity_id",
            _ => "id",
        }
        .to_owned()
    }

    fn add_nested_items(
        _conn: &DbConnection,
        _db_list: &[Coordinate],
        core_list: Vec<df_st_core::Coordinate>,
    ) -> Result<Vec<df_st_core::Coordinate>, Error> {
        Ok(core_list)
    }

    fn get_count_from_db(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
        _string_filter: HashMap<String, String>,
        offset: u32,
        limit: u32,
        group_by_opt: Option<String>,
        id_list: Option<Vec<i32>>,
    ) -> Result<Vec<ItemCount>, Error> {
        use crate::schema::coordinates::dsl::*;
        let query = coordinates.limit(limit as i64).offset(offset as i64);
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        optional_filter! {
            query, id_filter,
            id_list => id,
            [
                "id" => id,
                "region_id" => region_id,
                "underground_region_id" => underground_region_id,
                "world_construction_id" => world_construction_id,
                "entity_id" => entity_id,
            ],
            {group_by!{
                group_by_opt, query, conn,
                "id" => {id: i32},
                "x" => {x: i32},
                "y" => {y: i32},
                "region_id" => {region_id: Option<i32>},
                "underground_region_id" => {underground_region_id: Option<i32>},
                "world_construction_id" => {world_construction_id: Option<i32>},
                "entity_id" => {entity_id: Option<i32>},
            };},
        };
    }
}

impl DBObject<df_st_core::Rectangle, Rectangle> for Rectangle {
    fn add_missing_data_advanced(_core_world: &df_st_core::DFWorld, _world: &mut DBDFWorld) {
        // Handled by all the individual objects
    }

    #[cfg(feature = "postgres")]
    fn insert_into_db(conn: &DbConnection, rectangles: &[Rectangle]) {
        use diesel::pg::upsert::excluded;
        diesel::insert_into(rectangles::table)
            .values(rectangles)
            .on_conflict((rectangles::id, rectangles::world_id))
            .do_update()
            .set((
                rectangles::x1.eq(excluded(rectangles::x1)),
                rectangles::y1.eq(excluded(rectangles::y1)),
                rectangles::x2.eq(excluded(rectangles::x2)),
                rectangles::y2.eq(excluded(rectangles::y2)),
            ))
            .execute(conn)
            .expect("Error saving rectangles");
    }

    #[cfg(not(feature = "postgres"))]
    fn insert_into_db(conn: &DbConnection, rectangles: &[Rectangle]) {
        diesel::insert_into(rectangles::table)
            .values(rectangles)
            .execute(conn)
            .expect("Error saving rectangles");
    }

    /// Get a filtered list of rectangles from the database
    fn find_db_list(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
        _string_filter: HashMap<String, String>,
        offset: i64,
        limit: i64,
        order: Option<OrderTypes>,
        order_by: Option<String>,
        id_list: Option<Vec<i32>>,
    ) -> Result<Vec<Rectangle>, Error> {
        use crate::schema::rectangles::dsl::*;
        let (order_by, asc) = Self::get_order(order, order_by);
        let query = rectangles.limit(limit).offset(offset);
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        optional_filter! {
            query, id_filter,
            id_list => id,
            [
                "id" => id,
            ],
            {Ok(order_by!{
                order_by, asc, query, conn,
                "id" => id,
                "x1" => x1,
                "y1" => y1,
                "x2" => x2,
                "y2" => y2,
            })},
        }
    }

    fn find_db_item(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
    ) -> Result<Option<Rectangle>, Error> {
        use crate::schema::rectangles::dsl::*;
        let query = rectangles;
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        let query = query.filter(id.eq(id_filter.get("id").unwrap_or(&0)));
        Ok(query.first::<Rectangle>(conn).optional()?)
    }

    fn match_field_by(field: String) -> String {
        match field.as_ref() {
            "x1" => "x1",
            "y1" => "y1",
            "x2" => "x2",
            "y2" => "y2",
            _ => "id",
        }
        .to_owned()
    }

    fn add_nested_items(
        _conn: &DbConnection,
        _db_list: &[Rectangle],
        core_list: Vec<df_st_core::Rectangle>,
    ) -> Result<Vec<df_st_core::Rectangle>, Error> {
        Ok(core_list)
    }

    fn get_count_from_db(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
        _string_filter: HashMap<String, String>,
        offset: u32,
        limit: u32,
        group_by_opt: Option<String>,
        id_list: Option<Vec<i32>>,
    ) -> Result<Vec<ItemCount>, Error> {
        use crate::schema::rectangles::dsl::*;
        let query = rectangles.limit(limit as i64).offset(offset as i64);
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        optional_filter! {
            query, id_filter,
            id_list => id,
            [
                "id" => id,
            ],
            {group_by!{
                group_by_opt, query, conn,
                "id" => {id: i32},
                "x1" => {x1: i32},
                "y1" => {y1: i32},
                "x2" => {x2: i32},
                "y2" => {y2: i32},
            };},
        };
    }
}

impl DBObject<df_st_core::Path, Path> for Path {
    fn add_missing_data_advanced(_core_world: &df_st_core::DFWorld, _world: &mut DBDFWorld) {
        // Handled by all the individual objects
    }

    #[cfg(feature = "postgres")]
    fn insert_into_db(conn: &DbConnection, paths: &[Path]) {
        use diesel::pg::upsert::excluded;
        diesel::insert_into(paths::table)
            .values(paths)
            .on_conflict((paths::id, paths::world_id))
            .do_update()
            .set((
                paths::x.eq(excluded(paths::x)),
                paths::y.eq(excluded(paths::y)),
                paths::flow.eq(excluded(paths::flow)),
                paths::exit_tile.eq(excluded(paths::exit_tile)),
                paths::elevation.eq(excluded(paths::elevation)),
                paths::river_id.eq(excluded(paths::river_id)),
            ))
            .execute(conn)
            .expect("Error saving paths");
    }

    #[cfg(not(feature = "postgres"))]
    fn insert_into_db(conn: &DbConnection, paths: &[Path]) {
        diesel::insert_into(paths::table)
            .values(paths)
            .execute(conn)
            .expect("Error saving paths");
    }

    /// Get a filtered list of paths from the database
    fn find_db_list(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
        _string_filter: HashMap<String, String>,
        offset: i64,
        limit: i64,
        order: Option<OrderTypes>,
        order_by: Option<String>,
        id_list: Option<Vec<i32>>,
    ) -> Result<Vec<Path>, Error> {
        use crate::schema::paths::dsl::*;
        let (order_by, asc) = Self::get_order(order, order_by);
        let query = paths.limit(limit).offset(offset);
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        optional_filter! {
            query, id_filter,
            id_list => id,
            [
                "id" => id,
                "river_id" => river_id,
            ],
            {Ok(order_by!{
                order_by, asc, query, conn,
                "id" => id,
                "x" => x,
                "y" => y,
                "flow" => flow,
                "exit_tile" => exit_tile,
                "elevation" => elevation,
                "river_id" => river_id,
            })},
        }
    }

    fn find_db_item(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
    ) -> Result<Option<Path>, Error> {
        use crate::schema::paths::dsl::*;
        let query = paths;
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        let query = query.filter(id.eq(id_filter.get("id").unwrap_or(&0)));
        Ok(query.first::<Path>(conn).optional()?)
    }

    fn match_field_by(field: String) -> String {
        match field.as_ref() {
            "x" => "x",
            "y" => "y",
            "flow" => "flow",
            "exit_tile" => "exit_tile",
            "elevation" => "elevation",
            "river_id" => "river_id",
            _ => "id",
        }
        .to_owned()
    }

    fn add_nested_items(
        _conn: &DbConnection,
        _db_list: &[Path],
        core_list: Vec<df_st_core::Path>,
    ) -> Result<Vec<df_st_core::Path>, Error> {
        Ok(core_list)
    }

    fn get_count_from_db(
        conn: &DbConnection,
        id_filter: HashMap<String, i32>,
        _string_filter: HashMap<String, String>,
        offset: u32,
        limit: u32,
        group_by_opt: Option<String>,
        id_list: Option<Vec<i32>>,
    ) -> Result<Vec<ItemCount>, Error> {
        use crate::schema::paths::dsl::*;
        let query = paths.limit(limit as i64).offset(offset as i64);
        let query = query.filter(world_id.eq(id_filter.get("world_id").unwrap_or(&0)));
        optional_filter! {
            query, id_filter,
            id_list => id,
            [
                "id" => id,
                "river_id" => river_id,
            ],
            {group_by!{
                group_by_opt, query, conn,
                "id" => {id: i32},
                "x" => {x: i32},
                "y" => {y: i32},
                "flow" => {flow: i32},
                "exit_tile" => {exit_tile: i32},
                "elevation" => {elevation: i32},
                "river_id" => {river_id: Option<i32>},
            };},
        };
    }
}

impl PartialEq<df_st_core::Coordinate> for Coordinate {
    fn eq(&self, other: &df_st_core::Coordinate) -> bool {
        self.id == other.id && self.x == other.x && self.y == other.y
    }
}

impl PartialEq<df_st_core::Rectangle> for Rectangle {
    fn eq(&self, other: &df_st_core::Rectangle) -> bool {
        self.id == other.id
            && self.x1 == other.x1
            && self.y1 == other.y1
            && self.x2 == other.x2
            && self.y2 == other.y2
    }
}

impl PartialEq<df_st_core::Path> for Path {
    fn eq(&self, other: &df_st_core::Path) -> bool {
        self.id == other.id && self.x == other.x && self.y == other.y
    }
}

/// Force a new format for `Coordinate` to not clutter the screen
impl fmt::Debug for Coordinate {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        f.write_str(&format! {"(id: {}, ({}, {}))", self.id, self.x, self.y})
    }
}

impl fmt::Display for Coordinate {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        write!(f, "({} = {}, {})", self.id, self.x, self.y)
    }
}

impl fmt::Display for Rectangle {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        write!(f, "({}, {}, {}, {})", self.x1, self.y1, self.x2, self.y2)
    }
}

impl fmt::Display for Path {
    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
        write!(
            f,
            "({} = {}, {}, f={}, e={})",
            self.id, self.x, self.y, self.flow, self.exit_tile
        )
    }
}

impl Filler<df_st_core::Coordinate, Coordinate> for df_st_core::Coordinate {
    fn add_missing_data(&mut self, source: &Coordinate) {
        self.id.add_missing_data(&source.id);
        self.x.add_missing_data(&source.x);
        self.y.add_missing_data(&source.y);
    }
}

impl Filler<df_st_core::Rectangle, Rectangle> for df_st_core::Rectangle {
    fn add_missing_data(&mut self, source: &Rectangle) {
        self.id.add_missing_data(&source.id);
        self.x1.add_missing_data(&source.x1);
        self.y1.add_missing_data(&source.y1);
        self.x2.add_missing_data(&source.x2);
        self.y2.add_missing_data(&source.y2);
    }
}

impl Filler<df_st_core::Path, Path> for df_st_core::Path {
    fn add_missing_data(&mut self, source: &Path) {
        self.id.add_missing_data(&source.id);
        self.x.add_missing_data(&source.x);
        self.y.add_missing_data(&source.y);
        self.flow.add_missing_data(&source.flow);
        self.exit_tile.add_missing_data(&source.exit_tile);
        self.elevation.add_missing_data(&source.elevation);
    }
}