<?php

use Google\Cloud\Vision\Annotation\Web;

require_once (PATH_MODELS . "base/MasterBase.php") ;
require_once (PATH_TABLES . "admin/TableList.php") ;

class DatabaseTableListClass {

    public $AllTable = array(

        //ADMIN
        "UserTable",
        "UserGroupTable",
        "UserResetPasswordHistoryTable",
        "UserPasswordHistoryTable",
        "IndustryTable",
        "BusinessSectorTable",
        "BusinessNatureTable",
        "SoftwareTable",
        "AuditLogTable",
        "DepartmentGroupTable",

        //company profile
        "CompanyProfileTable",
        "CorporateGiftTable",
        "CorporateGiftDetailTable",
        "CorporateGiftHeaderImageTable",
        "CorporateGiftDeliveryTable",
        "CorporateGiftRecipientTable",
        "CorporateGiftHistoryTable",

        //customer
        "CustomerTable",
        "CustomerContactTable",
        "CustomerBranchTable",
        "CustomerSoftwareTable",
        "CustomerPolicyTable",
        "CustomerScheduleTable",
        "ExportCustomerHistoryTable",

        //prospect
        "ProspectTable",
        "ProspectContactTable",
        "ProspectConversationTable",
        "ProspectDeliveryTable",
        "ProspectDocumentTable",
        "PersonContactTable",
        
        "DestinationTable",

        //suspect
        "SuspectTable",
        "SuspectContactTable",

        //campaign
        "CampaignProfileTable",
        "CampaignTable",
        "CampaignDetailTable",
        "CampaignDeliveryTable",
        "CampaignRecipientTable",
        "CampaignHistoryTable",
        "CampaignHeaderImageTable",
    );
}

class UpdateDatabaseClass extends MasterBase {
    private $clsGlobalTableList;
    private $clsLocalTableList;
    private $clsTable;
    private $clsTableDetail;
    private $clsTableDefault;

	function __construct($db) {
		$this->db = $db ;
		$this->tbl = AuditLogTable::C_TABLE ;
		$this->fldid = AuditLogTable::C_ID ;
		$this->fldorg = AuditLogTable::C_ORG_ID ;
		$this->flddesc = AuditLogTable::C_DESC ;

        $this->clsGlobalTableList = (new DatabaseTableListClass())->AllTable;
	}
	function __destruct() {
	}

