use std::io::ErrorKind;

use crate::{Modloader, config::Cfg, List, modrinth::Version, get_modloader};

//TODO use prepared statements / change to rusqlite

//MODS
pub fn insert_mod(config: Cfg, id: String, name: String, versions: Vec<String>) -> Result<(), sqlite::Error> {

    println!("Inserting into modlist");

    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("INSERT INTO mods VALUES ('{}', '{}', '{}')", id, name.replace('\'', ""), versions.join("|"));

    dbg!(&sql);

    connection.execute(sql)
}

pub fn insert_mod_in_list(config: Cfg, list: List, id: String, current_version: String, applicable_versions: Vec<Version>, current_link: String) -> Result<(), sqlite::Error> {

    println!("Inserting into current list");

    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let mut applicable_versions_vec = vec![];

    for ver in applicable_versions {
        applicable_versions_vec.push(ver.id);
    }

    let sql = format!("INSERT INTO {} VALUES ('{}', '{}', '{}', '{}')", list.id, id, current_version, applicable_versions_vec.join("|"), current_link);
    
    connection.execute(sql)
}

pub fn remove_mod_from_list(config: Cfg, list: List, mod_id: String) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("DELETE FROM {} WHERE mod_id = '{}'", list.id, mod_id);
    
    dbg!(&sql);

    connection.execute(sql)
}

pub fn get_mods(config: Cfg) -> Result<Vec<String>, Box<dyn std::error::Error>> {

    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = "SELECT id FROM mods";
    
    let mut mods: Vec<String> = Vec::new();
    //TODO catch sql errors better
    connection.iterate(sql, |ids| {
        if ids.is_empty() { return false; };
        for &(_column, value) in ids.iter() {
            mods.push(String::from(value.unwrap()));
        }
        true
    }).unwrap();
    match mods.is_empty() {
        true => Err(Box::new(std::io::Error::new(ErrorKind::NotFound, "NO_MODS"))),
        false => Ok(mods),
    }
}

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

    let sql = format!("SELECT mod_id FROM {}", list.id);

    let mut mods: Vec<String> = Vec::new();
    //TODO catch sql errors better
    connection.iterate(sql, |ids| {
        if ids.is_empty() { return false; };
        for &(_column, value) in ids.iter() {
            mods.push(String::from(value.unwrap()));
        }
        true
    }).unwrap();
    match mods.is_empty() {
        true => Err(Box::new(std::io::Error::new(ErrorKind::NotFound, "NO_MODS"))),
        false => Ok(mods),
    }
}

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

    let sql = format!("SELECT id FROM mods WHERE name = '{}'", name);
    
    dbg!(&sql);

    let mut modification = String::new();
    //TODO catch sql errors better
    connection.iterate(sql, |id| {
        if id.is_empty() { return false; };
        for &(_column, value) in id.iter() {
            dbg!(&(_column, value));
            modification = String::from(value.unwrap());
        }
        true
    }).unwrap();
    
    dbg!(&modification);

    if modification.is_empty() { return Err(Box::new(std::io::Error::new(ErrorKind::NotFound, "MOD_NOT_IN_DATABASE"))) };
    
    Ok(modification)
}

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

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

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

let sql = format!("SELECT id, versions FROM mods {}", wherestr);

dbg!(&sql);

let mut versionmaps: Vec<DBModlistVersions> = Vec::new();
//TODO catch sql errors better
let mut cursor = connection.prepare(sql).unwrap().into_cursor();

while let Some(Ok(row)) = cursor.next() {
    println!("{}: {}", row.get::<String, _>(0), row.get::<String, _>(1));
    versionmaps.push(DBModlistVersions { mod_id: row.get::<String, _>(0), versions: row.get::<String, _>(1) })
};

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

Ok(versionmaps)
}

pub fn get_list_version(config: Cfg, list: List, mod_id: String) -> Result<String, Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();
    
    let sql = format!("SELECT applicable_versions FROM {} WHERE mod_id = '{}'", list.id, mod_id);

    //TODO catch sql errors better
    let mut version: String = String::new();
    connection.iterate(sql, |ver| {
        if ver.is_empty() { return false; };
        for &(_column, value) in ver.iter() {
            version = String::from(value.unwrap());
        }
        true
    }).unwrap();
    
    if version.is_empty() { return Err(Box::new(std::io::Error::new(ErrorKind::Other, "NO_MODS_ON_LIST"))); };

    Ok(version)
}


//LIST
pub fn insert_list(config: Cfg, id: String, mc_version: String, mod_loader: Modloader) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();
    
    let sql_list = format!("INSERT INTO lists VALUES ('{}', '{}', '{}')", id, mc_version, mod_loader.stringify());
    let sql_table = format!("CREATE TABLE '{}' ( 'mod_id' TEXT, 'current_version' TEXT, 'applicable_versions' BLOB, 'current_download' TEXT)", id);
    let sql = format!("{};{};", sql_list, sql_table);

    connection.execute(sql)
}

