use std::io::{Error, ErrorKind};

use rusqlite::Connection;

use crate::{
    config::Cfg,
    error::{ErrorType, MLError, MLE},
    List, Modloader,
};

//MODS
pub fn mods_insert(config: &Cfg, id: &str, slug: &str, name: &str) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(
        "INSERT INTO mods (id, slug, title) VALUES (?1, ?2, ?3)",
        [id, slug, name.replace('\'', "").as_str()],
    )?;

    Ok(())
}

pub fn mods_get_all_ids(
    config: &Cfg,
) -> Result<Vec<String>, Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut mods: Vec<String> = Vec::new();

    let mut stmt = connection.prepare("SELECT id FROM mods")?;
    let id_iter = stmt.query_map([], |row| row.get::<usize, String>(0))?;

    for id in id_iter {
        mods.push(id?);
    }

    match mods.is_empty() {
        true => Err(Box::new(Error::new(ErrorKind::NotFound, "NO_MODS_ALL"))),
        false => Ok(mods),
    }
}

///Get mod id based on the slug or name
///# Arguments
///
///* `data` - file directory of the database
///* `slug` - Slug or Name of a mod
///
///# Failure
///
///Will return `MLError` when no mod id is found
pub fn mods_get_id(data: &str, slug: &str) -> MLE<String> {
    let data = format!("{data}/data.db");
    let connection = Connection::open(data)?;

    let mut mod_id = String::new();

    //get from slug
    let mut stmt = connection.prepare("SELECT id FROM mods WHERE slug = ?")?;
    let id_iter = stmt.query_map([slug], |row| row.get::<usize, String>(0))?;

    for id in id_iter {
        mod_id = id?;
    }
    //get from id if no slug found
    if mod_id.is_empty() {
        let mut stmt =
            connection.prepare("SELECT id FROM mods WHERE id = ?")?;
        let id_iter =
            stmt.query_map([slug], |row| row.get::<usize, String>(0))?;

        for id in id_iter {
            mod_id = id?;
        }
    }
    //get from title if no id found from slug
    if mod_id.is_empty() {
        let mut stmt =
            connection.prepare("SELECT id FROM mods WHERE title = ?")?;
        let id_iter =
            stmt.query_map([slug], |row| row.get::<usize, String>(0))?;

        for id in id_iter {
            mod_id = id?;
        }
    }

    if mod_id.is_empty() {
        return Err(MLError::new(ErrorType::DBError, "GI_MOD_NOT_FOUND"));
    };

    Ok(mod_id)
}

pub struct ModInfo {
    pub slug: String,
    pub title: String,
}

pub fn mods_get_info(config: &Cfg, id: &str) -> MLE<ModInfo> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let mut mod_info: Option<ModInfo> = None;
    let mut stmt =
        connection.prepare("SELECT title, slug FROM mods WHERE id = ?")?;
    let name_iter = stmt.query_map([id], |row| {
        Ok(vec![
            row.get::<usize, String>(0)?,
            row.get::<usize, String>(1)?,
        ])
    })?;

    for info in name_iter {
        let i = info?;
        mod_info = Some(ModInfo {
            title: String::from(&i[0]),
            slug: String::from(&i[1]),
        });
    }

    match mod_info.is_none() {
        true => Err(MLError::new(ErrorType::DBError, "GN_MOD_NOT_FOUND")),
        false => Ok(mod_info.unwrap()),
    }
}

pub fn mods_remove(config: &Cfg, id: &str) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute("DELETE FROM mods WHERE id = ?", [id])?;

    Ok(())
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct DBModlistVersions {
    pub mod_id: String,
    pub versions: String,
}

pub fn mods_get_versions(
    config: &Cfg,
    mods: Vec<String>,
) -> MLE<Vec<DBModlistVersions>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    if mods.is_empty() {
        return Err(MLError::new(ErrorType::ArgumentError, "MODS_NO_INPUT"));
    }

    let mut wherestr = String::from("WHERE");
    for (i, id) in mods.iter().enumerate() {
        let mut or = " OR";
        if i == mods.len() - 1 {
            or = "";
        };
        wherestr = format!("{wherestr} id = '{id}'{or}");
    }

    let mut versionmaps: Vec<DBModlistVersions> = Vec::new();
    let mut stmt = connection.prepare(
        format!("SELECT id, versions, title FROM mods {wherestr}").as_str(),
    )?;
    let id_iter = stmt.query_map([], |row| {
        Ok(vec![
            row.get::<usize, String>(0)?,
            row.get::<usize, String>(1)?,
            row.get::<usize, String>(2)?,
        ])
    })?;

    for ver in id_iter {
        let version = ver?;
        versionmaps.push(DBModlistVersions {
            mod_id: String::from(&version[0]),
            versions: String::from(&version[1]),
        });
    }

    match versionmaps.is_empty() {
        true => Err(MLError::new(ErrorType::DBError, "MODS_MODS_NOT_FOUND")),
        false => Ok(versionmaps),
    }
}

