<?php

error_reporting(E_ERROR);

//include all DAO files
require_once('../models/sql/Connection.class.php');
require_once('../models/sql/ConnectionFactory.class.php');
require_once('../models/sql/ConnectionProperty.class.php');
require_once('../models/sql/QueryExecutor.class.php');
require_once('../models/sql/Transaction.class.php');
require_once('../models/sql/SqlQuery.class.php');
require_once('../models/core/ArrayList.class.php');
require_once('../models/dao/DAOFactory.class.php');

//Programsetting
require_once('../models/dao/ProgramsettingsDAO.class.php');
require_once('../models/dto/Programsetting.class.php');
require_once('../models/mysql/ProgramsettingsMySqlDAO.class.php');
require_once('../models/mysql/ext/ProgramsettingsMySqlExtDAO.class.php');
/////////////////////product////////////////////////
require_once('../models/dao/ProductDAO.class.php');
require_once('../models/dto/Product.class.php');
require_once('../models/mysql/ProductMySqlDAO.class.php');
require_once('../models/mysql/ext/ProductMySqlExtDAO.class.php');

//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();

//
$ProductEX = new ProductMySqlExtDAO();

$notExcuted = "";
$oneCommand = "";

$noOfsuccess = 0;
$noOfDuplicateColumnName = 0;
$noOfExcutedWithError = 0;
$noOfUnknownColumn = 0;
$laterSql = "";
$allCommands = "";
#################################################
//check and use the condition that suite this action
$dbhost = ConnectionProperty::getHost();
$dbuser = ConnectionProperty::getUser();
$dbpassword = ConnectionProperty::getPassword();
$dbname = ConnectionProperty::getDatabase();
if ($_SESSION['dbname'] != "" && isset($_SESSION['dbname'])) {
    $dbname = $_SESSION['dbname'];
}

$handle = fopen("../__db/syncStructure.sql", "r");
echo "<span style='color:blue'>note : colums with type that has definition like decimal(15,2) NOT handeled only decimal handled .call developer</span><br/>";
echo "<span style='color:yellowgreen'> read file done start executing... </span>";

if ($handle) {
    $time_start = microtime(true);
    while (($line = fgets($handle)) !== false) {
        // process the line read.
        $firstTowChars = substr($line, 0, 2);
        if ($firstTowChars == "/*" || $firstTowChars == "##" || $firstTowChars == "//" || $firstTowChars == "--") {

        } else {
            $line = trim($line);
            if (!empty($line)) {
                $lastChars = substr($line, -1);
                ##change , in comments it is used to separate colums
                $line = explode("COMMENT", $line);
                $explodedLine_Count = count($line) - 1;
                if ($explodedLine_Count > 0) {
                    $lastChars_2 = substr($line[$explodedLine_Count], -1);
                    $line[$explodedLine_Count] = str_replace(",", "-", $line[$explodedLine_Count]);
                    if ($lastChars_2 == ",") {
                        $line[$explodedLine_Count] = rtrim($line[$explodedLine_Count], "-");
                        $line[$explodedLine_Count].=",";
                    }
                }
                $line = implode("COMMENT", $line);
                ##
                $line = replace_first_after_word(",", "-", "decimal", $line);
                //if line is key replace `,` with `-` as we use , to know colums later in prepareSync()
                if (strpos($line, 'PRIMARY KEY') === 0 || strpos($line, 'UNIQUE KEY') === 0 ||
                        strpos($line, 'FOREIGN KEY') === 0 || strpos($line, 'KEY') === 0) {

                    $line = str_replace("`,`", "`-`", $line);
                }
                $oneCommand.=$line;

                //echo $line . "<br/>";
                if ($lastChars == ";") {
                    ##--new-----------------
                    prepareSync($oneCommand);
                    $oneCommand = "";
                }
            }
        }
    }

    if (!empty($allCommands)) {
        $ProductEX->updateBulk($allCommands);
//        $command = "mysql --user=$db_user --password=$db_password --host=$host $dbname < $mysqlImportFilename ";
//        $mysqlImportFilename = "..\db_backups\\" . $_SESSION['dbname'] . "_syncy_temp.sql";
//        $myfile = fopen($mysqlImportFilename, "a+") or die("Unable to open file!");
//        fwrite($myfile, $allCommands);
//        fclose($myfile);
    }

    ##reset auto increament
    //    $sql = explode(";;", $laterSql);
    //    $lastItem = count($sql) - 1;
    //    unset($sql[$lastItem]); //it is always empty
    //    foreach ($sql as $autoIncrementReseterSql) {
    //        runCommand($dbhost, $dbuser, $dbpassword, $dbname, $autoIncrementReseterSql);
    //    }
    if (!empty($laterSql)) {
        $ProductEX->updateBulk($laterSql);
    }
    //hide syncstructure icon from header
    $programsettingsdata = $ProgramsettingDAO->load(1);
    $programsettingsdata->syncstructure = 1;
    //oldsync   hash next line
    $ProgramsettingDAO->update($programsettingsdata);
    //draw header again to feel the syncstructure show hide
    $fh = fopen('../temp__cashedheader/cashedheader_' . $_SESSION['dbname'] . '_' . $_SESSION['userid'] . '.html', 'w');
    fclose($fh);
    $time_end = microtime(true);
    echo 'execution time in seconds: ' . round((microtime(true) - $time_start), 2) . ' seconds';
    ##end
    fclose($handle);
    echo "<span style='color:green'> DONE. </span><br/>";
    print_r("<pre>");
    echo "<div style='border:1px solid green;margin-right:60%'><span style='color:green'> no of queries excuted successfully : " . $noOfsuccess . " </span><br/>";
    echo "<span style='color:#f57c00'> no of queries excuted with Duplicate Column Name error : " . $noOfDuplicateColumnName . " </span><br/>";
    echo "<span style='color:#f57c00'> no of queries excuted with Unknown Column error : " . $noOfUnknownColumn . " </span><br/>";
    echo "<span style='color:red'> no of queries gave errors : " . $noOfExcutedWithError . " </span><br/></div>";
    print_r("</pre>");
} else {
    // error opening the file.
}

