var mapObj = obj => Object.keys(obj);

var Databases = {
    exists: name => Databases.hasOwnProperty(name),
    get: name => Databases.exists(name)
        ? Databases[name]
        : (console.error(`Database with name ${name} doens't exists`), null)
};

var CurrentDatabase = null;

var createDatabase = () => ({
    tables: {},
    tableExists(name){
        return !!this.tables[name]
    },
    getTable(name){
        return this.tables[name]
    }
});

var Users = {
    'root@localhost': {
        password: '589757',
        access: [],
    }
};


var sqlCommand = fn => fn ? fn : ({  });

var ALTER = sqlCommand();

ALTER.USER = name => ({
    IDENTIFIED: {
        BY: nenPassword => {
            if (Users.hasOwnProperty(name)){
                Users[name].password = nenPassword;
            }else{
                console.error(`User ${name} doesn't exists`);
            }
        }
    }
})

var SHOW = sqlCommand();

SHOW.DATABASES = () => {mapObj(Databases).map((v, k) => v !== `exists` && v !== `get` && console.log(v))}
SHOW.TABLES = () => {
    if(CurrentDatabase) {
        Object.keys(CurrentDatabase.tables).map(n => console.log(n))
    }else{
        console.error(`Cannot show tables: no database in use`);
    }
}

var CREATE  = sqlCommand();

var mergeObjects = (p = {}, c) => ({...p, ...c});

CREATE.DATABASE = name =>
    Databases.exists(name)
        ? console.error(`Database ${name} aleardy exists`)
        : Databases[name] = createDatabase();

buildTransactionSpec = (key, constraints, tableColumnsRest, tableSpecErrors, primaryKeyField, table) => {
    if (key === `PRIMARY_KEY`){
        if (tableColumnsRest.includes(constraints)) {
            primaryKeyField.is = constraints
        }else{
            tableSpecErrors.push(x => `Cannot set primary key: field ${constraints} doesn't exisits on ${name}`);
        }
        return null
    }else{
        var sortConstraints = constraints.sort(({precedence: x}, {precedence: y}) => x > y);
        const actualConstraints = sortConstraints.map(x => x.fn);
        return {
            description: sortConstraints.map(x => x.name).reduce((p, c) => `${p} ${c}`, ``).trim(),
            transaction(transaction){
                return actualConstraints.reduce((p, f) => p === null ? p : f(p, table, key), transaction);
            }
        }
    }
}

var createTableFromSpec = (name, tableSpec) => {
    var primaryKeyField = {};
    var tableSpecErrors = [];
    var table = {
        name,
        entries: [],
        lastInsertId: 0,
    };
    var tableColumnsRest = Object.keys(tableSpec);
    var transactionSpec = Object.entries(tableSpec).map(([key, constraints]) => ({
        [key]: buildTransactionSpec(key, constraints, tableColumnsRest, tableSpecErrors, primaryKeyField, table)
    })).filter(x => x[Object.keys(x)[0]]).reduce(mergeObjects);
    table.primaryKeyField = primaryKeyField.is;
    table.transactionSpec = transactionSpec;
    return tableSpecErrors.length ? (tableSpecErrors.map(console.error), null) : table;
}

CREATE.TABLE = (name, tableSpec) => CurrentDatabase
    ? CurrentDatabase.tables[name] = createTableFromSpec(name, tableSpec)
    : console.error(`Cannot create table, no database in use`)

var USE = sqlCommand(database => CurrentDatabase = Databases.get(database));

var sqlTypeSpec = (name, fn, precedence) => ({fn, precedence, name});

var INT = sqlTypeSpec(`INT`, value => {
    if (isNaN(value)) {
        console.error(`Type assertion failed: ${value} is not a number`);
        return null;
    }else{
        return parseInt(value);
    }
}, 2)

var UNSIGNED = sqlTypeSpec(`UNSIGNED`, value =>  value < 0 ? value * -1 : value, 3)

var NOT_NULL = sqlTypeSpec(`NOT_NULL`, (value, table, field) => value === undefined
    ? (console.error(`Type assertion failed: ${field} cannot be null`), null)
    : value
, 0);

var AUTO_INCREMENT = sqlTypeSpec(`AUTO_INCREMENT`, (value, table) => table.lastInsertId++ , -1)

var VARCHAR = count => sqlTypeSpec(`VARCHAR(${count})`, (value = '') =>
    value.length > count
        ? (console.error(`Type assertion failed ${value} is greather than ${count}.`), null)
        : value
, 1)

var isDate = date => !isNaN(Date.parse(date));

var DATE = sqlTypeSpec(`DATE`, value =>
    isDate(value)
    ? Date.parse(value)
    : (console.error(`Type assertion failed ${value} not an valid date.`), null)
, 1)


DESCRIBE = name => CurrentDatabase
    ? CurrentDatabase.tableExists(name)
        ? Object.entries(CurrentDatabase.getTable(name).transactionSpec).map(x => `${x[0]}: ${x[1].description} \n`).reduce((p, c) => p + c, `${name}: \n`)
        : console.error(`Table ${name} doesn't exists`)
    : console.error(`Cannot describe ${name}: no database in use`)

var safeGetTable = (tableName, action = `execute action`) =>
    CurrentDatabase
        ? CurrentDatabase.tableExists(tableName)
            ? CurrentDatabase.getTable(tableName)
            : (console.error(`Table ${tableName} doesn't exists`), null)
        : (console.error(`Cannot ${action}: no database in use`), null)