pub fn remove_list(config: Cfg, id: String) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql_list = format!("DELETE FROM lists WHERE id = '{}'", id);
    let sql_table = format!("DROP TABLE '{}'", id);
    let sql = format!("{};{};", sql_list, sql_table);

    connection.execute(sql)
}

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

    let sql = "SELECT id FROM lists";
    
    let mut list: Vec<String> = Vec::new();
    //TODO catch sql errors better
    connection.iterate(sql, |ids| {
        if ids.is_empty() { return false; };
        for &(_column, value) in ids.iter() {
            list.push(String::from(value.unwrap()));
        }
        true
    }).unwrap();
    match list.is_empty() {
        true => Err(Box::new(std::io::Error::new(ErrorKind::NotFound, "NO_LISTS"))),
        false => Ok(list),
    }
}

pub fn get_current_versions(config: Cfg, list: List) -> Result<Vec<String>, Box<std::io::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("SELECT current_version FROM {}", list.id);

    dbg!(&sql);

    let mut versions: Vec<String> = Vec::new();
    //TODO catch sql errors better
    let mut cursor = connection.prepare(sql).unwrap().into_cursor();

    while let Some(Ok(row)) = cursor.next() {
        versions.push(row.get::<String, _>(0));
    };

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

    Ok(versions)
}

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

    let sql = format!("SELECT mc_version, modloader FROM lists WHERE id = '{}'", id);
    
    let mut list = vec![];
    //TODO catch sql errors better
    connection.iterate(sql, |ids| {
        if ids.is_empty() { return false; };
        for &(_column, value) in ids.iter() {
            list.push(String::from(value.unwrap()));
        }
        true
    }).unwrap();

    if list.len() != 2 { return Err(Box::new(std::io::Error::new(ErrorKind::InvalidData, "LIST_MISSING_DATA"))) };
    
    Ok(List { id, mc_version: String::from(&list[0]), modloader: get_modloader(String::from(&list[1]))? })
}

pub fn change_list_versions(config: Cfg, list: List, current_version: String, versions: Vec<String>, mod_id: String) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("UPDATE {} SET current_version = '{}', applicable_versions = '{}' WHERE mod_id = '{}'", list.id, current_version, versions.join("|"), mod_id);

    connection.execute(sql)
}

//DOWNLOAD

pub fn insert_dl_link(config: Cfg, list: List, mod_id: String, link: String) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("UPDATE {} SET current_download = '{}' WHERE mod_id = '{}'", list.id, link, mod_id);

    connection.execute(sql)
}

pub fn get_dl_links(config: Cfg, list: List) -> Result<Vec<String>, Box<std::io::Error>> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("SELECT current_download FROM {}", list.id);

    dbg!(&sql);

    let mut links: Vec<String> = Vec::new();
    //TODO catch sql errors better
    let mut cursor = connection.prepare(sql).unwrap().into_cursor();

    while let Some(Ok(row)) = cursor.next() {
        links.push(row.get::<String, _>(0));
    };

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

    Ok(links)
}

//config
pub fn change_list(config: Cfg, id: String) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("UPDATE user_config SET value = '{}' WHERE id = 'current_list'", id);

    connection.execute(sql)
}

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

    let sql = "SELECT id FROM lists";
    
    let mut list: String = String::new();
    //TODO catch sql errors better
    connection.iterate(sql, |ids| {
        if ids.is_empty() { return false; };
        for &(_column, value) in ids.iter() {
            list = String::from(value.unwrap());
        }
        true
    }).unwrap();
    if list.is_empty() {
        get_lists(config)?;
        panic!("current list field should never be empty if there are other lists");
    };
    Ok(list)
}

pub fn update_dbversion(config: Cfg, ver: String) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = format!("UPDATE user_config SET value = '{}' WHERE id = 'db_version'", ver);

    connection.execute(sql)
}

pub fn create_dbversion(config: Cfg) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();
    let sql = "INSERT INTO 'user_config' VALUES ( 'db_version', '0.2' );";
    connection.execute(sql)
}

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

    let sql = "SELECT db_version FROM user_config";
    
    let mut ver: String = String::new();
    //TODO catch sql errors better
    connection.iterate(sql, |ids| {
        if ids.is_empty() { return false; };
        for &(_column, value) in ids.iter() {
            ver = String::from(value.unwrap());
        }
        true
    })?;
    if ver.is_empty() { return Err(Box::new(std::io::Error::new(ErrorKind::Other, "NO_DBVERSION"))); };
    Ok(ver)
}

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

    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let sql = "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 ); INSERT INTO 'user_config' VALUES ( 'db_version', '0.2' ); INSERT INTO 'user_config' VALUES ( 'current_list', '...' )";

    connection.execute(sql)
}

pub fn insert_column(config: Cfg, table: String, column: String, c_type: sqlite::Type) -> Result<(), sqlite::Error> {
    let data = format!("{}/data.db", config.data);
    let connection = sqlite::open(data).unwrap();

    let ct = match c_type {
        sqlite::Type::Null => "NULL",
        sqlite::Type::Float => "FLOAT",
        sqlite::Type::Binary => "BINARY",
        sqlite::Type::String => "TEXT",
        sqlite::Type::Integer => "INT",
    };

    let sql = format!("ALTER TABLE {} ADD '{}' {}", table, column, ct);
    connection.execute(sql)
}