use sea_orm_migration::schema::{integer_null, string};
use sea_orm_migration::sea_query::{ColumnDef, IntoIden};

/// Sets the column to be an alias for SQLite's rowid.
///
/// Required conditions:
/// - This column must *not* be auto_increment
/// - There cannot be other primary_key columns in the table
///
/// When using `DeriveEntityModel`, the type must be `i64` (or equivalent)
/// and should be tagged with:
///
/// `#[sea_orm(primary_key, auto_increment = false)]`
///
/// When using the new entity format:
///
///     use sea_orm::entity::prelude::*;
///     use seamantic::model::id::Id;
///
///     #[sea_orm::model]
///     #[derive(Debug, Clone, DeriveEntityModel)]
///     #[sea_orm(table_name = "rowid_test")]
///     pub struct Model {
///         #[sea_orm(primary_key, auto_increment = false)]
///         id: Id<Model>,
///     }
///     impl ActiveModelBehavior for ActiveModel {}
pub fn sqlite_rowid_alias<T: IntoIden>(name: T) -> ColumnDef {
	integer_null(name).primary_key().take()
}

/// Set the column to be a case insensitive string
///
/// When using the new entity format:
///
///     use sea_orm::entity::prelude::*;
///     use seamantic::model::id::Id;
///
///     #[sea_orm::model]
///     #[derive(Debug, Clone, DeriveEntityModel)]
///     #[sea_orm(table_name = "nocase_test")]
///     pub struct Model {
///         #[sea_orm(primary_key, auto_increment = false)]
///         id: i64,
///         #[sea_orm(extra = "COLLATE NOCASE")]
///         nocase: String,
///     }
///     impl ActiveModelBehavior for ActiveModel {}
pub fn sqlite_case_insensitive_string<T: IntoIden>(name: T) -> ColumnDef {
	string(name).extra("COLLATE NOCASE").take()
}

#[cfg(test)]
mod entity_tests {
	use sea_orm::{ConnectOptions, Database, DatabaseConnection};

	pub async fn new_initialized_memory_db() -> DatabaseConnection {
		let options = ConnectOptions::new("sqlite::memory:");

		let db = Database::connect(options)
			.await
			.expect("Database::connect()");
		db.get_schema_registry("seamantic::schema::sqlite::*")
			.sync(&db)
			.await
			.expect("db.get_schema_registry().sync()");
		db
	}

	mod rowid_test {
		use sea_orm::ActiveValue::{NotSet, Set};
		use sea_orm::entity::prelude::*;

		use crate::model::id::{Id, SeaOrmRepr};

		#[sea_orm::model]
		#[derive(Debug, Clone, DeriveEntityModel)]
		#[sea_orm(table_name = "rowid_test")]
		pub struct Model {
			#[sea_orm(primary_key, auto_increment = false)]
			id: Id<Model>,
		}
		impl ActiveModelBehavior for ActiveModel {}

		#[tokio::test]
		async fn test_sqlite_rowid_alias() {
			let db = super::new_initialized_memory_db().await;

			// Starts at 1 and increments
			for i in 1..=3 {
				let model = ActiveModel { id: NotSet };
				let model = model.insert(&db).await.expect("insert");
				assert_eq!(model.id.into_raw(), i);
			}

			// Delete the top number and re-add
			for i in 3..=3 {
				let model = ActiveModel {
					id: Set(Id::from_raw(i)),
				};
				model.delete(&db).await.expect("delete");
				let model = ActiveModel { id: NotSet };
				let model = model.insert(&db).await.expect("insert");
				assert_eq!(model.id.into_raw(), i);
			}

			// Jump to 100 and increment
			for i in 100..=103 {
				let model = ActiveModel {
					id: Set(Id::from_raw(i)),
				};
				let model = model.insert(&db).await.expect("insert");
				assert_eq!(model.id.into_raw(), i);
			}

			// Continue to increment
			for i in 104..=104 {
				let model = ActiveModel { id: NotSet };
				let model = model.insert(&db).await.expect("insert");
				assert_eq!(model.id.into_raw(), i);
			}

			// Jump to SeaOrmRepr::MAX and increment
			for i in SeaOrmRepr::MAX..=SeaOrmRepr::MAX {
				let model = ActiveModel {
					id: Set(Id::from_raw(i)),
				};
				let model = model.insert(&db).await.expect("insert");
				assert_eq!(model.id.into_raw(), i);
			}

			// Next ones are random around the center
			for _ in 0..3 {
				let model = ActiveModel { id: NotSet };
				let model = model.insert(&db).await.expect("insert");
				assert!(model.id.into_raw() > 0);
			}

			// Zero ID is valid
			for i in 0..=0 {
				let model = ActiveModel {
					id: Set(Id::from_raw(i)),
				};
				let model = model.insert(&db).await.expect("insert");
				assert_eq!(model.id.into_raw(), i);
			}

			// Negative ID is valid
			for i in SeaOrmRepr::MIN..=(SeaOrmRepr::MIN + 3) {
				let model = ActiveModel {
					id: Set(Id::from_raw(i)),
				};
				let model = model.insert(&db).await.expect("insert");
				assert_eq!(model.id.into_raw(), i);
			}
		}
	}

