const jsonMerger = require("json-merger");

//https://data-flair.training/blogs/mongodb-data-types/
//https://blog.back4app.com/migrating-from-sql-to-nosql/
//https://www.tutorialspoint.com/mongodb/mongodb_datatype.htm
//https://docs.mongodb.com/manual/reference/bson-types/

export const datatypeMysql2MongoDb = {
    "CHAR": "String",
    "VARCHAR": "String",
    "BINARY": "String",
    "VARBINARY": "String",
    "TINYBLOB": "String",
    "TINYTEXT": "String",
    "TEXT": "String",
    "BLOB": "String",
    "MEDIUMTEXT": "String",
    "MEDIUMBLOB": "String",
    "LONGTEXT": "String",
    "ENUM": "String",
    "SET": "String",

    "BIT": "Boolean",
    "TINYINT": "Number",
    "BOOL": "Boolean",
    "BOOLEAN": "Boolean",
    "SMALLINT": "Number",
    "MEDIUMINT": "Number",
    "INT": "Number",
    "INTEGER": "Number",
    "BIGINT": "Number",
    "FLOAT": "Number",
    "DOUBLE": "Number",
    "DECIMAL": "Number",
    "DEC": "Number",

    "DATE": "Date",
    "DATETIME": "Date",
    "TIMESTAMP": "Timestamp",
    "YEAR": "Date",

    "NULL": "Null",
    "JSON": "JSON"
}

//https://www.oracletutorial.com/oracle-basics/oracle-data-types/
export const datatypeOracle2MongoDb = {
    "VARCHAR2": "String",
    "NVARCHAR2": "String",
    "NUMBER": "Number",
    "LONG": "Number",
    "DATE": "Date",
    "BINARY_FLOAT": "Binary data",
    "BINARY_DOUBLE": "Binary data",
    "RAW": "Binary data",
    "LONG RAW": "Binary data",
    "ROWID": "Binary data",
    "CHAR": "String",
    "NCHAR": "String",
    "CLOB": "Object",
    "NCLOB": "Object",
    "BLOB": "Object",
    "BFILE": "Object",
    "TIMESTAMP": "Timestamp",
    "INTERVAL YEAR": "Number",
    "INTERVAL DAY": "Number",
    "UROWID": "String"
}


export const datatypeMSSQL2MongoDb = {
    "float": "Number",
    "real": "Number",
    "bigint": "Number",
    "int": "Number",
    "smallint": "Number",
    "tinyint": "Number",
    "bit": "Boolean",
    "decimal": "Number",
    "numeric": "Number",
    "money": "Number",
    "smallmoney": "Number",
    "char": "String",
    "varchar": "String",
    "text": "String",
    "nchar": "String",
    "nvarchar": "String",
    "nvartext": "String",
    "ntext": "String",
    "binary": "Binary data",
    "varbinary": "Binary data",
    "image": "Binary data",
    "datetime": "Date",
    "smalldatetime": "Date",
    "date": "Date",
    "time": "Date",
    "datetimeoffset": "Date",
    "datetime2": "Date",
}

export const datatypePostgres2MongoDb = {
    "bit": "Boolean",
    "smallint": "Number",
    "integer": "Number",
    "int": "Number", 
    "bigint": "Number",
    "decimal": "Number",
    "numeric": "Number",
    "real": "Number",
    "double precision": "Boolean",
    "smallserial": "Number",
    "serial": "Number",
    "bigserial": "Number",
    "money": "Number",
    "bool": "Boolean",
    "boolean": "Boolean",
    "char varying": "String",
    "character varying": "String",
    "varbit": "String",
    "varchar": "String",
    "character": "String",
    "char": "String",
    "text": "String",
    "bytea": "Binary data",
    "date": "Date",
    "TIMESTAMPT": "Date",
    "TIMESTAMPTZ": "Date",
    "timestamp without time zone": "Date",
    "time without time zone": "Date",
    "time with time zone": "Date",
    "interval": "Date",
    "ARRAY": "Object",
    "USER-DEFINED": "Object"
}

export const datatypeDB2ToMongoDb = {
    "BINARY": "Boolean",
    "CHAR() FOR BIT DATA": "Boolean",	
    "CHAR FOR BIT DATA": "Boolean",	
    "BIGINT": "Number",
    "BLOB": "Object",
    "CLOB": "Object",
    "DBCLOB":"Object",
    "GRAPHIC":"Object", 
    "DECFLOAT": "Number", 
    "DECIMAL": "Number",
    "DOUBLE": "Number",
    "FLOAT": "Number",
    "INTEGER": "Number",
    "VARBINARY": "Boolean",
    "NUMERIC": "Number",
    "REAL": "Number",
    "SMALLINT": "Number",
    "money": "Number",
    "bool": "Boolean",
    "boolean": "Boolean",
    "CHAR": "String",
    "LONG VARCHAR": "String",
    "LONG VARCHAR FOR BIT DATA": "Object",
    "LONG VARGRAPHIC": "Object",
    "ROWID": "String",
    "VARCHAR()": "String",
    "VARCHAR": "String",
    "VARGRAPHIC": "String",
    "DATE": "Date",
    "TIME": "Date",
    "TIMESTAMP": "Date",
    "TIMESTAMP WITH TIMEZONE": "Date",
    "XML": "Object",
}

//the flat version
export const private_schema = {
    "txHash": "",
    "private": true,
    "visible": true,
    "accessOwner": true,
    "accessGroup": false,
    "accessWorld": false,
    "encrypted": false,
    "vendorIn": [],
    "vendorOut": [],
}


