//https://github.com/kristiandupont/extract-pg-schema

import * as schema from "./templates/schema";
const { extractSchemas } = require('extract-pg-schema');
const { Sequelize } = require( 'sequelize' );
const jsonMerger = require("json-merger");
export default class PostgreSQLDatabaseDiscovery {

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

    constructor(PostgreSQLConfiguration) {
        
        //convert to the recognised configuration by the extract-pg-lib
        this.PostgreSQLConfiguration['host'] = PostgreSQLConfiguration.HOSTNAME
        this.PostgreSQLConfiguration['port'] = PostgreSQLConfiguration.PORT 
        this.PostgreSQLConfiguration['database'] = PostgreSQLConfiguration.DATABASE
        this.PostgreSQLConfiguration['schema'] = PostgreSQLConfiguration.SCHEMA
        this.PostgreSQLConfiguration['user'] = PostgreSQLConfiguration.USER
        this.PostgreSQLConfiguration['password'] = PostgreSQLConfiguration.PASSWORD
    }

    extractPostgreSQLSchema = async () => {

        this.connection =  this.PostgreSQLConfiguration;
        let schemas;


        this.connection.database = this.PostgreSQLConfiguration['database']
        let schemaName = this.PostgreSQLConfiguration['schema']
        let schema = await extractSchemas(this.connection);
        //schema = schema[schemaName]
        let tables = schema[schemaName].tables
        //tables = JSON.stringify(tables,null,3)
        //TODO make sure can combine multiple database and schema
        schemas = tables;
        return schemas;
    }

    //Get foreign key references details for the table, use when generating graph for generating the reverse relationship
    getForeignKeyReferences = (tables) => {

        let foreignKeyReferences = {}
        let tableNames = {}
        let foreignKey = {}
        let foreignKeys = []

        for(let i=0; i<tables.length; i++) {
            let table = tables[i]
            let columns = table.columns

            for(let j=0; j<columns.length; j++)
            {
                foreignKey =  columns[j].reference

                if(foreignKey !== null) {
                    foreignKeys.push(foreignKey)
                    //console.log(foreignKey)
                }
            }

            tableNames[table.name] = foreignKeys            
            foreignKeys = []
        }

        //console.log(JSON.stringify(tableNames, null, 3))
        foreignKeyReferences = tableNames
        return foreignKeyReferences
    }

    extractCustomer = async (PostgresConfiguration) => {
        let sequelize = new Sequelize(PostgresConfiguration.DATABASE, PostgresConfiguration.USER, PostgresConfiguration.PASSWORD, {
            host: PostgresConfiguration.HOSTNAME,
            port: PostgresConfiguration.PORT,
            dialect: 'postgres',
            logging:  false,
            foreignKeyConstraint: true,
        })

        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(PostgresConfiguration.TABLE, {
                //Speficy fields as needed
                // ProfileID: {
                //     type: Sequelize.INTEGER,
                //     primaryKey: true,
                // },
            },
            {        
                tableName: PostgresConfiguration.TABLE,
                timestamps: false,
            });

        customer.schema(PostgresConfiguration.SCHEMA)

        const customerRecords =  await customer.findAll({attributes:PostgresConfiguration.COLUMN, raw:true})
        //console.log(JSON.stringify(customerRecords,null,3))
        return customerRecords

    }
}


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

//Undo Comment to allow running from this file

// const PostgreSQLConfiguration = {
//     host: 'localhost',
//     database: ['Adventureworks'],
//     schema:['humanresources'],
//     user: 'jiskandar',
//     password: 'pwd'
// };


// const PostgreSQLConfiguration = {
//     host: 'localhost',
//     database: ['dvdrental'],
//     schema:['public'],
//     user: 'jiskandar',
//     password: 'pwd'
// };

const PostgreSQLConfiguration = {
    "DBMS"    : "Postgres",
    "HOSTNAME": "localhost",
    "PORT"    : "5432",
    "USER"    : "jiskandar",
    "PASSWORD": "pwd",
    "DATABASE": "dvdrental",
    "SCHEMA"  : "public"
};



// const connection = {
//     host: 'localhost',
//     database: 'Adventureworks',
//     user: 'jiskandar',
//     password: 'pwd'
// };


async function run() { 
    try {
        const postgreSQLDatabaseDiscovery = new PostgreSQLDatabaseDiscovery(PostgreSQLConfiguration);
        let extractedPostgreSQLSchema = await postgreSQLDatabaseDiscovery.extractPostgreSQLSchema();
        //console.log(JSON.stringify(extractedPostgreSQLSchema,null,3));

        let foreignKeys = await postgreSQLDatabaseDiscovery.getForeignKeyReferences(extractedPostgreSQLSchema)
        //console.log(foreignKeys);

        //console.log(foreignKeyReferences)
        
        extractedPostgreSQLSchema = schema.buildPostgresSchema(extractedPostgreSQLSchema);
        console.log(JSON.stringify(extractedPostgreSQLSchema, null , 3));

        //in this example, the schema is humanresources
        //console.log(JSON.stringify(extractedPostgreSQLSchema.humanresources,null,3))
        //console.log(JSON.stringify(extractedPostgreSQLSchema.humanresources.tables[0].columns[0].informationSchemaValue,null,3))
        //console.log(JSON.stringify(extractedPostgreSQLSchema,null,4));
    } catch (error) {
        console.log(error);
    }
};

//run();