	mod nocase_test {
		use sea_orm::ActiveValue::Set;
		use sea_orm::entity::prelude::*;

		#[sea_orm::model]
		#[derive(Debug, Clone, DeriveEntityModel)]
		#[sea_orm(table_name = "nocase_test")]
		pub struct Model {
			#[sea_orm(primary_key, auto_increment = false)]
			id: i64,
			case: String,
			#[sea_orm(extra = "COLLATE NOCASE")]
			nocase: String,
		}
		impl ActiveModelBehavior for ActiveModel {}

		#[tokio::test]
		async fn test_sqlite_case_insensitive_string() {
			let db = super::new_initialized_memory_db().await;

			let i = 50;

			// Insert a lowercase string
			{
				let model = ActiveModel {
					id: Set(i),
					case: Set("abcd".to_owned()),
					nocase: Set("abcd".to_owned()),
				};
				model.insert(&db).await.expect("insert");
			}

			// Query by uppercase string
			{
				Entity::find()
					.filter(Column::Case.eq("ABCD"))
					.one(&db)
					.await
					.expect("find by case insensitive string")
					.ok_or(())
					.expect_err("find by case insensitive string");

				let model = Entity::find()
					.filter(Column::Nocase.eq("ABCD"))
					.one(&db)
					.await
					.expect("find by case insensitive string")
					.expect("find by case insensitive string");
				assert_eq!(model.id, i);
				// The string should be read back as-is
				assert_eq!(model.nocase, "abcd");
			}
		}
	}
}

#[cfg(test)]
mod tests {
	use sea_orm::ActiveValue::{NotSet, Set};
	use sea_orm::entity::prelude::*;
	use sea_orm::{ConnectOptions, Database, DatabaseConnection};
	use sea_orm_migration::async_trait::async_trait;
	use sea_orm_migration::prelude::*;

	use crate::model::id::{Id, SeaOrmRepr};

	use super::{sqlite_case_insensitive_string, sqlite_rowid_alias};

	async fn new_memory_db() -> DatabaseConnection {
		let options = ConnectOptions::new("sqlite::memory:");
		Database::connect(options).await.expect("Database::connect")
	}

	#[tokio::test]
	async fn test_sqlite_rowid_alias() {
		struct Migrator;
		impl MigratorTrait for Migrator {
			fn migrations() -> Vec<Box<dyn MigrationTrait>> {
				vec![Box::new(Migration)]
			}
		}

		#[derive(Iden)]
		pub(super) enum TestTable {
			Table,
			Id,
		}
		#[derive(DeriveMigrationName)]
		struct Migration;
		#[async_trait]
		impl MigrationTrait for Migration {
			async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
				manager
					.create_table(
						Table::create()
							.table(TestTable::Table)
							.col(sqlite_rowid_alias(TestTable::Id))
							.to_owned(),
					)
					.await?;

				Ok(())
			}
		}

		#[derive(Debug, Clone, DeriveEntityModel)]
		#[sea_orm(table_name = "test_table")]
		pub struct Model {
			#[sea_orm(primary_key, auto_increment = false)]
			id: Id<Model>,
		}
		#[derive(Debug, EnumIter)]
		pub enum Relation {}
		impl RelationTrait for Relation {
			fn def(&self) -> RelationDef {
				panic!("No RelationDef")
			}
		}
		impl ActiveModelBehavior for ActiveModel {}

