#[cfg(feature = "mysql")]
use diesel::mysql::MysqlConnection;
use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;
use serde::{Deserialize, Serialize};

use crate::schema::{milestones_announced, movie_additions, movies, users};
use crate::settings::DatabaseConfig;

pub enum DbConn {
    #[cfg(feature = "mysql")]
    Mysql(MysqlConnection),
    Sqlite(SqliteConnection),
}

impl DbConn {
    pub fn load_movies(&mut self) -> QueryResult<Vec<Movie>> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => movies::table.load::<Movie>(c),
            DbConn::Sqlite(c) => movies::table.load::<Movie>(c),
        }
    }

    pub fn tmdb_id_count(&mut self, id: Option<i64>) -> QueryResult<i64> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => movies::table
                .filter(movies::tmdb_id.eq(id))
                .count()
                .get_result(c),
            DbConn::Sqlite(c) => movies::table
                .filter(movies::tmdb_id.eq(id))
                .count()
                .get_result(c),
        }
    }

    /// Delete all movies for `user` whose hash is NOT in `keep_hashes`.
    /// Called during a full-sync push so stale entries are removed automatically.
    pub fn delete_movies_not_in(
        &mut self,
        user: &str,
        keep_hashes: &[String],
    ) -> QueryResult<usize> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => {
                if keep_hashes.is_empty() {
                    diesel::delete(movies::table.filter(movies::user.eq(user))).execute(c)
                } else {
                    diesel::delete(
                        movies::table
                            .filter(movies::user.eq(user).and(movies::hash.ne_all(keep_hashes))),
                    )
                    .execute(c)
                }
            }
            DbConn::Sqlite(c) => {
                if keep_hashes.is_empty() {
                    diesel::delete(movies::table.filter(movies::user.eq(user))).execute(c)
                } else {
                    diesel::delete(
                        movies::table
                            .filter(movies::user.eq(user).and(movies::hash.ne_all(keep_hashes))),
                    )
                    .execute(c)
                }
            }
        }
    }

    /// Load all movies for `user` whose hash is NOT in `keep_hashes`.
    /// Used to detect which entries will be removed by `delete_movies_not_in`.
    pub fn load_movies_for_user_not_in(
        &mut self,
        user: &str,
        keep_hashes: &[String],
    ) -> QueryResult<Vec<Movie>> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => {
                if keep_hashes.is_empty() {
                    movies::table.filter(movies::user.eq(user)).load::<Movie>(c)
                } else {
                    movies::table
                        .filter(movies::user.eq(user).and(movies::hash.ne_all(keep_hashes)))
                        .load::<Movie>(c)
                }
            }
            DbConn::Sqlite(c) => {
                if keep_hashes.is_empty() {
                    movies::table.filter(movies::user.eq(user)).load::<Movie>(c)
                } else {
                    movies::table
                        .filter(movies::user.eq(user).and(movies::hash.ne_all(keep_hashes)))
                        .load::<Movie>(c)
                }
            }
        }
    }

    /// Load all movies with the given TMDB ID (across all users).
    pub fn load_movies_by_tmdb_id(&mut self, id: Option<i64>) -> QueryResult<Vec<Movie>> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => movies::table
                .filter(movies::tmdb_id.eq(id))
                .load::<Movie>(c),
            DbConn::Sqlite(c) => movies::table
                .filter(movies::tmdb_id.eq(id))
                .load::<Movie>(c),
        }
    }

    pub fn insert_or_ignore_movies(&mut self, movie_list: &[Movie]) -> QueryResult<usize> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => diesel::insert_or_ignore_into(movies::table)
                .values(movie_list)
                .execute(c),
            DbConn::Sqlite(c) => diesel::insert_or_ignore_into(movies::table)
                .values(movie_list)
                .execute(c),
        }
    }

    pub fn load_user(&mut self, user_name: &str) -> QueryResult<Vec<User>> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => users::table
                .filter(users::name.eq(user_name))
                .load::<User>(c),
            DbConn::Sqlite(c) => users::table
                .filter(users::name.eq(user_name))
                .load::<User>(c),
        }
    }

    /// Insert or replace a user record (used by the admin tool to create/update users).
    pub fn replace_user(&mut self, user: &User) -> QueryResult<usize> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => diesel::replace_into(users::table).values(user).execute(c),
            DbConn::Sqlite(c) => diesel::replace_into(users::table).values(user).execute(c),
        }
    }

    pub fn insert_movie_additions(&mut self, items: &[NewMovieAddition]) -> QueryResult<usize> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => diesel::insert_or_ignore_into(movie_additions::table)
                .values(items)
                .execute(c),
            DbConn::Sqlite(c) => diesel::insert_or_ignore_into(movie_additions::table)
                .values(items)
                .execute(c),
        }
    }

    pub fn load_additions_in_period(
        &mut self,
        from: &str,
        to: &str,
    ) -> QueryResult<Vec<MovieAdditionRow>> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => movie_additions::table
                .filter(
                    movie_additions::added_at
                        .ge(from)
                        .and(movie_additions::added_at.lt(to)),
                )
                .load::<MovieAdditionRow>(c),
            DbConn::Sqlite(c) => movie_additions::table
                .filter(
                    movie_additions::added_at
                        .ge(from)
                        .and(movie_additions::added_at.lt(to)),
                )
                .load::<MovieAdditionRow>(c),
        }
    }

    pub fn count_unique_movies(&mut self) -> QueryResult<i64> {
        #[allow(deprecated)]
        use diesel::dsl::count_distinct;
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) =>
            {
                #[allow(deprecated)]
                movies::table
                    .filter(movies::tmdb_id.is_not_null())
                    .select(count_distinct(movies::tmdb_id))
                    .get_result(c)
            }
            DbConn::Sqlite(c) =>
            {
                #[allow(deprecated)]
                movies::table
                    .filter(movies::tmdb_id.is_not_null())
                    .select(count_distinct(movies::tmdb_id))
                    .get_result(c)
            }
        }
    }

    pub fn total_movies_size(&mut self) -> QueryResult<i64> {
        // Load all sizes and sum in Rust — avoids Diesel's Numeric/BigInt type mismatch
        // for SQLite's SUM aggregate.
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => movies::table
                .select(movies::size)
                .load::<i64>(c)
                .map(|v| v.into_iter().sum()),
            DbConn::Sqlite(c) => movies::table
                .select(movies::size)
                .load::<i64>(c)
                .map(|v| v.into_iter().sum()),
        }
    }

    pub fn milestone_announced(&mut self, count: i64) -> QueryResult<bool> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => milestones_announced::table
                .filter(milestones_announced::count.eq(count))
                .count()
                .get_result::<i64>(c)
                .map(|n| n > 0),
            DbConn::Sqlite(c) => milestones_announced::table
                .filter(milestones_announced::count.eq(count))
                .count()
                .get_result::<i64>(c)
                .map(|n| n > 0),
        }
    }

    pub fn announce_milestone(&mut self, count: i64) -> QueryResult<usize> {
        match self {
            #[cfg(feature = "mysql")]
            DbConn::Mysql(c) => diesel::insert_or_ignore_into(milestones_announced::table)
                .values(milestones_announced::count.eq(count))
                .execute(c),
            DbConn::Sqlite(c) => diesel::insert_or_ignore_into(milestones_announced::table)
                .values(milestones_announced::count.eq(count))
                .execute(c),
        }
    }
}

