use crate::error::OrmError;
use crate::pagination::{Page, Pagination};
use crate::{DeleteQuery, DropQuery, InsertQuery, JoinType, SelectQuery, UpdateQuery};
use sqlx::{Any, Pool};

#[derive(Clone, Debug)]
pub struct ForeignKey {
    pub column_name: String,
    pub reference_column_name: String,
}

#[async_trait::async_trait]
pub trait Entity: where Self: Sized {
    async fn get_pool() -> Result<Pool<Any>, OrmError>;
    fn get_table_name() -> String;
    fn get_column_names() -> Vec<String>;
    fn get_sequence_name() -> Option<String>;
    fn get_primary_key_column() -> String;
    fn get_foreign_key_column<T: Entity>() -> Option<ForeignKey>;
    fn get_join<T: Entity>(join_type: JoinType) -> String;
    fn get_query_example(&self) -> SelectQuery;
    fn get_plain_query() -> SelectQuery;
    fn get_insert_query(&self) -> InsertQuery;
    fn get_update_query(&self) -> UpdateQuery;
    fn get_plain_insert_query() -> InsertQuery;
    fn get_delete_query() -> DeleteQuery;
    fn get_drop_query() -> DropQuery;
    async fn query_example(&self, pagination: Option<Pagination>, pool: &Pool<Any>) -> Result<Page<Self>, OrmError>;
    async fn insert(&self, pool: &Pool<Any>) -> Result<Self, OrmError>;
    async fn bulk_insert(entities: Vec<Self>, pool: &Pool<Any>) -> Result<Vec<Self>, OrmError>;
    async fn update(&self, pool: &Pool<Any>) -> Result<Self, OrmError>;
    async fn bulk_update(entities: Vec<Self>, pool: &Pool<Any>) -> Result<Vec<Self>, OrmError>;
    async fn update_by_example(example: Self, update: Self, pool: &Pool<Any>) -> Result<Vec<Self>, OrmError>;
    async fn delete(&self, pool: &Pool<Any>) -> Result<Self, OrmError>;
    async fn bulk_delete(entities: Vec<Self>, pool: &Pool<Any>) -> Result<Vec<Self>, OrmError>;
    async fn drop(pool: &Pool<Any>) -> Result<(), OrmError>;
    
}

#[rglw::codegen::test(name="Test Entity Macro")]
mod tests {
    use crate::{error::OrmError, get_connection, traits::entity::{Entity, ForeignKey}, JoinType};
    use crate::{get_pool, get_test_db_configuration, pagination::Pagination, query::{Query, SelectQuery, UpdateQuery, DropQuery, DeleteQuery, InsertQuery, WhereStatement, WhereType, WhereType::*}};
    use rglw_orm_codegen::Entity;
    use sqlx::{Any, Pool};
    use std::{fs};
    use std::path::Path;
    use crate::pagination::Page;
    use crate::pagination::calculate_pagination;
    use crate::types::IdentityType;

    #[derive(Default, Debug, Clone, sqlx::FromRow, Entity)]
    struct EntityA {
        #[primary_key]
        id: i64,
        some_field: String,
    }
    
    #[derive(Default, Debug, Clone, sqlx::FromRow, Entity)]
    struct EntityB {
        #[primary_key(sequence="some_sequence")]
        id: i64,
        some_field: String,
        #[foreign_key(references=EntityA, on="id")]
        some_other_entity_id: i64,
    }

    #[derive(Default, Debug, Clone, sqlx::FromRow, Entity)]
    #[table_name="some_other_name"]
    struct SomeEntityWithLargeName {
        #[primary_key]
        some_field: String,
    }

    #[derive(Default, Debug, Clone, sqlx::FromRow, Entity)]
    struct User {
        #[primary_key]
        pub id: i64,
        pub username: String,
        pub email: String,
    }