//userlist
pub fn userlist_insert(
    config: &Cfg,
    list_id: &str,
    mod_id: &str,
    current_version: &str,
    applicable_versions: Vec<String>,
    current_link: &str,
    set_version: bool,
) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let sv = match set_version {
        true => "1",
        false => "0",
    };

    connection.execute(
        format!(
            "INSERT INTO {list_id} VALUES (?1, ?2, ?3, ?4, 'NONE', ?5)"
        )
        .as_str(),
        [
            mod_id,
            current_version,
            applicable_versions.join("|").as_str(),
            current_link,
            sv,
        ],
    )?;

    Ok(())
}

pub fn userlist_get_all_ids(config: &Cfg, list_id: &str) -> MLE<Vec<String>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut mod_ids: Vec<String> = Vec::new();
    let mut stmt = connection
        .prepare(format!("SELECT mod_id FROM {list_id}").as_str())?;
    let id_iter = stmt.query_map([], |row| row.get::<usize, String>(0))?;

    for id in id_iter {
        mod_ids.push(id?);
    }

    match mod_ids.is_empty() {
        true => Err(MLError::new(
            ErrorType::DBError,
            &format!("NO_MODS_USERLIST{list_id}"),
        )),
        false => Ok(mod_ids),
    }
}

pub fn userlist_remove(config: &Cfg, list_id: &str, mod_id: &str) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(
        format!("DELETE FROM {list_id} WHERE mod_id = ?").as_str(),
        [mod_id],
    )?;
    Ok(())
}

pub fn userlist_get_applicable_versions(
    config: &Cfg,
    list_id: String,
    mod_id: String,
) -> MLE<String> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut version: String = String::new();
    let mut stmt = connection.prepare(
        format!(
            "SELECT applicable_versions FROM {list_id} WHERE mod_id = ?"
        )
        .as_str(),
    )?;
    let ver_iter =
        stmt.query_map([mod_id], |row| row.get::<usize, String>(0))?;

    for ver in ver_iter {
        version = ver?;
    }

    match version.is_empty() {
        true => Err(MLError::new(ErrorType::DBError, "GAV_MOD_NOT_FOUND")),
        false => Ok(version),
    }
}

pub fn userlist_get_all_applicable_versions_with_mods(
    config: &Cfg,
    list_id: String,
) -> MLE<Vec<(String, String)>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let mut versions: Vec<(String, String)> = Vec::new();
    let mut stmt = connection.prepare(
        format!("SELECT mod_id, applicable_versions FROM {list_id}").as_str(),
    )?;
    let id_iter = stmt.query_map([], |row| {
        Ok(vec![
            row.get::<usize, String>(0)?,
            row.get::<usize, String>(1)?,
        ])
    })?;

    for ver in id_iter {
        let out = ver?;
        versions.push((out[0].clone(), out[1].clone()));
    }

    if versions.is_empty() {
        return Err(MLError::new(ErrorType::DBError, "NO_MODS_ON_LIST"));
    };

    Ok(versions)
}

pub fn userlist_get_current_version(
    config: &Cfg,
    list_id: &str,
    mod_id: &str,
) -> MLE<String> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut version: String = String::new();
    let mut stmt = connection.prepare(
        format!("SELECT current_version FROM {list_id} WHERE mod_id = ?")
            .as_str(),
    )?;
    let ver_iter =
        stmt.query_map([&mod_id], |row| row.get::<usize, String>(0))?;

    for ver in ver_iter {
        version = ver?;
    }

    match version.is_empty() {
        true => Err(MLError::new(ErrorType::DBError, "GCV_MOD_NOT_FOUND")),
        false => Ok(version),
    }
}

pub fn userlist_get_all_current_version_ids(
    config: &Cfg,
    list_id: String,
) -> MLE<Vec<String>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let mut versions: Vec<String> = Vec::new();
    let mut stmt = connection
        .prepare(format!("SELECT current_version FROM {list_id}").as_str())?;
    let id_iter = stmt.query_map([], |row| row.get::<usize, String>(0))?;

    for id in id_iter {
        versions.push(id?);
    }

    if versions.is_empty() {
        return Err(MLError::new(ErrorType::DBError, "NO_MODS_ON_LIST"));
    };

    Ok(versions)
}