#[derive(Serialize, Deserialize, Insertable, Queryable, Debug, Clone)]
#[diesel(table_name = crate::schema::movies)]
pub struct Movie {
    pub title: String,
    pub year: i32,
    pub size: i64,
    pub hash: String,
    pub tmdb_id: Option<i64>,
    pub audio_languages: Option<String>,
    pub subtitle_languages: Option<String>,
    pub resolution: Option<String>,
    pub dynamic_range: Option<String>,
    pub bitrate: Option<i64>,
    pub user: String,
}

#[derive(Insertable, Queryable)]
#[diesel(table_name = crate::schema::users)]
pub struct User {
    pub name: String,
    pub hash: String,
    pub salt: String,
}

#[derive(Insertable)]
#[diesel(table_name = crate::schema::movie_additions)]
pub struct NewMovieAddition {
    pub tmdb_id: i64,
    pub title: String,
    pub year: i32,
    pub user: String,
    pub size: i64,
}

#[derive(Queryable)]
pub struct MovieAdditionRow {
    pub id: i32,
    pub tmdb_id: i64,
    pub title: String,
    pub year: i32,
    pub user: String,
    pub size: i64,
    pub added_at: String,
}

pub async fn connect(config: &DatabaseConfig) -> ConnectionResult<DbConn> {
    match config {
        #[cfg(feature = "mysql")]
        DatabaseConfig::Mysql(endpoint) => {
            let url = format!(
                "mysql://{}:{}@{}:{}/{}",
                endpoint.credentials.user,
                endpoint.credentials.password,
                endpoint.host,
                endpoint.port,
                endpoint.database,
            );
            Ok(DbConn::Mysql(MysqlConnection::establish(&url)?))
        }
        DatabaseConfig::Sqlite(path) => Ok(DbConn::Sqlite(SqliteConnection::establish(path)?)),
    }
}
