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

use rusqlite::Connection;

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

//mods
pub fn mods_insert(config: Cfg, id: String, name: String, versions: Vec<String>) -> MLE<()> {

    println!("Inserting mod {}({}) into database", name, id);

    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;

    connection.execute(
        "INSERT INTO mods (id, name, versions) VALUES (?1, ?2, ?3)",
        [id, name.replace('\'', ""), versions.join("|")]
    )?;

    Ok(())
}

pub fn mods_get_all_ids(config: Cfg) -> Result<Vec<String>, Box<dyn std::error::Error>> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    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"))),
        false => Ok(mods),
    }
}

pub fn mods_get_id(config: Cfg, name: String) -> MLE<String> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;
    
    let mut mod_id = String::new();
    let mut stmt = connection.prepare("SELECT id FROM mods WHERE name = ?")?;
    let id_iter = stmt.query_map([name], |row| {
        row.get::<usize, String>(0)
    })?;

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

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

pub fn mods_get_name(config: Cfg, id: &str) -> MLE<String> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;
    
    let mut mod_name = String::new();
    let mut stmt = connection.prepare("SELECT name FROM mods WHERE id = ?")?;
    let name_iter = stmt.query_map([id], |row| {
        row.get::<usize, String>(0)
    })?;

    for name in name_iter {
        mod_name = name?;
    };

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

pub fn mods_change_versions(config: Cfg, versions: String, mod_id: String) -> MLE<()> {

    //println!("Updating versions for {} with \n {}", mod_id, versions);

    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;

    connection.execute("UPDATE mods SET versions = ?1 WHERE id = ?2", [versions, mod_id])?;
    Ok(())
}

pub fn mods_remove(config: Cfg, id: String) -> MLE<()> {

    println!("Removing mod {} from database", id);

    let data = devdir(format!("{}/data.db", config.data).as_str());
    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 = devdir(format!("{}/data.db", config.data).as_str());
    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!("{} id = '{}'{}", wherestr, id, or);
    }

    let mut versionmaps: Vec<DBModlistVersions> = Vec::new();
    let mut stmt = connection.prepare(format!("SELECT id, versions, name 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?;
        println!("\t({}) Get versions from the database", String::from(&version[2]));
        //println!("Found versions {} for mod {}", version[1], version[0]);
        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: String, mod_id: String, current_version: String, applicable_versions: Vec<String>, current_link: String) -> MLE<()> {
    println!("Inserting {} into current list({})", mod_id, list_id);

    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;

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

    Ok(())
}

pub fn userlist_get_all_ids(config: Cfg, list_id: String) -> MLE<Vec<String>> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    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 {
        //println!("Found id {:?}", id.as_ref().unwrap());
        mod_ids.push(id?)
    };

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


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

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


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

    let mut version: String = String::new();
    let mut stmt = connection.prepare(format!("SELECT applicable_versions FROM {} WHERE mod_id = ?", list_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 = devdir(format!("{}/data.db", config.data).as_str());
    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].to_owned(), out[1].to_owned()));
    };

    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: String, mod_id: String) -> MLE<String> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data).unwrap();

    let mut version: String = String::new();
    let mut stmt = connection.prepare(format!("SELECT current_version FROM {} WHERE mod_id = ?", list_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) -> Result<Vec<String>, Box<dyn std::error::Error>> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    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(Box::new(std::io::Error::new(ErrorKind::Other, "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 = devdir(format!("{}/data.db", config.data).as_str());
    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].to_owned(), out[1].to_owned()));
    };

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

    Ok(versions)
}

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

    connection.execute(format!("UPDATE {} SET current_version = ?1, applicable_versions = ?2, current_download = ?3 WHERE mod_id = ?4", list_id).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 = devdir(format!("{}/data.db", config.data).as_str());
    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 {} SET disabled_versions = ?1 WHERE mod_id = ?2", list_id).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 = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data).unwrap();

    let mut version: String = String::new();
    let mut stmt = connection.prepare(format!("SELECT disabled_versions FROM {} WHERE mod_id = ?", list_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 = devdir(format!("{}/data.db", config.data).as_str());
    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?;
        println!("Found link {}", String::from(&l));
        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: String, mc_version: String, mod_loader: Modloader, download_folder: String) -> MLE<()> {
    println!("Creating list {}", id);

    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;

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

    Ok(())
}

pub fn lists_remove(config: Cfg, id: String) -> MLE<()> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    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: String) -> MLE<List> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    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: String::from(&list_id), 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: String, version: String) -> MLE<()> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    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 = devdir(format!("{}/data.db", config.data).as_str());
    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: String) -> MLE<()> {
    let data = devdir(format!("{}/data.db", config.data).as_str());
    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 = devdir(format!("{}/data.db", config.data).as_str());
    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 = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;

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

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

    let mut sql = format!("ALTER TABLE {} ADD '{}' {}", table, column, c_type);
    
    if default.is_some() {
        sql = format!("{} DEFAULT {}", sql, default.unwrap());
    }

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

pub fn db_setup(config: Cfg) -> Result<(), Box<dyn std::error::Error>> {
    
    println!("Initiating database");

    let data = devdir(format!("{}/data.db", config.data).as_str());
    let connection = Connection::open(data)?;

    connection.execute_batch(
        "CREATE TABLE 'user_config' ( 'id' TEXT, 'value' TEXT );
         CREATE TABLE 'mods' ( 'id' TEXT, 'name' TEXT, 'versions' TEXT );
         CREATE TABLE 'lists' ( 'id' TEXT, 'mc_version' TEXT, 'modloader' TEXT, 'download_folder' TEXT );
         INSERT INTO 'user_config' VALUES ( 'db_version', '0.4' );
         INSERT INTO 'user_config' VALUES ( 'current_list', '...' )",
    )?;
    
    Ok(())
}