pub fn userlist_get_all_current_versions_with_mods(
    config: &Cfg,
    list_id: String,
) -> Result<Vec<(String, String)>, Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let mut versions: Vec<(String, String)> = Vec::new();
    let mut stmt = connection.prepare(
        format!("SELECT mod_id, current_version FROM {list_id}").as_str(),
    )?;
    let id_iter = stmt.query_map([], |row| {
        Ok(vec![
            row.get::<usize, String>(0)?,
            row.get::<usize, String>(1)?,
        ])
    })?;

    for ver in id_iter {
        let out = ver?;
        versions.push((out[0].clone(), out[1].clone()));
    }

    if versions.is_empty() {
        return Err(Box::new(std::io::Error::new(
            ErrorKind::Other,
            "NO_MODS_ON_LIST",
        )));
    };

    Ok(versions)
}

pub fn userlist_get_set_version(
    config: &Cfg,
    list_id: &str,
    mod_id: &str,
) -> MLE<bool> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut set_version: bool = false;
    let mut stmt = connection.prepare(
        format!("SELECT set_version FROM {list_id} WHERE mod_id = ?")
            .as_str(),
    )?;
    let ver_iter =
        stmt.query_map([&mod_id], |row| row.get::<usize, bool>(0))?;

    for ver in ver_iter {
        set_version = ver?;
    }

    Ok(set_version)
}

pub fn userlist_change_versions(
    config: &Cfg,
    list_id: String,
    current_version: String,
    versions: String,
    link: String,
    mod_id: String,
) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(format!("UPDATE {list_id} SET current_version = ?1, applicable_versions = ?2, current_download = ?3 WHERE mod_id = ?4").as_str(), [current_version, versions, link, mod_id])?;
    Ok(())
}

pub fn userlist_add_disabled_versions(
    config: &Cfg,
    list_id: String,
    disabled_version: String,
    mod_id: String,
) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let currently_disabled_versions = userlist_get_disabled_versions(
        config,
        String::from(&list_id),
        String::from(&mod_id),
    )?;
    let disabled_versions = match currently_disabled_versions == "NONE" {
        true => disabled_version,
        false => {
            format!("{currently_disabled_versions}|{disabled_version}")
        }
    };

    connection.execute(
        format!(
            "UPDATE {list_id} SET disabled_versions = ?1 WHERE mod_id = ?2"
        )
        .as_str(),
        [disabled_versions, mod_id],
    )?;
    Ok(())
}

pub fn userlist_get_disabled_versions(
    config: &Cfg,
    list_id: String,
    mod_id: String,
) -> MLE<String> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut version: String = String::new();
    let mut stmt = connection.prepare(
        format!("SELECT disabled_versions FROM {list_id} WHERE mod_id = ?")
            .as_str(),
    )?;
    let ver_iter =
        stmt.query_map([mod_id], |row| row.get::<usize, String>(0))?;

    for ver in ver_iter {
        version = ver?;
    }

    match version.is_empty() {
        true => Err(MLError::new(ErrorType::DBError, "GDV_MOD_NOT_FOUND")),
        false => Ok(version),
    }
}

pub fn userlist_get_all_downloads(
    config: &Cfg,
    list_id: String,
) -> Result<Vec<String>, Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut links: Vec<String> = Vec::new();
    let mut stmt = connection.prepare(
        format!("SELECT current_download FROM {list_id}").as_str(),
    )?;
    let link_iter = stmt.query_map([], |row| row.get::<usize, String>(0))?;

    for link in link_iter {
        let l = link?;
        links.push(l);
    }

    if links.is_empty() {
        return Err(Box::new(std::io::Error::new(
            ErrorKind::Other,
            "NO_MODS_ON_LIST",
        )));
    };

    Ok(links)
}

//lists
///Inserts into lists table and creates new table
pub fn lists_insert(
    config: &Cfg,
    id: &str,
    mc_version: &str,
    mod_loader: &Modloader,
    download_folder: &str,
) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(
        "INSERT INTO lists VALUES (?1, ?2, ?3, ?4)",
        [id, mc_version, &mod_loader.to_string(), download_folder],
    )?;
    connection.execute(format!("CREATE TABLE {id}( 'mod_id' TEXT, 'current_version' TEXT, 'applicable_versions' BLOB, 'current_download' TEXT, 'disabled_versions' TEXT DEFAULT 'NONE', 'set_version' INTEGER, CONSTRAINT {id}_PK PRIMARY KEY (mod_id) )").as_str(), [])?;

    Ok(())
}

pub fn lists_remove(config: &Cfg, id: &str) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute("DELETE FROM lists WHERE id = ?", [&id])?;
    connection.execute(format!("DROP TABLE {id}").as_str(), [])?;
    Ok(())
}

