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

use rusqlite::Connection;

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

//mods
pub fn mods_insert(config: Cfg, id: String, name: String, versions: Vec<String>) -> Result<(), Box<dyn std::error::Error>> {

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

    let data = format!("{}/data.db", config.data);
    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 = 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 {
        println!("Found id {:?}", id.as_ref().unwrap());
        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) -> Result<String, Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    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 {
        println!("Found id {:?}", id.as_ref().unwrap());
        mod_id = id?;
    };

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

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

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

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

    if mods.is_empty() { return Err(Box::new(Error::new(ErrorKind::Other, "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(dbg!(format!("SELECT id, versions FROM mods {}", wherestr).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 version = ver?;
        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(Box::new(Error::new(ErrorKind::NotFound, "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) -> Result<(), Box<dyn std::error::Error>> {
    println!("Inserting {} into current list({})", mod_id, list_id);

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

    
    connection.execute(format!("INSERT INTO {} VALUES (?1, ?2, ?3, ?4)", 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) -> Result<Vec<String>, Box<dyn std::error::Error>> {
    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 {
        println!("Found id {:?}", id.as_ref().unwrap());
        mod_ids.push(id?)
    };

    match mod_ids.is_empty() {
        true => Err(Box::new(std::io::Error::new(ErrorKind::NotFound, "NO_MODS"))),
        false => Ok(mod_ids),
    }
}


pub fn userlist_remove(config: Cfg, list_id: String, mod_id: String) -> Result<(), Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    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) -> Result<String, Box<dyn std::error::Error>> {
    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 {} 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 {
        println!("Found id {:?}", ver);
        version = ver?;
    };

    match version.is_empty() {
        true => Err(Box::new(Error::new(ErrorKind::NotFound, "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 = 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(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) -> Result<(), Box<dyn std::error::Error>> {
    let data = format!("{}/data.db", config.data);
    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_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?;
        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
pub fn lists_insert(config: Cfg, id: String, mc_version: String, mod_loader: Modloader) -> Result<(), Box<dyn std::error::Error>> {
    println!("Creating list {}", id);

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

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

    Ok(())
}

pub fn lists_remove(config: Cfg, id: String) -> Result<(), Box<dyn std::error::Error>> {
    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: String) -> Result<List, Box<dyn std::error::Error>> {
    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 };
    let mut stmt = connection.prepare("SELECT mc_version, modloader 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)?])
    })?;

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

    if list.id.is_empty() { return Err(Box::new(Error::new(ErrorKind::Other, "LIST_NOT_FOUND"))); }
    
    Ok(list)
}

pub fn lists_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 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 {
        println!("Found id {:?}", id.as_ref().unwrap());
        list_ids.push(id?)
    };

    match list_ids.is_empty() {
        true => Err(Box::new(std::io::Error::new(ErrorKind::NotFound, "NO_LISTS"))),
        false => Ok(list_ids),
    }
}

//config
pub fn config_change_current_list(config: Cfg, id: 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 = 'current_list'", [id])?;
    Ok(())
}

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

    connection.execute(format!("ALTER TABLE {} ADD '{}' {}", table, column, c_type).as_str(), ())?;
    Ok(())
}

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

    let data = format!("{}/data.db", config.data);
    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 );
         INSERT INTO 'user_config' VALUES ( 'db_version', '0.2' );
         INSERT INTO 'user_config' VALUES ( 'current_list', '...' )",
    )?;
    
    Ok(())
}