<?php

namespace IMATHUZH\Qfq\Core\Database;

use IMATHUZH\Qfq\Core\Store\Sip;
use IMATHUZH\Qfq\Core\Store\Store;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
use PHPUnit\Exception;

class DatabaseQfqSpecific {


    /**
     * Build a form to show in an exception where the current FE User selects missing tables to be created.
     *
     * @param $indexMatch - keyname of one element in $fileTables to be preselected.
     * @param array $files - key=name of the sql file, value=tables created by the named sql file.
     * @param $dbIndexUsed - dbIndex of db to add the tables to.
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function formCreateQfqTable($indexMatch, array $files, $dbIndexUsed): string {

        $html = '';

        // Fetch a list of current tables
        $db = new Database($dbIndexUsed);
        $arr = $db->sql("SHOW Tables");
        // Check if Permissions to create Tables is given.
        try {
            $db->sql(SQL_CHECK_DROP_TABLE_PERMISSION);
            $db->sql(SQL_CHECK_CREATE_TABLE_PERMISSION);
            $db->sql(SQL_CHECK_DROP_TABLE_PERMISSION);
        } catch (\Exception $e) {
            if (stripos($e->getMessage(), 'denied') === false) {
                // Permission was not denied but another Exception was Thrown
                throw $e;
            }
            return "<form><p>The database user does not have permission to create SQL tables.<br/>
                    Please check the configuration of the database user account.<br/>
                    Required privileges: <code>CREATE</code>, <code>DROP</code>, <code>CREATE TEMPORARY TABLE</code>.</p></form>";
        }
        foreach ($arr as $row) {
            $key = reset($row);// get first element
            $currentTables[$key] = 1;
        }

        // Sort according  'table names'
        asort($files);

        $lenPrefix = strlen(QFQ_TABLE_PREFIX);
        // Iterate over all sql files
        foreach ($files as $file => $tableNames) {
            $len = strlen($file);
            // From filename 'qfqTablePeriod.sql' extract 'Period'
            $category = substr($file, $lenPrefix, $len - $lenPrefix - 4); // cut 'qfq...sql'

            // Store SQL files in session, attacker can't manipulate which sql file will be played.
            $_SESSION[QFQ_CREATE_TABLE][$category] = $file;
            $htmlId = DB_UPDATE_INPUT_PREFIX . $category;

            $option = '';
            $optionLabel = '';

            // Preselect SQL file which contains the missing table
            if ($file == $indexMatch) {
                $option = ' checked title="Missing table(s)."';
                $optionLabel = ' title="Missing table(s)."';
            }

            // Check if all tables already exist: disable checkbox. $tableNames can be a comma separated list of table names.
            $minOneMissing = false;
            $arr = explode(',', $tableNames);
            foreach ($arr as $table) {
                if (!isset($currentTables[$table])) {
                    $minOneMissing = true;
                }
            }
            if (!$minOneMissing) {
                $option .= " disabled";
                $optionLabel .= ' title="Table already exist." class="text-muted"';
            }

            // Build input checkbox for current sql file
            $html .= "<label for=\"$htmlId\"$optionLabel>"
                . "<input type=\"checkbox\" id=\"$htmlId\" name=\"$htmlId\" value=\"1\"$option> "
                . ($category == $tableNames ? $category : "$category: $tableNames") . "</label><br>";
        }

        // Finalize form build
        $arg = SIP_MODE_SAVE . '=' . SAVE_MODE_DB_UPDATE . '&' . SIP_DB_INDEX_USED . '=' . $dbIndexUsed;
        $sip = new Sip();
        $sipValue = $sip->queryStringToSip($arg, RETURN_SIP);
        $html .= HelperFormElement::buildNativeHidden(CLIENT_SIP, $sipValue);
        $action = Path::urlApi(API_SAVE_PHP);
        $html = '<form action="' . $action . '" method="post">' . $html . '<input class="btn btn-default" type="submit" value="Create table(s)"></form>';

        return $html;
    }

    /**
     * Build a form to show in an exception where the current FE User selects missing functions to be created.
     *
     * @param $indexMatch - keyname of one element in $fileTables to be preselected.
     * @param array $files - key=name of the sql file, value=tables created by the named sql file.
     * @param $dbIndexUsed - dbIndex of db to add the tables to.
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function formCreateQfqFunction($indexMatch, array $files, $dbIndexUsed): string {

        //TODO - es kann sein das wir keine Berechtigung haben um funktionen anzulegen - dann keine neue Excepotion sondern die bestehenden normal anzeigen.
        $html = '';

        // Fetch a list of current functions
        $db = new Database($dbIndexUsed);

        // Check if Permissions to Functions is given.
        try {
            $db->sql(SQL_CHECK_DROP_FUNCTION_PERMISSION);
            $db->sql(SQL_CHECK_CREATE_FUNCTION_PERMISSION);
            $db->sql(SQL_CHECK_DROP_FUNCTION_PERMISSION);
        } catch (\Exception $e) {
            if (stripos($e->getMessage(), 'denied') === false) {
                // Permission was not denied but another Exception was Thrown
                throw $e;
            }
            return "<form><p>The database user does not have permission to create SQL functions.<br/>
                    Please check the configuration of the database user account.<br/>
                    Required permissions: <code>CREATE ROUTINE</code>, <code>ALTER ROUTINE</code>, <code>DROP</code>, <code>CREATE</code>.</p></form>";
        }

        $dbNameUsed = $db->getDbName();
        $arr = $db->sql("SHOW FUNCTION STATUS WHERE Db = '$dbNameUsed'");
        foreach ($arr as $row) {
            $key = $row['Name'] ?? '-';
            $currentTables[$key] = 1;
        }

        // Sort according  'function names'
        asort($files);

        $lenPrefix = strlen(QFQ_FUNCTION_PREFIX);
        // Iterate over all sql files
        foreach ($files as $file => $functionNames) {
            $len = strlen($file);
            // From filename 'qfqTablePeriod.sql' extract 'Period'
            $category = substr($file, $lenPrefix, $len - $lenPrefix - 4); // cut 'qfq...sql'

            // Store SQL files in session, attacker can't manipulate which sql file will be played.
            $_SESSION[QFQ_CREATE_TABLE][$category] = $file;
            $htmlId = DB_UPDATE_INPUT_PREFIX . $category;

            $option = '';
            $optionLabel = '';

            // Preselect SQL file which contains the missing table
            if ($file == $indexMatch) {
                $option = ' checked title="Missing table(s)."';
                $optionLabel = ' title="Missing table(s)."';
            }

            // Check if all tables already exist: disable checkbox. $tableNames can be a comma separated list of table names.
            $minOneMissing = false;
            $arr = explode(',', $functionNames);
            foreach ($arr as $table) {
                if (!isset($currentTables[$table])) {
                    $minOneMissing = true;
                }
            }
            if (!$minOneMissing) {
                $option .= " disabled";
                $optionLabel .= ' title="Table already exist." class="text-muted"';
            }

            $functionNames = str_replace(',', ' ', $functionNames);
            // Build input checkbox for current sql file
            $html .= "<label for=\"$htmlId\"$optionLabel>"
                . "<input type=\"checkbox\" id=\"$htmlId\" name=\"$htmlId\" value=\"1\"$option> "
                . ($category == $functionNames ? $category : "$category: $functionNames") . "</label><br>";
        }

        // Finalize form build
        $arg = SIP_MODE_SAVE . '=' . SAVE_MODE_DB_UPDATE . '&' . SIP_DB_INDEX_USED . '=' . $dbIndexUsed;
        $sip = new Sip();
        $sipValue = $sip->queryStringToSip($arg, RETURN_SIP);
        $html .= HelperFormElement::buildNativeHidden(CLIENT_SIP, $sipValue);
        $action = Path::urlApi(API_SAVE_PHP);
        $html = '<form action="' . $action . '" method="post">' . $html . '<input class="btn btn-default" type="submit" value="Create function(s)"></form>';

        return $html;
    }

    /**
     * Get from STORE_CLIENT input names with DB_UPDATE_INPUT_PREFIX???.
     * Per found DB_UPDATE_INPUT_PREFIX??? play corresponding () SQL file.
     * Such SQL files will create one or more tables and their indexes.
     *
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function playSelectedSqlFiles(): void {

        $indexDb = Store::getVar(SIP_DB_INDEX_USED, STORE_SIP);
        if (!is_int($indexDb)) {
            $indexDb = DB_INDEX_DEFAULT;
        }
        $db = new Database($indexDb);

        $clientValues = Store::getStore(STORE_CLIENT);
        $len = strlen(DB_UPDATE_INPUT_PREFIX);
        foreach ($clientValues as $key => $value) {
            if (DB_UPDATE_INPUT_PREFIX == substr($key, 0, $len) && $value == '1') {
                $category = substr($key, $len);
                $file = $_SESSION[QFQ_CREATE_TABLE][$category] ?? '';
                $db->playSqlFile(__DIR__ . '/../../Sql/' . $file);
            }
        }
    }
}