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

const oracledb = require('oracledb');
const jsonMerger = require("json-merger");
const autoBind = require( 'auto-bind' );

oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;

export default class OracleDatabaseDiscovery {

    OracleConfiguration = null;
    db = null;
    connection = null;
    schema = { }

    constructor(OracleConfiguration) {
        this.OracleConfiguration = OracleConfiguration;
        autoBind( this );
    }

    extractOracleSchema =  async (owner) => {
        let extractedSchemas = {};
        let schemaWithDatabaseNames = {};

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

            //combine all extracted schemas into one schema
            //schemaWithDatabaseNames = jsonMerger.mergeObjects([schemaWithDatabaseNames, schemaWithDatabaseName]);

        return schemaWithDatabaseName;
    }

    fetchTables =  async (owner) => {
        try {
            this.OracleConfiguration.OWNER = owner;

            //setup the connection
            this.connection = await oracledb.getConnection({
                user: this.OracleConfiguration.USER,
                password: this.OracleConfiguration.PASSWORD,
                connectString: `${this.OracleConfiguration.HOSTNAME}:${this.OracleConfiguration.PORT}/${this.OracleConfiguration.SERVICE_NAME}`
            });

            const result = await this.connection.execute(
                `SELECT table_name FROM all_tables where owner= :owner`, [owner],
                { extendedMetaData: true, resultSet: true }
            );

            let arr_table = []
            const rs= result.resultSet
            let row;
            while ((row = await rs.getRow())) {
                //each row return something like { TABLE_NAME: 'REGIONS' }
                //get the value of the TABLE_NAME
                arr_table.push(row.TABLE_NAME)
            }
            await rs.close()

            for(let i=0 ; i<arr_table.length ; i++) {
                let result = await this.connection.execute(
                    `SELECT * FROM ` + arr_table[i]+` WHERE rownum = :id`,
                    [1],  // get only one row, bind value for :id
                    { extendedMetaData: true },
                );

                //console.log(result);

                //get the primary key and later we insert it into the metadata
                let primaryKey = await this.getPrimaryKey(this.connection, arr_table[i]);
                primaryKey = primaryKey.rows[0].COLUMN_NAME;
                //console.log(arr_table[i] + "\t" + primaryKey);

                let table = {};
                let fieldName = "";
                //add primary key to metadata
                for(let i=0; i<result.metaData.length; i++) {
                    result.metaData[i]['primaryKey']  = primaryKey;
                    fieldName = result.metaData[i]['name'];
                    table[fieldName] = result.metaData[i]
                    //console.log(table);
                }

                //add the key which is the table name with its value
                //this.schema[arr_table[i]] = result.metaData
                this.schema[arr_table[i]] = table;
            }

            return this.schema;

        } catch (err) {
            console.error(err);
        } finally {
            if (this.connection) {
                try {
                    await this.connection.close();
                } catch (err) {
                    console.error(err);
                }
            }
        }
    }


    extractCustomer = async (OracleConfiguration, serviceName, tableName, columnNames) => {

        OracleConfiguration.SERVICE_NAME = serviceName;

        //setup the connection
        let connection = await oracledb.getConnection({
            user: OracleConfiguration.USER,
            password: OracleConfiguration.PASSWORD,
            connectString: `${OracleConfiguration.HOSTNAME}:${this.OracleConfiguration.PORT}/${OracleConfiguration.SERVICE_NAME}`
        });


        return new Promise((resolve, reject) => {
            const customer = connection.execute(
                `SELECT ${columnNames} FROM ${tableName}`, [],
                function(err, result) {
                    if (err) {
                       return reject(err.message);
                    }
                    resolve(result.rows);
                });
        });

    }

    //Get the primary key of the tablename
    getPrimaryKey = async (connection, tableName) => {

        let query = `SELECT column_name FROM all_cons_columns WHERE constraint_name = (SELECT constraint_name FROM all_constraints  
                      WHERE UPPER(table_name) = UPPER( '${tableName}' ) AND CONSTRAINT_TYPE = 'P')`
        let result = await connection.execute(query);

        return result;
    }

    getForeignKeyReferences =  async(OracleConfiguration) => {

        let connection;

        try {

            connection = await oracledb.getConnection( {
                user: OracleConfiguration.USER,
                password: OracleConfiguration.PASSWORD,
                connectString: `${OracleConfiguration.HOSTNAME}:${this.OracleConfiguration.PORT}/${OracleConfiguration.SERVICE_NAME}`
            });


            let owner = OracleConfiguration.OWNER;

            //get all the tables with the foreign keys and their referential keys
            let metadata = await this.queryForeignKeys(connection,owner);
            return metadata;

        } catch (err) {
            console.error(err);
        } finally {
            if (connection) {
                try {
                    await connection.close();
                } catch (err) {
                    console.error(err);
                }
            }
        }
    }

    //Gets relationship details between tables
    //refer to https://dba.stackexchange.com/questions/11047/how-to-retrieve-foreign-key-constraints-data
    queryForeignKeys = async(connection, owner) => {
        let result = await connection.execute(
            `SELECT
                    CONS.CONSTRAINT_NAME,
                        CONS.TABLE_NAME,
                        COLS.COLUMN_NAME,
                        CONS.R_CONSTRAINT_NAME,
                        CONS_R.TABLE_NAME R_TABLE_NAME,
                        COLS_R.COLUMN_NAME R_COLUMN_NAME
                        
                    FROM USER_CONSTRAINTS CONS
                    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
                    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
                    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
            
                    -- returns only foreign key constraints
                    WHERE CONS.CONSTRAINT_TYPE = 'R' and CONS.OWNER = 'HR'
                    ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME`
        );
        return result;
    }

}

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


//Undo Comment to allow running from this file
/*
const OracleConfiguration = {
    HOSTNAME: 'localhost',
    PORT     : '1521',
    USER: 'hr',
    PASSWORD: 'hr',
    SERVICE_NAME: 'orclpdb1',
    OWNER: ''
};

const oracleDatabaseDiscovery = new OracleDatabaseDiscovery(OracleConfiguration);


const owner = 'HR';
(async () => {
    //includes inserting private schema
    const extractedOracleSchema = await oracleDatabaseDiscovery.extractOracleSchema(owner);
    console.log(JSON.stringify(extractedOracleSchema,null,4));
})();
*/

/*
//cutomer id hash
(async() => {
    let extractedCustomers:any = [];
    extractedCustomers = await oracleDatabaseDiscovery
        .extractCustomer(OracleConfiguration,'XEPDB1','DEPARTMENTS', 'DEPARTMENT_ID');

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

})();
*/

/*
//get the foreignkeys
(async() => {
    let foreignKeys = await oracleDatabaseDiscovery.getForeignKeyReferences().then(response => {
        console.log(response)
    });
})();
 */