<?php

/* ## while using output_buffering=off in php.ini it wil show reult line by line
  ob_start();
  echo 'Begin ...<br />';
  for ($i = 0; $i < 10; $i++) {
  echo $i . '<br />';
  flush();
  ob_flush();
  sleep(1);
  }
  echo 'End ...<br />';
  die(); */
error_reporting(E_ERROR);
//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);
//error_reporting(E_ALL);
//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');
//Programsetting
$Programsetting = new Programsetting();
$ProgramsettingDAO = new ProgramsettingsMySqlDAO();
$ProgramsettingEX = new ProgramsettingsMySqlExtDAO();




$nochangeColumns = array("productionrateproduct" => "quantity", "taskorder" => "num", "taskorderproducts" => "quantity"
    , "productionexecution" => "num", "productionexecutionoutput" => "num", "productionexecutionproduct" => "quantity,actualquantity", "productionexecutionwaste" => "num"
    , "storemovement" => "transferproductamount", "storedetail" => "productquantity", "sizecolorstoredetail" => "quantity", "storereport" => "productquantity,productbefore,productafter"
    , "restaurantrawdestruction" => "quantity");

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

$noOfsuccess = 0;
$noOfDuplicateColumnName = 0;
$noOfExcutedWithError = 0;
$noOfUnknownColumn = 0;
$laterSql = "";
#################################################
//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); //for keys
                    $line = $line . "++"; //for keys
                }
                $oneCommand .= $line;

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

    ##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);
    }
    //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) - $time_start);
    echo 'execution time is: ' . round($time_end / 60, 2) . ' Mins';

    ##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.
}

###############################################################

function runCommand($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) {

        } elseif (stripos($errorMessage, "check that column/key exists") !== false) {
            //try to drop index that is not there its ok
        } 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;
    global $nochangeColumns;
    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 . '"';
        $allResult = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckTable);
        $result = $allResult[0];
        $rowsDataOfSelect = $allResult[1];

        if ($result->num_rows == "1") {
            //this condition is to skip creat table that come with "view"
            //if (strpos($tablename, 'view') === false) {
            if ($rowsDataOfSelect[0]["TABLE_TYPE"] === "BASE TABLE") {
                //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
                $secondExplode = str_replace("++", ",", $secondExplode); //for keys
                $colums = explode(",", $secondExplode);
                foreach ($colums as $oneColumn) {
                    $oneColumn = replace_first_after_word("-", ",", "decimal", $oneColumn);

                    $colData = explode("`", $oneColumn);
                    $columName = $colData[1];
                    $columProp = $colData[2];

                    if (!empty($columName)) {
                        //if key do no thing it do exist in first table insert
                        if (strpos($oneColumn, 'PRIMARY KEY') === 0) {
                            //later
                        } else if (strpos($oneColumn, 'UNIQUE KEY') === 0) {
                            //later
                            $oneColumn = str_replace("**", ",", $oneColumn); //for keys
                        } else if (strpos($oneColumn, 'FOREIGN KEY') === 0) {
                            //later
                        } else if (strpos($oneColumn, 'KEY') === 0) {
                            $oneColumn = str_replace("**", ",", $oneColumn); //for keys
                            $oneColumn = str_replace("KEY", "", $oneColumn);
                            $oneColumn = str_replace(")", "", $oneColumn);
                            $oneColumn = explode("(", $oneColumn);

                            $sqlCol = ' ALTER TABLE ' . $tablename . ' DROP INDEX ' . trim($oneColumn[0]) . ';';
                            runCommand($dbhost, $dbuser, $dbpassword, $dbname, $sqlCol);
                            $sqlCol = ' ALTER TABLE ' . $tablename . ' ADD INDEX ' . trim($oneColumn[0]) . ' (' . trim($oneColumn[1]) . ');'; //name of key is optional but preferred
                            runCommand($dbhost, $dbuser, $dbpassword, $dbname, $sqlCol);
                        } else {

                            if (isset($nochangeColumns[$tablename]) && in_array($columName, explode(',', $nochangeColumns[$tablename]))) {
                                //this id no change column do nothing
                            } else {
                                $sqlCheckColumn = 'SELECT * FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA` = "' . $dbname . '" and `TABLE_NAME` = "' . $tablename . '" and `COLUMN_NAME`="' . $columName . '"';

                                $allResult = runCommand2($dbhost, $dbuser, $dbpassword, "information_schema", $sqlCheckColumn);
                                $result = $allResult[0];
                                $rowsDataOfSelect = $allResult[1];

                                if ($result->num_rows == "1") {
                                    //column exist
                                    $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 = str_replace("++", "", $oneCommand); //for keys
            $oneCommand = replace_first_after_word("-", ",", "decimal", $oneCommand);
            $oneCommand = str_replace("`**`", "`,`", $oneCommand); //for keys
            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);
    }
}

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;
    $result = $conn->query($sql);
    $allRows = array();
    while ($row = mysqli_fetch_assoc($result)) {//mysqli_fetch_field
        array_push($allRows, $row);
    }
    return array($result, $allRows);
}

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;
}

?>