//Insert schema template to the column meta data
export const insertTemplateToSchema = (extractedSchema) => {
    let privacySchema = private_schema;

    let dbNames = Object.keys(extractedSchema);
    for (let dbName of dbNames) {
        let tableNames = Object.keys(extractedSchema[dbName]);
        for (let tableName of tableNames) {
            //console.log(tableNames[i]);
            let columnNames = Object.keys(extractedSchema[dbName][tableName]);
            //console.log(columnNames);
            for (let columnName of columnNames) {
                let temp = extractedSchema[dbName][tableName][columnName];
                temp = jsonMerger.mergeObjects([temp, privacySchema]);
                extractedSchema[dbName][tableName][columnName] = temp;
            }
        }
    }

    //console.log(JSON.stringify(extractedSchema,null,4));
    return extractedSchema;
}

//Filter the raw data and restructure the schema to include databasename, classname, and insert template
export const buildPostgresSchema = (extractedSchema) => {
    let privacySchema = private_schema;
    let databaseName
    let schemaName
    let tableName
    let attributes = {}
    let field = {}
    let fields = {}
    let node = {}
    let nodes = []

    for (let extSchema of extractedSchema) {
        //console.log(schema)
        databaseName = extSchema.informationSchemaValue.table_catalog
        schemaName = extSchema.informationSchemaValue.table_schema
        tableName = extSchema.informationSchemaValue.table_name

        //console.log(tableName, schemaName, "\n")
        let columns = extSchema.columns
        for (let column of columns) {
            let columnName = column.name
            let isPrimaryKey = column.isPrimaryKey
            let type = column.informationSchemaValue.data_type
            //console.log("\t", columnName, isPrimaryKey, type)

            attributes = {
                "schemaName": tableName,
                "fieldName": columnName,
                "primaryKey": isPrimaryKey,
                "type": type,
            }
            attributes = jsonMerger.mergeObjects([attributes, privacySchema])

            //console.log(JSON.stringify(attributes,null,3))
            field[columnName] = attributes
            attributes = {} //necessary to reset it to empty
            fields = jsonMerger.mergeObjects([fields, field]);
            field = {}
        }

        node['databaseName'] = databaseName
        node['className'] = tableName
        //console.log(fields)
        node['fields'] = fields
        nodes.push(node)
        node = {}
        fields = {}
        //console.log(JSON.stringify(nodes,null,3))

    }

    //console.log(JSON.stringify(nodes,null,3))
    return nodes;
}

//Filter the raw data and restructure the schema to include databasename, classname, and insert template
export const buildMSSQLSchema = (extractedSchema, databaseName, tableName) => {
    let privacySchema = private_schema;

    let fields = {}
    let node = {}

    node['databaseName'] = databaseName

    const columnNames = Object.keys(extractedSchema);
    for (let i = 0; i < columnNames.length; i++) {
        node['className'] = tableName
        fields[columnNames[i]] = {
            "schemaName": tableName,
            "fieldName": extractedSchema[columnNames[i]]['name'],
            "primaryKey": extractedSchema[columnNames[i]]['identity'],
            "type": extractedSchema[columnNames[i]]['type']['declaration'],
        }
        fields[columnNames[i]] = jsonMerger.mergeObjects([fields[columnNames[i]], privacySchema])
    }

    node['fields'] = fields;
    //console.log(node)
    return node;
}

//Filter the raw data and restructure the schema to include databasename, classname, and insert template
export const buildDB2Schema = (extractedSchema, dbName) => {
    let privacySchema = private_schema;
    let attributes = {};
    let field = {};
    let fields = {};
    let node = {};
    let nodes = [];

    //console.log(extractedSchema);
    let tables = Object.keys(extractedSchema);
    for (let table in extractedSchema) {

        const properties = extractedSchema[table]
        for(let j=0;j<properties.length;j++) {
            let tableName = table;
            let columnName = properties[j]['COLUMN_NAME'];
            let isPrimaryKey = properties[j]['IS_PRIMARY'] === 1 ? true : false;
            let type = properties[j]['DATA_TYPE'];
            //console.log("\t", columnName, isPrimaryKey, type)
    
            attributes = {
                "schemaName": tableName,
                "fieldName": columnName,
                "primaryKey": isPrimaryKey,
                "type": type,
            }
            attributes = jsonMerger.mergeObjects([attributes, privacySchema])
    
            //console.log(JSON.stringify(attributes,null,3))
            field[columnName] = attributes
            attributes = {} //necessary to reset it to empty
            fields = jsonMerger.mergeObjects([fields, field]);
            field = {}
        }
        
        node['databaseName'] = dbName;
        node['className'] = table;
        //console.log(fields)
        node['fields'] = fields
        nodes.push(node)
        node = {}
        fields = {}        
    }

    //console.log(JSON.stringify(nodes,null,3))
    return nodes;
}


//Insert schema template to the column meta data
export const translateDatatype = (fromDBMS, type) => {
    let translatedType;
    if (fromDBMS.toUpperCase() === "MYSQL") {
        translatedType = datatypeMysql2MongoDb[type];
    }
    else if (fromDBMS.toUpperCase() === "ORACLE") {
        translatedType = datatypeOracle2MongoDb[type];
    }
    else if (fromDBMS.toUpperCase() === "MSSQL") {
        translatedType = datatypeMSSQL2MongoDb[type];
    }
    else if (fromDBMS.toUpperCase() === "POSTGRES") {
        translatedType = datatypePostgres2MongoDb[type];
    }
    else if (fromDBMS.toUpperCase() === "DB2") {
        translatedType = datatypeDB2ToMongoDb[type];
    }

    return translatedType;
}