    #[rglw_codegen::before_all]
    pub async fn before_all() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let result = sqlx::query(
            "CREATE TABLE IF NOT EXISTS User(\
                    id INTEGER PRIMARY KEY AUTOINCREMENT,\
                    username TEXT NOT NULL,
                    email TEXT NOT NULL
                )").execute(&pool).await;
        assert!(result.is_ok());
        let result = sqlx::query(
            "\
                INSERT INTO User(username,email) VALUES (
                    'Jonathan',
                    'email@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 1',
                    'user1@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 2',
                    'user2@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 3',
                    'user3@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 4',
                    'user4@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'User 5',
                    'user5@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'Delete Example',
                    'delete1@rustyglw.org'
                );
                INSERT INTO User(username,email) VALUES (
                    'Delete Example',
                    'delete2@rustyglw.org'
                );
            ").execute(&pool).await;
        assert!(result.is_ok());
    }

    #[rglw_codegen::after_all]
    pub async fn after_all() {
        fs::remove_file(Path::new("./entity.sqlite")).unwrap();
    }

    pub fn test_table_name() {
        assert_eq!(SomeEntityWithLargeName::get_table_name(), "some_other_name");
    }
    
    pub fn test_get_column_names() {
        assert_eq!(SomeEntityWithLargeName::get_column_names(), vec!["some_field"])
    }
    
    pub fn test_get_primary_key_column() {
        assert_eq!(EntityB::get_primary_key_column(), "id");
    }

    pub fn test_get_foreign_key_column() {
        let foreign_key = EntityB::get_foreign_key_column::<EntityA>();
        assert!(foreign_key.is_some());
        let foreign_key = foreign_key.unwrap();
        assert_eq!(foreign_key.column_name, "some_other_entity_id");
        assert_eq!(foreign_key.reference_column_name, "id");
    }
    
    pub fn test_sequence_name() {
        assert_eq!(SomeEntityWithLargeName::get_sequence_name(), None);
        assert_eq!(EntityA::get_sequence_name(), None);
        assert_eq!(EntityB::get_sequence_name(), Some("some_sequence".to_string()));
        assert_eq!(EntityB::get_join::<EntityA>(JoinType::LeftJoin), "LEFT JOIN entity_a ON entity_b.some_other_entity_id = entity_a.id");
    }

    pub fn test_get_join() {
        let foreign_key = EntityB::get_join::<EntityA>(JoinType::LeftJoin);
        assert_eq!(foreign_key, "LEFT JOIN entity_a ON entity_b.some_other_entity_id = entity_a.id");
    }
    
    pub fn test_get_query_example() {
        let entity = EntityA {
            id: 1,
            some_field: "some_field".to_string(),
        };
        let query = entity.get_query_example();
        assert_eq!(query.build(), "SELECT id, some_field\nFROM entity_a\nWHERE id = $1 AND some_field = $2;");
    }
    
    pub fn test_get_plain_query() {
        assert_eq!(User::get_plain_query().build(), "SELECT id, username, email\nFROM user;");
    }
    
    pub fn test_get_insert_query() {
        let entity = EntityB {
            some_field: "some_field".to_string(),
            some_other_entity_id: 2,
            ..Default::default()
        };
        let query = entity.get_insert_query();
        assert_eq!(query.build(), "INSERT INTO entity_b (id, some_field, some_other_entity_id) VALUES (nextval('some_sequence'), $1, $2) RETURNING id;");
        let entity = EntityB {
            id: 1,
            some_field: "some_field".to_string(),
            some_other_entity_id: 2,
        };
        let query = entity.get_insert_query();
        assert_eq!(query.build(), "INSERT INTO entity_b (id, some_field, some_other_entity_id) VALUES ($1, $2, $3) RETURNING id;");
        let entity = EntityA {
            some_field: "some_field".to_string(),
            ..Default::default()
        };
        let query = entity.get_insert_query();
        assert_eq!(query.build(), "INSERT INTO entity_a (some_field) VALUES ($1) RETURNING id;");
        let entity = EntityA {
            id: 1,
            some_field: "some_field".to_string(),
        };
        let query = entity.get_insert_query();
        assert_eq!(query.build(), "INSERT INTO entity_a (id, some_field) VALUES ($1, $2) RETURNING id;");
    }

    pub fn test_get_update_query() {
        let entity = EntityB {
            some_field: "some_field".to_string(),
            some_other_entity_id: 2,
            ..Default::default()
        };
        let query = entity.get_update_query();
        assert_eq!(query.build(), "UPDATE entity_b\nSET some_field = $1, some_other_entity_id = $2;");
    }

    pub fn test_get_plain_insert_query() {
        let mut query = EntityB::get_plain_insert_query();
        let query_string = query.build();
        assert_eq!(query_string, "INSERT INTO entity_b (id, some_field, some_other_entity_id) VALUES ($1, $2, $3) RETURNING id;");
        query.remove_column("id".to_string());
        let query_string = query.build();
        assert_eq!(query_string, "INSERT INTO entity_b (id, some_field, some_other_entity_id) VALUES (nextval('some_sequence'), $1, $2) RETURNING id;");
        let query = EntityA::get_plain_insert_query();
        assert_eq!(query.build(), "INSERT INTO entity_a (id, some_field) VALUES ($1, $2) RETURNING id;");
    }

    pub fn test_get_delete_query() {
        let query = EntityB::get_delete_query();
        assert_eq!(query.build(), "DELETE FROM entity_b;");
    }

    pub fn test_get_drop_query() {
        let query = EntityB::get_drop_query();
        assert_eq!(query.build(), "DROP TABLE entity_b;");
    }

    pub async fn test_query_example() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user = User {
            username: "Jonathan".to_string(),
            ..Default::default()
        };
        let result = user.query_example(Some(Pagination {
            page_number: 1,
            page_size: 10,
        }), &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        assert_eq!(result.data.len(), 1);
        assert_eq!(result.data.first().unwrap().username, "Jonathan".to_string());
    }

    pub async fn test_insert() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user = User {
            username: "Jonathan".to_string(),
            email: "some_new_email@test.com".to_string(),
            ..Default::default()
        };
        let result = user.insert(&pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        assert_eq!(result.username, "Jonathan".to_string());
        assert_eq!(result.email, "some_new_email@test.com".to_string());
        assert!(result.id > 0);
    }

    pub async fn test_bulk_insert() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user1 = User {
            username: "Jonathan".to_string(),
            email: "some_new_email1@test.com".to_string(),
            ..Default::default()
        };
        let user2 = User {
            username: "Jonathan".to_string(),
            email: "some_new_email2@test.com".to_string(),
            ..Default::default()
        };
        let result = User::bulk_insert(vec![user1, user2], &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        assert_eq!(result.len(), 2);
        assert_eq!(result.first().unwrap().email, "some_new_email1@test.com".to_string());
        assert_eq!(result.last().unwrap().email, "some_new_email2@test.com".to_string());
    }

    pub async fn test_update() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user = User {
            username: "Jonathan".to_string(),
            ..Default::default()
        };
        let result = user.query_example(None, &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        let mut user = result.data.first().unwrap().clone();
        user.username = "Updated username".to_string();
        let updated_user = user.update(&pool).await.unwrap();
        assert_eq!(updated_user.username, "Updated username");
    }

    pub async fn test_bulk_update() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user = User {
            username: "Jonathan".to_string(),
            ..Default::default()
        };
        let result = user.query_example(None, &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        let users = result.data;
        let mut user1 = users.first().unwrap().clone();
        let mut user2 = users.last().unwrap().clone();
        user1.username = "Updated username 1".to_string();
        user2.username = "Updated username 2".to_string();
        let updated_user = User::bulk_update(vec![user1, user2], &pool).await;
        assert!(updated_user.is_ok());
        let updated_user = updated_user.unwrap();
        assert_eq!(updated_user.first().unwrap().username, "Updated username 1");
        assert_eq!(updated_user.last().unwrap().username, "Updated username 2");
    }

    pub async fn test_update_by_example() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user = User {
            username: "Jonathan".to_string(),
            ..Default::default()
        };
        let result = User::update_by_example(user, User {
            username: "Updated username by example".to_string(),
            ..Default::default()
        }, &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        for entity in result {
            assert_eq!(entity.username, "Updated username by example");
        }
    }

    pub async fn test_delete() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user = User {
            username: "Jonathan".to_string(),
            ..Default::default()
        };
        let result = user.query_example(Some(Pagination {
            page_number: 1,
            page_size: 10,
        }), &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        for entity in result.data {
            let deleted = entity.delete(&pool).await;
            assert!(deleted.is_ok());
        }
        let result = user.query_example(Some(Pagination {
            page_number: 1,
            page_size: 10,
        }), &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        assert_eq!(result.total_rows, 0);
    }

    pub async fn test_bulk_delete() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let user = User {
            username: "Delete Example".to_string(),
            ..Default::default()
        };
        let result = user.query_example(Some(Pagination {
            page_number: 1,
            page_size: 10,
        }), &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        let result = User::bulk_delete(result.data, &pool).await;
        assert!(result.is_ok());
        let result = user.query_example(Some(Pagination {
            page_number: 1,
            page_size: 10,
        }), &pool).await;
        assert!(result.is_ok());
        let result = result.unwrap();
        assert_eq!(result.total_rows, 0);
    }

    pub async fn test_drop() {
        let pool = get_pool(get_test_db_configuration("sqlite://entity.sqlite?mode=rwc".to_string())).await;
        assert!(pool.is_ok());
        let pool = pool.unwrap();
        let result = User::drop(&pool).await;
        assert!(result.is_ok());
        let user = User {
            username: "Delete Example".to_string(),
            ..Default::default()
        };
        let result = user.query_example(Some(Pagination {
            page_number: 1,
            page_size: 10,
        }), &pool).await;
        assert!(result.is_err());
        let error_message = result.err().unwrap().message;
        assert_eq!(error_message, "error returned from database: (code: 1) no such table: user");
    }
}