###############################################################
//new way to save all sql to run it all at once

function runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
    global $allCommands;
    $allCommands.= $oneCommand . ";";
}

/* function runCommand_old_queryByQuery($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
  global $noOfsuccess;
  global $noOfDuplicateColumnName;
  global $noOfUnknownColumn;
  global $noOfExcutedWithError;
  // Create connection
  $conn = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);

  // Check connection
  if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
  }
  $sql = $oneCommand;

  if ($conn->query($sql) === TRUE) {
  //echo "success";
  $noOfsuccess++;
  } else {
  //echo "Error: " . $conn->error . "<br/>";
  $errorMessage = $conn->error;
  if (stripos($errorMessage, "Duplicate column name") !== false) {
  //now  it is dublicate column doenot matter
  $noOfDuplicateColumnName++;
  } elseif (stripos($errorMessage, "Unknown column") !== false) {
  $noOfUnknownColumn++;
  } elseif (stripos($errorMessage, "Multiple primary key defined") !== false) {

  } else {
  $noOfExcutedWithError++;
  //show error
  print_r("<pre>");
  print_r($sql . "<br/><span style='color:red'>Error: " . $errorMessage . "</sapn><hr>");
  print_r("</pre>");
  }
  }
  } */

function prepareSync($oneCommand) {

    global $dbhost;
    global $dbuser;
    global $dbpassword;
    global $dbname;
    global $laterSql;
    if (strpos($oneCommand, 'CREATE TABLE IF NOT EXISTS') !== false) {
        $firstExplode = explode("CREATE TABLE IF NOT EXISTS", $oneCommand);
        $secondExplode = explode("(", $firstExplode[1]);
        $tablename = trim(str_replace("`", "", $secondExplode[0]));


        $sqlCheckTable = 'SELECT * FROM information_schema.`TABLES` WHERE `TABLE_SCHEMA` = "' . $dbname . '" and`TABLE_NAME` = "' . $tablename . '"';
        $result = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckTable);
        if ($result->num_rows == "1") {
            //this condition is to skip creat table that come with "view"
            if (strpos($tablename, 'view') === false) {
                //tableexist
                ##remove first part of sql like => CREATE TABLE IF NOT EXISTS `xxx` (
                $firstExplode = explode('(', $oneCommand); // split all parts
                unset($firstExplode[0]);
                $firstExplode = implode($firstExplode, '(');
                ##remove second part of sql like => ) ENGINE=InnoDB AUTO_INCREMENT=280 DEFAULT CHARSET=latin1;
                $secondExplode = explode(')', $firstExplode); // split all parts
                $secondExplodeCount = count($secondExplode) - 1;
                unset($secondExplode[$secondExplodeCount]);
                $secondExplode = implode($secondExplode, ')');

                ##get colums
                $colums = explode(",", $secondExplode);
                //print_r($secondExplode);
                foreach ($colums as $oneColumn) {
                    $oneColumn = replace_first_after_word("-", ",", "decimal", $oneColumn);

                    $colData = explode("`", $oneColumn);
                    $columName = $colData[1];
                    $columProp = $colData[2];
                    //if key do no thing it do exist in first table insert
                    if (strpos($oneColumn, 'PRIMARY KEY') === 0) {
                        $sqlCheckColumn = 'SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and INDEX_NAME = "PRIMARY";';
                        $result = runCommand3($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
//                        if (!empty($result)) {
//                            //there is primary key remove it first
//                            runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename MODIFY $columName INT NOT NULL;");
//                            runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename DROP PRIMARY KEY;");
//                        }
                        if (empty($result)) {
                            //there is no pk add primary key
                            //run command is okey but not working give db some times and run it in later sql
                            //runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename MODIFY $columName INT NOT NULL PRIMARY KEY AUTO_INCREMENT;");
                            $laterSql .= "ALTER TABLE $tablename MODIFY $columName INT NOT NULL PRIMARY KEY AUTO_INCREMENT;;";
                        }
                    } else if (strpos($oneColumn, 'UNIQUE KEY') === 0) {
                        $sqlCheckColumn = 'SELECT count(*) FROM information_schema.STATISTICS WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and INDEX_NAME = "' . $columName . '";';
                        $result = runCommand3($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
//                        if (!empty($result)) {
//                            //there is unique key with this name remove it first
//                            runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename DROP INDEX $columName;");
//                        }
                        if (empty($result)) {
                            $oneColumn = str_replace('`-`', '`,`', $oneColumn);
                            //add uniqu key
                            // run command is okey but not working give db some times and run it in later sql
                            //runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename ADD $oneColumn;");
                            $laterSql .= "ALTER TABLE $tablename ADD $oneColumn;;";
                        }
                    } else if (strpos($oneColumn, 'FOREIGN KEY') === 0) {
                        //later
                    } else if (strpos($oneColumn, 'KEY') === 0) {
                        $sqlCheckColumn = 'SELECT count(*) FROM information_schema.STATISTICS WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and INDEX_NAME = "' . $columName . '";';
                        $result = runCommand3($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
                        if (empty($result)) {
                            $oneColumn = str_replace('`-`', '`,`', $oneColumn);
                            $oneColumn = str_replace('INDEX', 'KEY', $oneColumn);
                            //add key
                            // run command is okey but not working give db some times and run it in later sql
                            //runCommand($dbhost, $dbuser, $dbpassword, $dbname, "ALTER TABLE $tablename ADD $oneColumn;");
                            $laterSql .= "ALTER TABLE $tablename ADD $oneColumn ;;";
                        }
                    } else {

                        if (!empty($columName)) {
                            $sqlCheckColumn = 'SELECT * FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and `COLUMN_NAME`="' . $columName . '"';

                            $result = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);

                            if ($result->num_rows == "1") {
                                //column exist
                                $oneColumn = str_replace('AUTO_INCREMENT', '', $oneColumn);
                                $sqlCol = ' ALTER TABLE ' . $tablename . ' CHANGE `' . $columName . '` ' . $oneColumn . '';
                            } else {
                                //new column
                                $sqlCol = 'ALTER TABLE ' . $tablename . ' ADD ' . $oneColumn . '';
                                if ($columName == "clientStoreIds") {
                                    $laterSql .= 'UPDATE `client` SET `clientStoreIds` = "-10";;';
                                }
                            }
                            runCommand($dbhost, $dbuser, $dbpassword, $dbname, $sqlCol);
                        }
                    }
                }
            }
        } else {
            //new table
            $oneCommand = replace_first_after_word("-", ",", "decimal", $oneCommand);
            runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand);
            $laterSql .= 'ALTER TABLE ' . $tablename . ' auto_increment = 1;;';
            //runCommand($dbhost, $dbuser, $dbpassword, $dbname, $resetAutoIncrement);
        }
    } elseif (strpos($oneCommand, 'SQL SECURITY DEFINER VIEW') !== false) {
        //this is view
        $viewSelectAs = explode("SQL SECURITY DEFINER VIEW", $oneCommand);
        $oneCommand = "CREATE OR REPLACE ALGORITHM = MERGE VIEW" . $viewSelectAs[1];
        //runCommand($dbhost, $dbuser, $dbpassword, $dbname, $dropViewSQL);
        runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand);
    } else {
        if (strpos($oneCommand, 'CREATE VIEW') === 0) {
            $oneCommand = str_replace('CREATE VIEW', 'CREATE OR REPLACE ALGORITHM = MERGE VIEW', $oneCommand);
        }
        runCommand($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand);
    }
}