    function processColumn($rowsTable, $columnList, $columnDetailList, $key, $column,$bu_prefix=""){

        //Log::write($key . " - " . $column);
        $detail = $columnDetailList[$key];
        $found = false;
        $rowFound = null;
        foreach($rowsTable as $row){
            if($row['COLUMN_NAME'] == $column){
                $found = true;
                $rowFound = $row;
            }
        }
        $detailArr = explode("|", $detail);

        if($found){
            $needToUpdateDataType = false;
            $needToUpdateDefault = false;
            $needToUpdateNullable = false;
            $needToUpdateSize = false;
            //Log::write($rowFound);
            //Log::write($detailArr);
            if($detailArr[0] != 'identity'){
                //check datatype
                if($rowFound['DATA_TYPE'] != $detailArr[0]){
                    $needToUpdateDataType = true;
                    if($rowFound['IS_NULLABLE'] != $detailArr[2]){
                        $needToUpdateNullable = true;
                    }
                } else {
                    //check nullable
                    if($rowFound['IS_NULLABLE'] != $detailArr[2]){
                        $needToUpdateNullable = true;
                    } else {

                        //check default
                        if($rowFound['COLUMN_DEFAULT'] != "(" . $detailArr[3] . ")"){
                            $needToUpdateDefault = true;
                        } else {

                            //check size
                            if($rowFound['DATA_TYPE'] == 'nvarchar' && intval($rowFound['CHARACTER_MAXIMUM_LENGTH']) != intval($detailArr[1])){
                                $needToUpdateSize = true;
                            } else if($rowFound['DATA_TYPE'] == 'decimal' && $rowFound['NUMERIC_PRECISION'] . "," . $rowFound['NUMERIC_SCALE'] != $detailArr[1]){
                                $needToUpdateSize = true;
                            }  else {
                                //COLUMN IS FINE
                            }
                        }
                    }
                }
            } 
            // else {
            //         $sqlIdentity = "SELECT IDENT_CURRENT('".($bu_prefix. $rowFound['TABLE_NAME'])."') AS result";
            //         Log::write($sqlIdentity);
            //         $result = $this->db->getTable($sqlIdentity);

            //         $result = $result[0]['result'];
            //         Log::write("result");
            //         Log::write($result);

            //         if(!is_null($result) && count($result)>0){
            //             // $needToUpdateDataType = true;
            //         }else{
            //             $sqlDropTable = "DROP TABLE " . $bu_prefix. $rowFound['TABLE_NAME'] . "  ";

            //             //Log::write($sqlDropTable);
            //             //$this->db->executeSql($sqlDropTable);
            //             //Log::write("Recreate ".$bu_prefix. $rowFound['TABLE_NAME']);
            //             //$this->processAddTable($columnList, $columnDetailList);
            //         }

            // }

            if($needToUpdateDataType || $needToUpdateNullable || $needToUpdateSize || $needToUpdateDefault){
                if($needToUpdateNullable){
                    if($detailArr[2] == "NO"){
                        if(!$needToUpdateDataType){
                            $sqlUpdate = "UPDATE " . $bu_prefix. $columnList["C_TABLE"] . " SET " . $column . " = " . $detailArr[3] . " " .
                                            " WHERE " . $column . " is null ";
                            Log::write($sqlUpdate);
                            $this->db->executeSql($sqlUpdate);
                        }
                        else {
                            if(($rowFound['DATA_TYPE'] == 'text' || $rowFound['DATA_TYPE'] == 'ntext' || $rowFound['DATA_TYPE'] == 'nvarchar' || $rowFound['DATA_TYPE'] == 'varchar') &&
                                ($detailArr[0] == 'int' || $detailArr[0] == 'decimal')){

                                $sqlUpdate = "UPDATE " .$bu_prefix. $columnList["C_TABLE"] . " SET " . $column . " = '0' " .
                                                " WHERE " . $column . " is null ";
                                Log::write($sqlUpdate);
                                $this->db->executeSql($sqlUpdate);
                            } else if(($rowFound['DATA_TYPE'] == 'int' || $rowFound['DATA_TYPE'] == 'decimal') &&
                                    ($detailArr[0] == 'text' || $detailArr[0] == 'ntext' || $detailArr[0] == 'nvarchar' || $detailArr[0] == 'varchar')){

                                $sqlUpdate = "UPDATE " .$bu_prefix. $columnList["C_TABLE"] . " SET " . $column . " = 0 " .
                                                " WHERE " . $column . " is null ";
                                Log::write($sqlUpdate);
                                $this->db->executeSql($sqlUpdate);
                            }
                        }
                    }
                } else if($needToUpdateDataType){
                    //dropping constraint
                    $sqlUpdateDefault = "select * from sys.columns where object_id = Object_ID(N'" .$bu_prefix. $columnList["C_TABLE"] . "') and name = '" . $column . "' ";
                    $rowDef = $this->db->getTable($sqlUpdateDefault);

                    if(!is_null($rowDef) && count($rowDef) > 0){
                        $objectId = $rowDef[0]['default_object_id'];

                        $sqlUpdateDefault = "select * from sys.default_constraints where OBJECT_NAME(parent_object_id) = '" .$bu_prefix. $columnList["C_TABLE"] . "' AND object_id = " . $objectId . " ";
                        $rowDef = $this->db->getTable($sqlUpdateDefault);

                        if(!is_null($rowDef) && count($rowDef) > 0){
                            $name = $rowDef[0]['name'];

                            $sqlUpdateDefault = " ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " DROP CONSTRAINT " . $name . " ";
                            $this->db->executeSql($sqlUpdateDefault);
                        }
                    }
                }

                if($rowFound['DATA_TYPE'] == 'text' && $detailArr[0] == 'ntext'){
                    $sqlTemp = "EXEC sys.sp_rename '" .$bu_prefix. $columnList["C_TABLE"] . "." . $column . "' , '" . $column . "_OLD', 'COLUMN'" ;
                    $this->db->executeSql($sqlTemp);

                    $sqlTemp = "ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " ADD " . $column . " " .
                                $detailArr[0] . " " .
                                (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " " .
                                "DEFAULT " . $detailArr[3];
                    $this->db->executeSql($sqlTemp);

                    $sqlTemp = "UPDATE " .$bu_prefix. $columnList["C_TABLE"] . " set " . $column . " = " . $column . "_OLD";
                    $this->db->executeSql($sqlTemp);


                    $sqlTemp = "ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " DROP COLUMN " . $column . "_OLD";
                    $this->db->executeSql($sqlTemp);
                }


                $sqlUpdate = "ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " ALTER COLUMN " . $column . " " .
                                $detailArr[0] . " " .
                                (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " ";
                Log::write($rowFound['DATA_TYPE'] . " - " . $sqlUpdate);
                $this->db->executeSql($sqlUpdate);

                if($needToUpdateDefault){
                    //dropping constraint
                    $sqlUpdateDefault = "select * from sys.columns where object_id = Object_ID(N'" .$bu_prefix. $columnList["C_TABLE"] . "') and name = '" . $column . "' ";
                    $rowDef = $this->db->getTable($sqlUpdateDefault);

                    if(!is_null($rowDef) && count($rowDef) > 0){
                        $objectId = $rowDef[0]['default_object_id'];

                        $sqlUpdateDefault = "select * from sys.default_constraints where OBJECT_NAME(parent_object_id) = '" .$bu_prefix. $columnList["C_TABLE"] . "' AND object_id = " . $objectId . " ";
                        $rowDef = $this->db->getTable($sqlUpdateDefault);

                        if(!is_null($rowDef) && count($rowDef) > 0){
                            $name = $rowDef[0]['name'];

                            $sqlUpdateDefault = " ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " DROP CONSTRAINT " . $name . " ";
                            $this->db->executeSql($sqlUpdateDefault);
                        }
                    }

                    $sqlUpdateDefault = "ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " ADD DEFAULT " . $detailArr[3] . " FOR " . $column;
                    Log::write("DEFAULT - " . $sqlUpdateDefault);
                    $this->db->executeSql($sqlUpdateDefault);
                }
            }
        } else {
            $data_type = $detailArr[0];
            if($detailArr[0] == 'identity'){
                $data_type = " INT IDENTITY (1,1) ";
                $sqlAdd = "ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " ADD " . $column . " " .
                                $data_type . " " .
                                (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " ";
            }else{
                $sqlAdd = "ALTER TABLE " .$bu_prefix. $columnList["C_TABLE"] . " ADD " . $column . " " .
                                $detailArr[0] . " " .
                                (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " " .
                                "DEFAULT " . $detailArr[3] ;
        }
            Log::write($sqlAdd);
            $this->db->executeSql($sqlAdd);
        }
    }

    function processAddTable($columnList, $columnDetailList, $bu_prefix =""){

        $strColumn = "";
        $strTable = "";
        $strPK = "";
        foreach($columnList as $key => $column){
            if ($key == "C_TABLE"){
                $strTable = $column;
            } else if ($key == "C_PK") {
                $strPK = $column;
            } else if ($key == "C_CLEAR_TRANSACTION") {
                //$strPK = $column;
            }else if ($key == "C_TABLE_TYPE") {
                //$strPK = $column;
            } else {
                if($strColumn != "") $strColumn .= ", ";

                $detail = $columnDetailList[$key];
                $detailArr = explode("|", $detail);

                if($detailArr[0] == 'identity'){
                    $strColumn .= " " . $column . " " .
                                        "INT IDENTITY (1,1) ";

                } else {
                    $strColumn .= " " . $column . " " .
                                        $detailArr[0] . " " .
                                        (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                        ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " " .
                                        "DEFAULT " . $detailArr[3];
                }
            }
        }

        $sqlAddTable = "CREATE TABLE " . $bu_prefix .$strTable . " (" .
                                        $strColumn .
                                        ($strPK != "" ? ", CONSTRAINT PK_" . $bu_prefix . $strTable . " PRIMARY KEY(" . $strPK . ")" : "") . ") ";

        Log::write($sqlAddTable);
        $this->db->executeSql($sqlAddTable);
    }
    function processAddTableQuery($columnList, $columnDetailList, $bu_prefix =""){

        $strColumn = "";
        $strTable = "";
        $strPK = "";
        foreach($columnList as $key => $column){
            if ($key == "C_TABLE"){
                $strTable = $column;
            } else if ($key == "C_PK") {
                $strPK = $column;
            } else if ($key == "C_CLEAR_TRANSACTION") {
                //$strPK = $column;
            }else if ($key == "C_TABLE_TYPE") {
                //$strPK = $column;
            } else {
                if($strColumn != "") $strColumn .= ", ";

                $detail = $columnDetailList[$key];
                $detailArr = explode("|", $detail);

                if($detailArr[0] == 'identity'){
                    $strColumn .= " " . $column . " " .
                                        "INT IDENTITY (1,1) ";

                } else {
                    $strColumn .= " " . $column . " " .
                                        $detailArr[0] . " " .
                                        (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                        ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " " .
                                        "DEFAULT " . $detailArr[3];
                }
            }
        }
        $sqlAddTable = "DROP TABLE " . $bu_prefix .$strTable ." ; \n";
        $sqlAddTable .= "CREATE TABLE " . $bu_prefix .$strTable . " (" .
                                        $strColumn .
                                        ($strPK != "" ? ", CONSTRAINT PK_" . $bu_prefix . $strTable . " PRIMARY KEY(" . $strPK . ")" : "") . ") ; \n";

        return $sqlAddTable;
    }
    function processAddTableRecordQuery($columnList, $columnDetailList, $tableName ){


        $strTable = "";
        $strPK = "";

        $sql = "SELECT * FROM " . $tableName ;
        $rows = $this->db->getTable($sql);

        $sqlAddTable = '';
        $sqlAddTablePK = "" ;
        $sqlHavePK = false;
        if (!is_null(  $rows) && count(  $rows)>0) {


            foreach ($rows as $key => $value) {
                $strColumn = "";
                $strColumnValue = "";
                $sep='';
                foreach($columnList as $key => $column){
                    if ($key == "C_TABLE"){
                        $strTable = $column;
                    } else if ($key == "C_PK") {
                        $strPK = $column;
                        $sqlHavePK = true ;
                    } else if ($key == "C_CLEAR_TRANSACTION") {
                        //$strPK = $column;
                    }else if ($key == "C_TABLE_TYPE") {
                        //$strPK = $column;
                    } else {
                        if($strColumn != "") $strColumn .= ", ";

                        $strColumn .= " " . $column . " " ;

                        $strColumnValue .=$sep."'".htmlspecialchars( $value[$column],ENT_QUOTES)."'" ;
                        $sep = ',';
                    }
                }
                $sqlAddTable .= "INSERT INTO " . $tableName . "(" . $strColumn . ") values (" . $strColumnValue . ") ; \n";
            }
            if(  $sqlHavePK ){
                $sqlAddTablePK .= "SET IDENTITY_INSERT ".$tableName ." ON \n";
                $sqlAddTablePK .= $sqlAddTable ;
                $sqlAddTablePK .= "SET IDENTITY_INSERT ".$tableName ." OFF \n";
            }else{
                $sqlAddTablePK .= $sqlAddTable ;
            }


        }

        // $sqlAddTable = "CREATE TABLE " . $bu_prefix .$strTable . " (" .
        //                                 $strColumn .
        //                                 ($strPK != "" ? ", CONSTRAINT PK_" . $bu_prefix . $strTable . " PRIMARY KEY(" . $strPK . ")" : "") . ") ";


        return $sqlAddTablePK;
    }
    function processDefaultRecord($tableName, $records){
        if(count($records) > 0){
            foreach($records as $record){
                $strColumnName = "";
                $strColumnValue = "";
                $strFilter = "";
                foreach($record as $columnName => $columnValue){
                    if($columnName != 'M'){
                        if($strColumnName != "") $strColumnName .= ", ";
                        $strColumnName .= " " . $columnName;
                        if($strColumnValue != "") $strColumnValue .= ", ";
                        $strColumnValue .= " " . $columnValue;

                        if($strFilter != "") $strFilter .= " AND ";
                        $strFilter .= " " . $columnName . " = " . $columnValue;
                    }
                }

                $needToInsert = false;
                if($record['M'] == 'Y'){
                    $sql = "SELECT * FROM " . $tableName . " WHERE " . $strFilter;
                    $rows = $this->db->getTable($sql);

                    $needToInsert = is_null($rows) || count($rows) == 0;
                } else {
                    $sql = "SELECT * FROM " . $tableName;
                    $rows = $this->db->getTable($sql);
                    $needToInsert = is_null($rows) || count($rows) == 0;
                }

                if($needToInsert){
                    $sqlInsert = "INSERT INTO " . $tableName . "(" . $strColumnName . ") values (" . $strColumnValue . ")";

                    Log::write($sqlInsert);
                    $this->db->executeSql($sqlInsert);
                }
            }
        }
    }

    function processDefaultRecordQuery($tableName, $records){
        $sqlInsert = "";
        if(count($records) > 0){
            foreach($records as $record){
                $strColumnName = "";
                $strColumnValue = "";
                $strFilter = "";
                foreach($record as $columnName => $columnValue){
                    if($columnName != 'M'){
                        if($strColumnName != "") $strColumnName .= ", ";
                        $strColumnName .= " " . $columnName;
                        if($strColumnValue != "") $strColumnValue .= ", ";
                        $strColumnValue .= " " . $columnValue;

                        if($strFilter != "") $strFilter .= " AND ";
                        $strFilter .= " " . $columnName . " = " . $columnValue;
                    }
                }

                $needToInsert = false;
                if($record['M'] == 'Y'){
                    $sql = "SELECT * FROM " . $tableName . " WHERE " . $strFilter;
                    $rows = $this->db->getTable($sql);

                    $needToInsert = is_null($rows) || count($rows) == 0;
                } else {
                    $sql = "SELECT * FROM " . $tableName;
                    $rows = $this->db->getTable($sql);
                    $needToInsert = is_null($rows) || count($rows) == 0;
                }

                if($needToInsert){
                    $sqlInsert = "INSERT INTO " . $tableName . "(" . $strColumnName . ") values (" . $strColumnValue . ") ; \n";

                    //Log::write($sqlInsert);
                    //$this->db->executeSql($sqlInsert);
                }
            }
        }
        return $sqlInsert ;
    }
    
    function checkWhetherTableIsGlobal($tableName){
        $sql_check_table = "SELECT * FROM information_schema.tables WHERE table_name = '" . $tableName . "'";
        $result = $this->db->getTable($sql_check_table);
        if(count($result) > 0){
            return true;
        }else{
            return false;
        }
    }
    
    function upadateIDtoNull($parentTable, $parentColumn, $childTable, $childColumn, $data="NULL"){
        $sql = "UPDATE " . $parentTable . " SET " . $parentColumn . " = " . $data . 
                " WHERE " . $parentColumn . " NOT IN (SELECT " .  $childColumn . " FROM " . $childTable . ")";
        // log::write($sql);
        $this->db->executeSql($sql);        
    }

    function processUpdateGlobalDatabase(){
        $this->db->open();
        $result = "";
        try {
            $this->clsGlobalTableList = (new DatabaseTableListClass())->AllTable;
            foreach($this->clsGlobalTableList as $tableName){
                $this->clsTable = new ReflectionClass($tableName);
                $this->clsTableDetail = new ReflectionClass($tableName . "Detail");
                $columnList = $this->clsTable->getConstants();
                $columnDetailList = $this->clsTableDetail->getConstants();

                $sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" .$columnList["C_TABLE"] . "'";

                $rows = $this->db->getTable($sql);
                // Log::write($rows);

                if(!is_null($rows) && count($rows)){
                    // Log::write($columnList);
                    foreach($columnList as $key => $column){
                        if ($key != "C_TABLE" && $key != "C_PK"  && $key != "C_CLEAR_TRANSACTION" ){
                            $this->processColumn($rows, $columnList, $columnDetailList, $key, $column);
                        }
                    }
                } else {
                    $this->processAddTable($columnList, $columnDetailList);
                }

                $realTableName = $columnList["C_TABLE"];

                if(class_exists($tableName . "Default")){
                    $this->clsTableDefault = new ReflectionClass($tableName . "Default");
                    $records = $this->clsTableDefault->getConstants()['record'];

                    $this->processDefaultRecord($realTableName, $records);
                }
            }
        } catch (Exception $e) {
            $result = $e->getMessage();
        }
        $this->db->close();
        return $result;
    }

    function processBackupGlobalDatabase(){
        $this->db->open();
        $result = "";
        $sql_query = "";
        try {
            $this->clsGlobalTableList = (new DatabaseTableListClass())->AllTable;
            foreach($this->clsGlobalTableList as $tableName){
                $this->clsTable = new ReflectionClass($tableName);
                $this->clsTableDetail = new ReflectionClass($tableName . "Detail");
                $columnList = $this->clsTable->getConstants();
                $columnDetailList = $this->clsTableDetail->getConstants();

                $sql_query .= $this->processAddTableQuery($columnList, $columnDetailList);

                $realTableName = $columnList["C_TABLE"];

                if(class_exists($tableName . "Default")){
                    $this->clsTableDefault = new ReflectionClass($tableName . "Default");
                    $records = $this->clsTableDefault->getConstants()['record'];

                    $sql_query .= $this->processDefaultRecordQuery($realTableName, $records);
                }

                $sql_query .= $this->processAddTableRecordQuery($columnList, $columnDetailList,$realTableName);
            }


        } catch (Exception $e) {
            $result = $e->getMessage();
        }
        $this->db->close();
        return $sql_query;
    }
}