		let db = new_memory_db().await;
		Migrator::up(&db, None).await.expect("up");

		// Starts at 1 and increments
		for i in 1..=3 {
			let model = ActiveModel { id: NotSet };
			let model = model.insert(&db).await.expect("insert");
			assert_eq!(model.id.into_raw(), i);
		}

		// Delete the top number and re-add
		for i in 3..=3 {
			let model = ActiveModel {
				id: Set(Id::from_raw(i)),
			};
			model.delete(&db).await.expect("delete");
			let model = ActiveModel { id: NotSet };
			let model = model.insert(&db).await.expect("insert");
			assert_eq!(model.id.into_raw(), i);
		}

		// Jump to 100 and increment
		for i in 100..=103 {
			let model = ActiveModel {
				id: Set(Id::from_raw(i)),
			};
			let model = model.insert(&db).await.expect("insert");
			assert_eq!(model.id.into_raw(), i);
		}

		// Continue to increment
		for i in 104..=104 {
			let model = ActiveModel { id: NotSet };
			let model = model.insert(&db).await.expect("insert");
			assert_eq!(model.id.into_raw(), i);
		}

		// Jump to SeaOrmRepr::MAX and increment
		for i in SeaOrmRepr::MAX..=SeaOrmRepr::MAX {
			let model = ActiveModel {
				id: Set(Id::from_raw(i)),
			};
			let model = model.insert(&db).await.expect("insert");
			assert_eq!(model.id.into_raw(), i);
		}

		// Next ones are random around the center
		for _ in 0..3 {
			let model = ActiveModel { id: NotSet };
			let model = model.insert(&db).await.expect("insert");
			assert!(model.id.into_raw() > 0);
		}

		// Zero ID is valid
		for i in 0..=0 {
			let model = ActiveModel {
				id: Set(Id::from_raw(i)),
			};
			let model = model.insert(&db).await.expect("insert");
			assert_eq!(model.id.into_raw(), i);
		}

		// Negative ID is valid
		for i in SeaOrmRepr::MIN..=(SeaOrmRepr::MIN + 3) {
			let model = ActiveModel {
				id: Set(Id::from_raw(i)),
			};
			let model = model.insert(&db).await.expect("insert");
			assert_eq!(model.id.into_raw(), i);
		}
	}

	#[tokio::test]
	async fn test_sqlite_case_insensitive_string() {
		struct Migrator;
		impl MigratorTrait for Migrator {
			fn migrations() -> Vec<Box<dyn MigrationTrait>> {
				vec![Box::new(Migration)]
			}
		}

		#[derive(Iden)]
		pub(super) enum TestTable {
			Table,
			Id,
			CiStr,
		}
		#[derive(DeriveMigrationName)]
		struct Migration;
		#[async_trait]
		impl MigrationTrait for Migration {
			async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
				manager
					.create_table(
						Table::create()
							.table(TestTable::Table)
							.col(sqlite_rowid_alias(TestTable::Id))
							.col(sqlite_case_insensitive_string(TestTable::CiStr))
							.to_owned(),
					)
					.await?;

				Ok(())
			}
		}

		#[derive(Debug, Clone, DeriveEntityModel)]
		#[sea_orm(table_name = "test_table")]
		pub struct Model {
			#[sea_orm(primary_key, auto_increment = false)]
			id: i64,
			ci_str: String,
		}
		#[derive(Debug, EnumIter)]
		pub enum Relation {}
		impl RelationTrait for Relation {
			fn def(&self) -> RelationDef {
				panic!("No RelationDef")
			}
		}
		impl ActiveModelBehavior for ActiveModel {}

		let db = new_memory_db().await;
		Migrator::up(&db, None).await.expect("up");

		let i = 50;

		// Insert a lowercase string
		{
			let model = ActiveModel {
				id: Set(i),
				ci_str: Set("abcd".to_owned()),
			};
			model.insert(&db).await.expect("insert");
		}

		// Query by uppercase string
		{
			let model = Entity::find()
				.filter(Column::CiStr.eq("ABCD"))
				.one(&db)
				.await
				.expect("find by case insensitive string")
				.expect("find by case insensitive string");
			assert_eq!(model.id, i);
			// The string should be read back as-is
			assert_eq!(model.ci_str, "abcd");
		}
	}
}
