//https://github.com/kristiandupont/extract-pg-schema
//https://stackoverflow.com/questions/49775451/how-to-configure-sequelize-with-mssql

import * as schema from "./templates/schema";
const { Sequelize } = require('sequelize');
const mssql = require('mssql')
const jsonMerger = require("json-merger");
const _ = require('lodash');
export default class MSSQLDatabaseDiscovery {

    connectionString: string = null;
    config = {};
    connection = null;
    foreignKeyReferences = {}

    constructor(MSSQLConfiguration) {

        //convert to the recognised configuration by mssql not sequelize
        this.config = {
            "user": MSSQLConfiguration.USER, //default is sa
            "password": MSSQLConfiguration.PASSWORD,
            "server": MSSQLConfiguration.HOSTNAME, // for local machine
            "port": parseInt(MSSQLConfiguration.PORT, 10),
            "database": MSSQLConfiguration.DATABASE, // name of database        
            "options": {
                "encrypt": false
            }
        }
    }

    //Get meta data using mssql lib
    //Get all the metadata from multiple tables in the same database
    extractMSSQLSchema = async (tableNames) => {

        let schemas = []

        for(let i=0; i<tableNames.length; i++) {
            let tableName = tableNames[i];
            //console.log(tableName);
            let extractedMSSQLSchema = await this.extractSchema(tableName)
            //console.log(extractedMSSQLSchema);
            //TODO to handle tablename with spaces such as Order Details
            extractedMSSQLSchema = schema.buildMSSQLSchema(extractedMSSQLSchema,this.config['database'],tableName);
            //console.log(extractedMSSQLSchema);
            schemas.push(extractedMSSQLSchema);
        }

        return schemas;
    }

    //get the database table names
    getTableNames = async() => {

        let tableNames = [];

        try {
            let connection = await mssql.connect(this.config);
            //console.log('Connection has been established successfully.');
            let databaseName = this.config['database'];
            //get the database table names
            let tNames = await new mssql.Request().query(`
                SELECT TABLE_NAME
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='NorthWindDB'
            `);

            for (let i=0; i<tNames.recordset.length; i++ ) {
                let tName = tNames.recordset[i];
                let tableName = tName['TABLE_NAME'];

                tableNames.push(tableName);
            }

            connection.close();
            return tableNames;
        }
        catch (error) {
            console.error('Unable to connect to the database:', error);
        }        

        return null;
    }

    //Get meta data using mssql lib
    extractSchema = async (tableName) => {

        try {
            let connection = await mssql.connect(this.config);
            //console.log('Connection has been established successfully.');

            let result = await new mssql.Request().query(`select * from ${tableName}`);
            let metadata = result.recordset.columns;
            //console.log(metadata);

            connection.close();
            return metadata;

        } catch (error) {
            console.error('Unable to connect to the database:', error);
        }

        return null;
    }

    //Use mssql lib instead of sequalize
    getForeignKeyReferences = async (MSSQLConfiguration) => {
        let config = {
            "user": MSSQLConfiguration.USER, //default is sa
            "password": MSSQLConfiguration.PASSWORD,
            "server": MSSQLConfiguration.HOSTNAME, // for local machine
            "port": parseInt(MSSQLConfiguration.PORT, 10),
            "database": MSSQLConfiguration.DATABASE, // name of database        
            "options": {
                "encrypt": false
            }
        }

        try {
            let connection = await mssql.connect(config);
            console.log('Connection has been established successfully.');

            const result = await new mssql.Request().query(
                `SELECT OBJECT_NAME(f.parent_object_id) ConsTable, 
                    OBJECT_NAME (f.referenced_object_id) refTable,
                    COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
                FROM 
                    sys.foreign_keys AS f 
                INNER JOIN 
                    sys.foreign_key_columns AS fc 
                    ON f.OBJECT_ID = fc.constraint_object_id 
                INNER JOIN 
                    sys.tables t 
                    ON t.OBJECT_ID = fc.referenced_object_id 
                order by 
                ConsTable`);

            connection.close();

            let foreignKeyReferences = result.recordset;

            //create a json with arrays group by the source tablename
            const property = "ConsTable"
            const references = _.groupBy(foreignKeyReferences, property);
            
            return references;

        } catch (error) {
            console.error('Unable to connect to the database:', error);
        }

        return null;
    }

    //Use sequalize lib
    extractCustomer = async (MSSQLConfiguration) => {
        let sequelize = new Sequelize(MSSQLConfiguration.DATABASE, MSSQLConfiguration.USER, MSSQLConfiguration.PASSWORD, {
            host: MSSQLConfiguration.HOSTNAME,
            port: MSSQLConfiguration.PORT,
            dialect: 'mssql',
        })

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

        var customer = sequelize.define(MSSQLConfiguration.TABLE, {
            //Speficy fields as needed
        },
            {
                tableName: MSSQLConfiguration.TABLE,
                timestamps: false,
            });

        customer.schema(MSSQLConfiguration.SCHEMA)

        const customerRecords = await customer.findAll({ attributes: MSSQLConfiguration.COLUMN, raw: true })

        //const [results, metadata] = await sequelize.query("SELECT * FROM Employees");
        //console.log(results)

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


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

//Undo Comment to allow running from this file

const MSSQLConfiguration = {
    "DBMS": "MSSQL",
    "HOSTNAME": "localhost",
    "PORT": "1433",
    "USER": "sa",
    "PASSWORD": "PentaTech01",
    "DATABASE": "NorthWindDB",
};
async function run() {
    try {

        const mssqlDatabaseDiscovery = new MSSQLDatabaseDiscovery(MSSQLConfiguration);

        const tableNames = await mssqlDatabaseDiscovery.getTableNames();

        //Create a node by geting the combined extracedMSSQLSchema from each table 
        let extractedMSSQLSchema = await mssqlDatabaseDiscovery.extractMSSQLSchema(tableNames);
        console.log(JSON.stringify(extractedMSSQLSchema,null,3));

        let foreignKeys = await mssqlDatabaseDiscovery.getForeignKeyReferences(MSSQLConfiguration)
        //console.log(foreignKeys);

    } catch (error) {
        console.log(error);
    }
};

//run();