var concatStr = (p = ``, c) => `${p}${c}`
INSERT = sqlCommand()
INSERT.INTO = (name) => (...rowSpec) => ({
    VALUES(...arrs){
        var table = safeGetTable(name, `insert`);
        var tableColumns = Object.keys(table.transactionSpec);
        var notFoundColumns = rowSpec.filter(column => !tableColumns.includes(column));
        if (notFoundColumns.length) {
            console.error(notFoundColumns.map(x => `Cannot insert in ${name}: column ${x} doesn't exisit\n`).reduce(concatStr))
            return null
        }
        if (!table){
            return null
        }
        if(!arrs.every(arr => arr.length === rowSpec.length)){
            console.error(`Row insert failed: Malformatted inserts`)
            return null;
        }
        var insertSpec = arrs.map(dataSpec => dataSpec.map((name, idx) => ({[rowSpec[idx]]: name})).reduce(mergeObjects));

        var newTables = insertSpec.map(inserts =>
            Object.entries(table.transactionSpec).map(([key, value]) => ({ [key]: value.transaction(inserts[key])})).reduce(mergeObjects)
        )

        table.entries.push(...newTables)
    }
})



replaceAll = str => ({
    by: map => Object.entries(map).map(([regex, replacer]) => str => str.replace(new RegExp(regex, 'gi'), replacer)).reduce((p, c) => c(p), str),
})

compileJSFinder = (keys, str) => new Function('searcher', 'return ' +replaceAll(str).by({
    'OR': '||',
    'AND': '&&',
    'NOT': '!',
    '=': '===',
    ...keys.map(k => ({[k]: `searcher.${k}`})).reduce(mergeObjects),
}));

filterColumns = (entries, columns) => entries.map(row => Object.entries(row).filter(([k, v]) => columns.includes(k)).map(([k, v]) => ({[k]: v})).reduce(mergeObjects))


orderBy = (tableList) => ({
    ORDER_BY: (name) => ({
        ASC: () => tableList.sort((a, b) => a[name] > b[name]),
        DESC: () => tableList.sort((a, b) => a[name] < b[name]),
        DO: () => tableList.sort((a, b) => a[name] > b[name]),
    }),
    DO: () => tableList,
})

SELECT = sqlCommand((...columns) => ({
    FROM(name){
        return {
            DO(){
                var table = safeGetTable(name, `select`);
                if (!table) return null
                if (columns[0] === `*`) return table.entries;
                return orderBy(filterColumns(table.entries, columns))
            },
            WHERE(whereSpec){
                var table = safeGetTable(name, `select`);
                if (!table) return null
                var finder;
                var findColumns = columns[0] === '*' ? Object.keys(table.transactionSpec) : columns;
                if (typeof whereSpec === `function`){
                    finder = whereSpec;
                }else{
                    finder = compileJSFinder(findColumns, whereSpec);
                }
                var foundTables = table.entries.filter(finder);
                return  orderBy(filterColumns(foundTables, findColumns));
            }
        }
    }
}))


DELETE = sqlCommand((...columns) => ({
    FROM(name){
        return {
            DO(){
                var table = safeGetTable(name, `select`);
                if (!table) return null
                table.entries = [];
                return true;
            },
            WHERE(whereSpec){
                var table = safeGetTable(name, `select`);
                if (!table) return null
                var finder;
                var findColumns = Object.keys(table.transactionSpec);
                if (typeof whereSpec === `function`){
                    finder = whereSpec;
                }else{
                    finder = compileJSFinder(findColumns, whereSpec);
                }
                table.entries = table.entries.filter(i => !finder(i));
                return true
            }
        }
    }
}))
ALTER = sqlCommand();
ALTER.TABLE = (tname) => ({
    ADD(name, transactionSpec){
        var table = safeGetTable(tname, `alter table`);
        if (!table) return null

        var errors = [];
        var newPrimaryKey = {};
        var newSpec = buildTransactionSpec(name, transactionSpec, Object.keys(table.transactionSpec), errors, newPrimaryKey, table);
        if(errors.length){
            errors.map(console.error)
            return null
        }
        if(table.primaryKeyField !== undefined && newPrimaryKey.is){
            console.error(`Cannot reset primary key`);
            return null
        }
        table.transactionSpec[name] = newSpec;
        table.entries = table.entries.map(e => ({[name]: undefined, ...e}));
        return true
    },
    REMOVE(...names){
        var table = safeGetTable(tname, `alter table`);
        if (!table) return null
        names.forEach(name => delete table.transactionSpec[name])
        table.entries = table.entries.map(e => {
            names.forEach(name => delete e[name])
            return e;
        });
        return true
    },
})

updateRow = (row, updateSpec) => (updateSpec.map(([key, value]) => row[key] = value), row)

UPDATE = sqlCommand(tableNae => ({
    SET: (...specs) => ({
        WHERE(whereSpec){
            var updateSpec = specs.map(spec => spec.split(`=`).map(part => part.trim()));
            var table = safeGetTable(tableNae, `select`);
            if (!table) return null
            var finder;
            var findColumns = Object.keys(table.transactionSpec);
            if (typeof whereSpec === `function`){
                finder = whereSpec;
            }else{
                finder = compileJSFinder(findColumns, whereSpec);
            }
            table.entries = table.entries.map(row => finder(row) ? updateRow(row, updateSpec) : row );
            return true
        }
    })
}))