'use strict';

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

require('dotenv').config();
const ibmdb = require('ibm_db');
const autoBind = require('auto-bind');
const jsonMerger = require("json-merger");
const _ = require('lodash')


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

    connection: any;
    Databases = {};
    connectionString: string = null;
    DB2Configuration = null;
    foreignKeyReferences = {};

    constructor(DB2Configuration) {

        this.DB2Configuration = DB2Configuration;
        const connection = this.connect(DB2Configuration);
    }

    connect = async (DB2Configuration) => {
        const connStr = `DATABASE=${DB2Configuration.DATABASE};HOSTNAME=${DB2Configuration.HOSTNAME}; \
                       UID=${DB2Configuration.USER};PWD=${DB2Configuration.PASSWORD};PORT=${DB2Configuration.PORT}; \
                       PROTOCOL=TCPIP`;

        // const connector = async (err, conn) => {
        //     if (err) return console.log(err);

        //     this.connection = conn;
        //     //console.log(this.connection);
        //     console.log("call connect -> DB2 connection established")
        // };

        var option = { connectTimeout: 50, systemNaming: true };// Connection Timeout after 50 seconds.
        this.connection = ibmdb.openSync(connStr, option);

        console.log("DB2 connection established")
    }

    //extract the schema includes table names (as key) and their column names (as values)
    extractDB2Schema = async () => {

        let extractedSchemas = {};
        let schemaWithDatabaseNames = {};
        let schemaWithTableNames = {};

        try {
            const tables = await this.fetchTables(this.DB2Configuration.SCHEMA);

            for (let i = 0; i < tables.length; i++) {
                const sql_statement = `select 
                                        -- tabname as table_name,                        
                                        colno as position,
                                        colname as column_name,
                                        typename as data_type,
                                        length,
                                        scale,
                                        default,
                                        remarks as description,
                                        case when keyseq>0 then 1 else 0 end as is_primary, 
                                        case when nulls='Y' then 1 else 0 end as nullable,
                                        case when identity ='Y' then 1 else 0 end as is_identity,
                                        case when generated ='' then 0 else 1 end as  is_computed,
                                        text as computed_formula
                                from syscat.columns
                                where tabname = '${tables[i]}'           
                                      and tabschema = '${this.DB2Configuration.SCHEMA}'
                                order by colno;`;
                schemaWithTableNames[tables[i]] = await this.connection.querySync(sql_statement);
            }
            schemaWithDatabaseNames = schemaWithTableNames;
            //console.log(schemaWithDatabaseNames);
        }
        catch (error) {
            console.log(error);
        }

        return schemaWithDatabaseNames;
    }

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

        const sql_statement = `select   
                                    ref.tabschema concat '.' concat ref.tabname as foreign_table, 
                                    '>-' as rel, 
                                    ref.reftabschema concat '.' concat ref.reftabname as primary_table,
                                    key.colseq  as no,  
                                    key.colname  as fk_column_name,
                                    ' = ' as join,
                                    keypk.colname as pk_column_name,
                                    ref.constname as fk_constraint_name
                            from syscat.references ref
                            left outer join syscat.keycoluse key on
                                    key.tabschema = ref.tabschema and key.tabname = ref.tabname
                                    and key.constname = ref.constname
                            left outer join syscat.keycoluse keypk on
                                    keypk.tabschema = ref.reftabschema
                                    and keypk.tabname = ref.reftabname 
                                    and keypk.constname = ref.refkeyname 
                                    and keypk.colseq=key.colseq`;

        const foreignKeyReferences = await this.connection.querySync(sql_statement)
        return foreignKeyReferences;
    }


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

        const sql_statement = `select tabname as table_name
                            from syscat.tables
                            where tabschema = '${schema}'
                            and type = 'T'
                            order by tabname`;

        const tables = await this.connection.querySync(sql_statement);

        var _tables = tables.map(function (table) {
            return table.TABLE_NAME;
        });
        return _tables;
    }

    extractCustomer = async (DB2Configuration) => {

        let columns = DB2Configuration.COLUMN;
        columns = columns.toString();
        let sql_statement = `select ${columns} from EMPLOYEE`;

        const customerRecords = await this.connection.querySync(sql_statement);
        return customerRecords;
    }
}

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

//Undo Comment to allow running from this file

const DB2Configuration = {
    HOSTNAME: 'localhost',
    PORT: '50000',
    USER: 'db2inst1',
    PASSWORD: 'pentatech',
    DATABASE: 'SAMPLE',
    SCHEMA: 'DB2INST1'
};


async function run() {

    try {
        const db2DatabaseDiscovery = new DB2DatabaseDiscovery(DB2Configuration);

        let extractedSchema = await db2DatabaseDiscovery.extractDB2Schema();
        //console.log(extractedSchema);

        const foreignKeys = await db2DatabaseDiscovery.getForeignKeyReferences("SAMPLE");
        //console.log(foreignKeys);

        extractedSchema = schema.buildDB2Schema(extractedSchema, DB2Configuration.DATABASE);
        console.log(extractedSchema);

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

};

//run();


//module.exports.DB2Database = DB2Database;