pub fn lists_get(config: &Cfg, list_id: &str) -> MLE<List> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut list = List {
        id: String::new(),
        mc_version: String::new(),
        modloader: Modloader::Fabric,
        download_folder: String::new(),
    };
    let mut stmt = connection.prepare(
        "SELECT mc_version, modloader, download_folder FROM lists WHERE id = ?",
    )?;

    let list_iter = stmt.query_map([&list_id], |row| {
        Ok(vec![
            row.get::<usize, String>(0)?,
            row.get::<usize, String>(1)?,
            row.get::<usize, String>(2)?,
        ])
    })?;

    for l in list_iter {
        let li = l?;
        list = List {
            id: list_id.to_string(),
            mc_version: String::from(&li[0]),
            modloader: Modloader::from(&li[1])?,
            download_folder: String::from(&li[2]),
        };
    }

    if list.id.is_empty() {
        return Err(MLError::new(ErrorType::DBError, "LIST_NOT_FOUND"));
    }

    Ok(list)
}

pub fn lists_version(config: &Cfg, list_id: &str, version: &str) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    connection.execute(
        "UPDATE lists SET mc_version = ? WHERE id = ?",
        [version, list_id],
    )?;
    Ok(())
}

pub fn lists_get_all_ids(config: &Cfg) -> MLE<Vec<String>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut list_ids: Vec<String> = Vec::new();
    let mut stmt = connection.prepare("SELECT id FROM lists")?;
    let id_iter = stmt.query_map([], |row| row.get::<usize, String>(0))?;

    for id in id_iter {
        list_ids.push(id?);
    }

    match list_ids.is_empty() {
        true => Err(MLError::new(ErrorType::DBError, "NO_LISTS")),
        false => Ok(list_ids),
    }
}

//config
pub fn config_change_current_list(config: &Cfg, id: &str) -> MLE<()> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(
        "UPDATE user_config SET value = ? WHERE id = 'current_list'",
        [id],
    )?;
    Ok(())
}

pub fn config_get_current_list(config: &Cfg) -> MLE<String> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data).unwrap();

    let mut list_id = String::new();
    let mut stmt = connection
        .prepare("SELECT value FROM user_config WHERE id = 'current_list'")?;
    let list_iter = stmt.query_map([], |row| row.get::<usize, String>(0))?;

    for list in list_iter {
        list_id = list?;
    }

    if list_id.is_empty() {
        return Err(MLError::new(ErrorType::DBError, "NO_CURRENT_LIST"));
    }

    Ok(list_id)
}

//SETUP(UPDATES)
pub fn s_userlist_update_download(
    config: &Cfg,
    list_id: String,
    mod_id: String,
    link: String,
) -> Result<(), Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(
        format!(
            "UPDATE {list_id} SET current_download = ?1 WHERE mod_id = ?2"
        )
        .as_str(),
        [link, mod_id],
    )?;
    Ok(())
}

pub fn s_config_create_version(
    config: &Cfg,
) -> Result<(), Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(
        "INSERT INTO 'user_config' VALUES ( 'db_version', '0.2' )",
        (),
    )?;
    Ok(())
}

pub fn s_config_update_version(
    config: &Cfg,
    ver: String,
) -> Result<(), Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    connection.execute(
        "UPDATE user_config SET value = ? WHERE id = 'db_version'",
        [ver],
    )?;
    Ok(())
}

pub fn s_config_get_version(
    config: &Cfg,
) -> Result<String, Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let mut version: String = String::new();
    let mut stmt = connection
        .prepare("SELECT value FROM user_config WHERE id = 'db_version'")?;
    let ver_iter = stmt.query_map([], |row| row.get::<usize, String>(0))?;

    for ver in ver_iter {
        version = ver?;
    }

    if version.is_empty() {
        return Err(Box::new(std::io::Error::new(
            ErrorKind::Other,
            "NO_DBVERSION",
        )));
    };
    Ok(version)
}

pub fn s_insert_column(
    config: &Cfg,
    table: String,
    column: String,
    c_type: String,
    default: Option<String>,
) -> Result<(), Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = Connection::open(data)?;

    let mut sql = format!("ALTER TABLE {table} ADD '{column}' {c_type}");

    if default.is_some() {
        sql = format!("{} DEFAULT {}", sql, default.unwrap());
    }

    connection.execute(sql.as_str(), ())?;
    Ok(())
}

pub fn db_setup(path: &str) -> MLE<()> {
    let connection = Connection::open(path)?;

    connection.execute_batch(
        "CREATE TABLE 'user_config' ( 'id' TEXT, 'value' TEXT );
         CREATE TABLE 'mods' ( 'id' TEXT, 'slug' TEXT, 'title' TEXT, CONSTRAINT mods_PK PRIMARY KEY (id) );
         CREATE TABLE 'lists' ( 'id' TEXT, 'mc_version' TEXT, 'modloader' TEXT, 'download_folder' TEXT );
         INSERT INTO 'user_config' VALUES ( 'db_version', '0.5' );
         INSERT INTO 'user_config' VALUES ( 'current_list', '...' )",
    )?;

    Ok(())
}