<?php
class Database {

    function check_database_exist_or_not($data){
        $hostname = $data['hostname'];
        $username = $data['username'];
        $password = $data['password'];
        $database_name = $data['database'];

        $conn = new mysqli($hostname, $username, $password);
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $stmt = $conn->prepare("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ?");
        $stmt->bind_param("s", $database_name);
        $stmt->execute();
        $stmt->store_result();
        $exists = $stmt->num_rows > 0;
        $stmt->close();
        $conn->close();
        return $exists;
    }

    function create_database($data){
        $mysqli = new mysqli($data['hostname'],$data['username'],$data['password'],'');
        if(mysqli_connect_errno()) return false;

        $db = $data['database'];
        $db_escaped = str_replace('`','``',$db);
        $ok = $mysqli->query("CREATE DATABASE IF NOT EXISTS `{$db_escaped}`");
        $mysqli->close();
        return $ok ? true : false;
    }

    function create_tables($data){
        $database_file = 'billing.sql';
        $sql = @file_get_contents($database_file);
        if($sql === false){
            echo $this->support();
            echo "<div class='container'>
                    <div class='col-md-6 col-md-offset-3'>
                        <div class='alert alert-warning'>
                            <h4><i class='fa fa-exclamation-triangle'></i> Database File Not Found</h4>
                            <p>Failed to read database file: <code>$database_file</code></p>
                            <hr>
                            <p><strong>Please make sure:</strong></p>
                            <ul>
                                <li>The billing.sql file exists in the setup/install/ directory</li>
                                <li>The file has proper read permissions</li>
                                <li>The file contains valid SQL content</li>
                            </ul>
                            <a href='javascript:history.back()' class='btn btn-primary'>
                                <i class='fa fa-arrow-left'></i> Go Back
                            </a>
                        </div>
                    </div>
                  </div>";
            exit;
        }

        $sql = $this->sanitize_dump($sql);

        sleep(1);

        $con = mysqli_connect($data['hostname'],$data['username'],$data['password'],$data['database']);
        if(mysqli_connect_errno()) return false;
        mysqli_set_charset($con, 'utf8mb4');

        $delimiter = ';';
        $buffer = '';
        $success = 0;
        $errors = [];
        $total = 0;

        $lines = explode("\n", $sql);
        foreach ($lines as $rawLine) {
            $line = rtrim($rawLine);

            if (preg_match('/^\s*DELIMITER\s+(.+)\s*$/i', $line, $m)) {
                $this->flush_stmt($con, $buffer, $delimiter, $success, $errors, $total);
                $buffer = '';
                $delimiter = $m[1];
                continue;
            }

            $buffer .= $rawLine . "\n";

            if ($this->ends_with(rtrim($buffer), $delimiter)) {
                $stmt = trim(substr($buffer, 0, -strlen($delimiter)));
                $buffer = '';
                if ($stmt === '' || preg_match('/^\s*DELIMITER\b/i', $stmt)) continue;

                if (preg_match('/\bDEFINER\b/i', $stmt)) continue;
                if (preg_match('/^\s*SET\s+/i', $stmt)) continue;

                $total++;
                $res = mysqli_query($con, $stmt);
                if ($res) {
                    $success++;
                } else {
                    $err = mysqli_error($con);
                    if (stripos($err, 'SUPER') === false && stripos($err, 'Access denied') === false) {
                        if (stripos($err, 'Duplicate') === false && stripos($err, 'exists') === false) {
                            $errors[] = $err;
                        }
                    } else {
                        $success++;
                    }
                }
                usleep(30000);
            }
        }

        $this->flush_stmt($con, $buffer, $delimiter, $success, $errors, $total);
        mysqli_close($con);
        sleep(1);

        if ($total === 0) return false;
        if ($success >= ($total * 0.8)) return true;

        if (!empty($errors)) {
            error_log('Database installation errors: ' . implode(' | ', array_unique($errors)));
        }
        return false;
    }