//get statistics
function runCommand2($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
    // Create connection
    $conn = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = $oneCommand;

    return $conn->query($sql);
}

//query single result
function runCommand3($dbhost, $dbuser, $dbpassword, $dbname, $oneCommand) {
    // Create connection
    $conn = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = $oneCommand;

    $result = $conn->query($sql);
    $row = mysqli_fetch_array($result);
    return $row[0];
}

function replace_first_after_word($search, $replace, $wordToSearchAfter, $str) {
    ##change , in decimal it is used to separate colums
    $line = $str;
    if (strpos($str, $wordToSearchAfter) !== false) {
        $loops = array_count_values(str_word_count($str, 1)); //substr_count($str, $search);
        $loops = $loops[$wordToSearchAfter];
        if ($loops > 0) {
            $line = explode($wordToSearchAfter, $str);
            for ($i = 1; $i <= $loops; $i++) {
                $myPart = str_split($line[$i]);

                for ($j = 0; $j < count($myPart); $j++) {
                    if ($myPart[$j] == $search) {
                        $myPart[$j] = $replace;
                        $myPart = implode("", $myPart);
                        break;
                    }
                }
                $line[$i] = $myPart;
            }
            $line = implode($wordToSearchAfter, $line);
        }
    }

    return $line;
}

?>