'use strict';

import * as schema from "./templates/schema";

require('dotenv').config();
const { Sequelize } = require( 'sequelize' );
const autoBind      = require( 'auto-bind' );
const jsonMerger = require("json-merger");

//Adapter design patter refers to https://dev.to/carlillo/series/540
export default class MySQLDatabaseDiscovery{

    sequelize;
    Databases = {};
    connectionString:string = null;
    MySQLConfiguration = null;
    foreignKeyReferences = {};

    constructor(MySQLConfiguration) {
        this.sequelize = new Sequelize( MySQLConfiguration.DATABASE,
            MySQLConfiguration.USER,
            MySQLConfiguration.PASSWORD, {
                port:MySQLConfiguration.PORT,
                host: MySQLConfiguration.HOSTNAME,
                dialect: MySQLConfiguration.DIALECT
            },
            {logging:false}
        );

        this.MySQLConfiguration = MySQLConfiguration;
        this.Databases = {};
        autoBind( this );

    }

    //extract the schema includes table names (as key) and their column names (as values)
    extractMySQLSchema = async (dbName) => {
        let extractedSchemas = {};
        let schemaWithDatabaseNames = {};

        let schemaWithDatabaseName = {};
        let extractedSchema = await this.fetchTables(dbName);
        schemaWithDatabaseName[dbName] = extractedSchema;

        //combine all extracted schemas into one schema
        return schemaWithDatabaseName;
    }

    //Get foreign key references details for the table, use when generating graph for generating the reverse relationship
    getForeignKeyReferences = () => {
        return this.foreignKeyReferences;
    }

    //populate databasename as a key in Json format
    fetchDatabases = async () => {

        let EmptyRecord = {};

        try {
            await this.sequelize.authenticate();
            console.log('Connection has been established successfully.');
        } catch( error ) {
            console.error('Unable to connect to the database:', error);
        }

        let dbs = null;
        try {
            dbs = await this.sequelize.query("show databases", { raw : true });
        } catch( error ) {
            throw new Error(`Show Databases Error : ${JSON.stringify(error, null, 8)}`);
        }

        const tempRecord = [];
        dbs[0].forEach(
            dbRecord => {
                //get the value database name based on key Database
                tempRecord.push( dbRecord['Database'] );
            });

        //build the database in Json
        tempRecord.forEach(
            db => {
                //add key to database such as [db] is the key
                //eg. {job_database: { Connection: null, Tables: [] } }
                this.Databases[db] = EmptyRecord;
            });

        return this.Databases;
    };


    //Get the table name and add to the database
    fetchTables = async (databaseName) => {

        let sequelize = new Sequelize(databaseName, this.MySQLConfiguration.USER, this.MySQLConfiguration.PASSWORD, {
            host: this.MySQLConfiguration.HOSTNAME,
            port: this.MySQLConfiguration.PORT,
            dialect: 'mysql',
            logging:  false,
            foreignKeyConstraint: true,
        })

        let tableNames = [];
        let schema = {};

        //getQueryInterface().showAllSchemas() for showing tables including View
        await sequelize.getQueryInterface().showAllTables()
            .then((tables) => {
                //console.log(tables);
                for(let table of tables) {
                    tableNames.push(table);
                }
            }).then( //result => console.log(result)
            ).catch((err) => {
                console.log('showAllSchemas ERROR',err);
            })

        //Get foreign key references details for the table.
        for(let i=0; i<tableNames.length; i++) {
            let tableName = tableNames[i];
            //Get foreign key references details for the table.
            await sequelize.getQueryInterface().getForeignKeyReferencesForTable(tableName)
                .then(foreignKeyRefs => {
                    //get only the tables that have foreign keys
                    if(Array.isArray(foreignKeyRefs) && foreignKeyRefs.length) {
                        //JSON.parse is used to remove raw TextRow and convert to JSON format
                        let fkRefs = JSON.parse(JSON.stringify(foreignKeyRefs));
                        //merges all foreign key references
                        this.foreignKeyReferences[tableName] = fkRefs;
                    }
                })
                .catch((err) => {
                    console.log('Describes Table Error', err);
                })
        }

        //add tables to the database
        //go through the collection, and extract the table name and allows it to add columns as keys
        //build table record
        //Note: make sure to update the Json data one level above
        //do not do this.Databases[databaseName]['Tables'] = ['jobs'] //this will update all the databases
        for(let i=0; i<tableNames.length; i++) {
            let tr = tableNames[i];
            await sequelize.getQueryInterface().describeTable(tr)
                .then((columnDataType) => {
                    schema[tr] = columnDataType;
                })
                .catch((err) => {
                    console.log('Describes Table Error', err);
                })
        }

        return schema;
    }

    extractCustomer = async (MySQLConfiguration, databaseName, tableName, columnNames) => {

        let sequelize = new Sequelize(databaseName, MySQLConfiguration.USER, MySQLConfiguration.PASSWORD, {
            host: MySQLConfiguration.HOSTNAME,
            port: MySQLConfiguration.PORT,
            dialect: 'mysql',
            logging:  false,
            foreignKeyConstraint: true,
        })

        var customer = sequelize.define(tableName, {
                // ProfileID: {
                //     type: Sequelize.INTEGER,
                //     primaryKey: true,
                // },
            },
            {
                tableName: tableName,
                freezeTableName: true,
                timestamps: false,
            });

         return customer.findAll({attributes:columnNames, raw:true})
            //.then(data=> {console.log (data)} );
    }
}

/**************** Main Entry Point ******************************************/

//Undo Comment to allow running from this file
/*
const MySQLConfiguration = {
    HOSTNAME : 'appstralian.top',
    PORT     : '3306',
    ADMIN    : '',
    TEST     : '',
    USER     : 'admin_penta',
    PASSWORD : 'pentaglobal',
    DIALECT  : 'mysql'
};
*/
/*
const MySQLConfiguration = {
    HOSTNAME : 'localhost',
    PORT     : '3306',
    ADMIN    : '',
    TEST     : '',
    USER     : 'root',
    PASSWORD : '',
    DIALECT  : 'mysql'
};


const mySQLDatabaseDiscovery = new MySQLDatabaseDiscovery(MySQLConfiguration);

const dbs = mySQLDatabaseDiscovery.fetchDatabases()
    .then(databases => {
        for (let databaseName of Object.keys(databases)) {
            console.log(databaseName);
            const tables = mySQLDatabaseDiscovery.fetchTables(databaseName)
                .then(extractedSchema => {
                    extractedSchema = schema.insertTemplateToSchema(extractedSchema);
                    console.log(JSON.stringify(extractedSchema,null,4));
                });
        }
    });
*/
/*
(async() => {
    let extractedCustomers = await mySQLDatabaseDiscovery
        .extractCustomer(MySQLConfiguration,'job_database','candidateprofiles', 'ProfileID');
    //console.log(extractedCustomers);

    for(let extractedCustomer of extractedCustomers) {
       console.log(extractedCustomer['ProfileID']);
    }

})();
*/

//module.exports.MySQLDatabase = MySQLDatabase;