    private function sanitize_dump($sql){
        $sql = str_replace("\r\n","\n", $sql);
        $sql = str_replace("\r","\n", $sql);

        $sql = preg_replace('/\/\*!\d+\s*DEFINER\s*=\s*[^*]*\*\//i', '', $sql);
        $sql = preg_replace('/\bCREATE\s+DEFINER\s*=\s*[`"\']?[^`"\']+[`"\']?@[`"\']?[^`"\']+[`"\']?\s+/i', 'CREATE ', $sql);
        $sql = preg_replace('/\bDEFINER\s*=\s*[`"\']?[^`"\']+[`"\']?@[`"\']?[^`"\']+[`"\']?/i', '', $sql);
        $sql = preg_replace('/\bSQL\s+SECURITY\s+DEFINER\b/i', 'SQL SECURITY INVOKER', $sql);
        $sql = preg_replace('/\bALGORITHM\s*=\s*(UNDEFINED|MERGE|TEMPTABLE)\b/i', '', $sql);

        $sql = preg_replace('/^\s*SET\s+@@?GLOBAL\s+[^;]+;\s*/im','', $sql);
        $sql = preg_replace('/^\s*SET\s+SQL_MODE\s*=\s*[^;]+;\s*/im','', $sql);
        $sql = preg_replace('/^\s*SET\s+time_zone\s*=\s*[^;]+;\s*/im','', $sql);
        $sql = preg_replace('/^\s*SET\s+@[^;]+;\s*/im','', $sql);

        $sql = preg_replace('/^\s*START\s+TRANSACTION\s*;\s*/im','', $sql);
        $sql = preg_replace('/^\s*COMMIT\s*;\s*/im','', $sql);
        $sql = preg_replace('/^\s*LOCK\s+TABLES\s+[^;]+;\s*/im','', $sql);
        $sql = preg_replace('/^\s*UNLOCK\s+TABLES\s*;\s*/im','', $sql);

        $sql = preg_replace('/\/\*!\d+[^*]*\*+(?:[^/*][^*]*\*+)*\//i', '', $sql);

        $sql = preg_replace('/CREATE\s+VIEW\s+`?[\w]+`?\s+.*?DEFINER\s*=\s*`[^`]+`@`[^`]+`.*?;/is', '', $sql);
        $sql = preg_replace('/CREATE\s+VIEW\s+`?[\w]+`?\s+.*?SQL\s+SECURITY\s+DEFINER.*?;/is', '', $sql);

        $sql = preg_replace('/DROP\s+TABLE\s+IF\s+EXISTS\s+`?view_[\w]+`?;\s*/i', '', $sql);

        return $sql;
    }

    private function flush_stmt($con, &$buffer, $delimiter, &$success, &$errors, &$total){
        $buffer = trim($buffer);
        if ($buffer === '') return;
        if ($this->ends_with($buffer, $delimiter)) {
            $stmt = trim(substr($buffer, 0, -strlen($delimiter)));
        } else {
            $stmt = $buffer;
        }
        $buffer = '';

        if ($stmt === '' || preg_match('/^\s*DELIMITER\b/i', $stmt)) return;
        if (preg_match('/\bDEFINER\b/i', $stmt)) return;
        if (preg_match('/^\s*SET\s+/i', $stmt)) return;

        $total++;
        $res = mysqli_query($con, $stmt);
        if ($res) {
            $success++;
        } else {
            $err = mysqli_error($con);
            if (stripos($err, 'SUPER') === false && stripos($err, 'Access denied') === false) {
                if (stripos($err, 'Duplicate') === false && stripos($err, 'exists') === false) {
                    $errors[] = $err;
                }
            } else {
                $success++;
            }
        }
    }

    private function ends_with($haystack, $needle){
        $len = strlen($needle);
        if ($len === 0) return true;
        return substr($haystack, -$len) === $needle;
    }

    function getBaseUrl(){
        $protocol = ((!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off') || (isset($_SERVER['SERVER_PORT']) && $_SERVER['SERVER_PORT'] == 443)) ? "https://" : "http://";
        $url = $protocol . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];
        $url = str_replace('setup/install/', '', $url);
        $url = str_replace('index.php', '', $url);
        return rtrim($url, '/');
    }

    function support(){
        return "
        <link href='https://maxcdn.bootstrapcdn.com/bootswatch/3.3.7/cosmo/bootstrap.min.css' rel='stylesheet'>
        <br>
        <div class='container'>
            <div class='col-md-4 col-md-offset-4'>
                <p class='alert alert-success alert-dismissible'>
                    If you facing any issue in Installation, Please Contact Support. <br>
                    Email: admin@nanzige.com
                </p>
            </div>
        </div>
        <br>";
    }
}
