import {Database} from 'sqlite'
import drive from 'sqlite3'
import * as fs from 'fs-extra'
import {CellValue, Workbook, Worksheet} from 'exceljs'

namespace Databases {
    // export const main:Database = new Database({filename:__dirname+`/databases/data.db`,driver:drive.Database})
    export type mainData = 'app_data_user' | 'app_data_shift' | 'app_data_tokens' | 'app_data_state' | 'app_data_shiftid' | 'app_data_shtemp'
    export function open(dbname:string):Database{
        var database:Database = new Database({filename:__dirname+`/databases/${dbname}.db`,driver:drive.Database})
        database.open()
        return database
    }
    export async function get(table:mainData,find:{[key:string]:string|undefined}|'all'):Promise<any[]>{
        var database:Database = open('data')
        if (find == 'all'){
            return database.all(`SELECT * FROM '${table}'`)
        } else {
            var keys = Object.keys(find) , vals = Object.values(find)
            var commands:string[] = [] 
            for (var k in keys){
                commands.push(`${keys[k]} = '${vals[k]}'`)
            }
            var qu:string = commands.join(' AND ')
            return database.all(`SELECT * FROM '${table}' WHERE ${qu}`)
        }
    }
    export async function getLast(table:mainData,find:{[key:string]:string|undefined}|'all',count:number = 0):Promise<any>{
        var database:Database = open('data')
        if (find == 'all'){
            var data = await database.all(`SELECT * FROM '${table}'`)
            return data.reverse()[count]
        } else {
            var keys = Object.keys(find) , vals = Object.values(find)
            var commands:string[] = [] 
            for (var k in keys){
                commands.push(`${keys[k]} = '${vals[k]}'`)
            }
            var qu:string = commands.join(' AND ')
            var data = await database.all(`SELECT * FROM '${table}' WHERE ${qu}`)
            return data.reverse()[count]
        }
    }
    export async function getOne(table:mainData,find:{[key:string]:string|undefined}|'all'):Promise<any>{
        var database:Database = open('data')
        if (find == 'all'){
            return database.all(`SELECT * FROM '${table}'`)
        } else {
            var keys = Object.keys(find) , vals = Object.values(find)
            var commands:string[] = [] 
            for (var k in keys){
                commands.push(`${keys[k]} = '${vals[k]}'`)
            }
            var qu:string = commands.join(' AND ')
            return database.get(`SELECT * FROM '${table}' WHERE ${qu}`)
        }
    }
    export async function add(table:mainData,insert:{}):Promise<void>{
        var database:Database = open('data')
        var last:any = await getLast(table,'all')
        var addid = last?.id || 0
        var vals = Object.values(insert)
        vals.unshift(addid+1)
        var _vals = vals.map((x)=>`'${x}'`).join(',')
        var keys = Object.keys(insert)
        keys.unshift('id')
        var _keys = keys.join(',')
        var query:string = `INSERT INTO ${table} (${_keys}) VALUES(${_vals})`
        return database.exec(query)
    }
    export async function del(table:mainData,del:{[key:string]:string|undefined}|'all'):Promise<any> {
        var database:Database = open('data')
        if (del == 'all'){
            return database.exec(`DELETE FROM ${table}`)
        } else {
            var keys = Object.keys(del) , vals = Object.values(del)
            var commands:string[] = [] 
            for (var k in keys){
                commands.push(`${keys[k]} = '${vals[k]}'`)
            }
            var qu:string = commands.join(' AND ')
            return database.exec(`DELETE FROM '${table}' WHERE ${qu}`)
        } 
    }
    export async function search(table:mainData,find:{[key:string]:string|undefined}|'all'):Promise<any>{
        var database:Database = open('data')
        if (find == 'all'){
            return database.all(`SELECT * FROM '${table}'`)
        } else {
            var keys = Object.keys(find) , vals = Object.values(find)
            var commands:string[] = [] 
            for (var k in keys){
                commands.push(`${keys[k]} LIKE '%${vals[k]}%'`)
            }
            var qu:string = commands.join(' AND ')
            return database.all(`SELECT * FROM '${table}' WHERE ${qu}`)
            // var qu = Object.entries(find)[0]
            // return database.all(`SELECT * FROM '${table}' WHERE ${qu[0]} LIKE '%${qu[1]}%'`)
        }
    }
    export async function update(table:mainData,find:{[key:string]:string|undefined},updates:{[key:string]:string}):Promise<any>{
        var database:Database = open('data')
        var query:string = `UPDATE ${table} SET `
        var ukeys = Object.keys(updates) , uvals = Object.values(updates)
        var commands:string[] = [] 
        for (var k in ukeys){
            commands.push(`${ukeys[k]} = '${uvals[k]}'`)
        }
        var qu:string = commands.join(',')
        var fin = Object.entries(find)[0]
        return database.exec(`UPDATE ${table} SET ${commands} WHERE ${fin[0]} = '${fin[1]}'`)
    }
    export class Sqlite extends Database {
        constructor(name:string){
            super({filename:`./config/${name}.db`,driver:drive.Database})
        }
        public createTable(tbname:string,table:string[]):Sqlite{
            this.open()
            var template:string = table.map<string>((v,i)=>`'${v}' varchar(255) NULL`).join(',')
            this.exec(`CREATE TABLE '${tbname}' (id int NOT NULL,${template},PRIMARY KEY (id));`)
            return this
        }
        public fetchbyId(id:number){

        }
        public async fetchAll(tbname:string):Promise<any[]>{
            var res = await this.all(`select * from ${tbname}`)
            
            return res
        }
    }
    export class Users {
        public static findUser(){

        }
    }
}

export default Databases