<?php

namespace IMATHUZH\Qfq\Core\Helper;

/**
 * Class SqlQuery
 * @package qfq
 */
class SqlQuery {

    /**
     * Create SQL query that updates the given table with the contents of an associative array.
     * Returns prepared UPDATE statement together with a parameter array.
     * There must be at least one column to update.
     *
     * @param string $tableName
     * @param array $values nonempty, of the form array(column => value)
     * @param int $recordId
     * @param string $primaryKey
     * @return array|int
     * @throws \CodeException
     */
    public static function updateRecord(string $tableName, array $values, int $recordId, string $primaryKey = F_PRIMARY_KEY_DEFAULT) {
        if (count($values) === 0)
            throw new \CodeException('No columns given to update - this should not happen.', ERROR_CODE_SHOULD_NOT_HAPPEN);
        if ($recordId === 0) {
            throw new \CodeException('RecordId=0 - this is not possible for update.', ERROR_RECORDID_0_FORBIDDEN);
        }
        $sql = 'UPDATE `' . $tableName . '` SET ';
        foreach ($values as $column => $value) {
            $sql .= '`' . $column . '` = ?, ';
        }
        $sql = substr($sql, 0, strlen($sql) - 2) . " WHERE $primaryKey = ?";
        $values[] = $recordId;
        return array($sql, array_values($values));
    }

    /**
     * Create SQL query which inserts the values of the given associative array into the given table.
     * Returns prepared INSERT statement together with a parameter array.
     *
     * @param $tableName
     * @param array $values array(column => value)
     * @return array
     * @throws \CodeException
     */
    public static function insertRecord($tableName, array $values): array {
        if (count($values) === 0) {
            $sql = "INSERT INTO `$tableName` VALUES ()";
            return array($sql, []);
        }
        $paramList = str_repeat('?, ', count($values));
        $paramList = substr($paramList, 0, strlen($paramList) - 2);
        $columnList = '`' . implode('`, `', array_keys($values)) . '`';
        $sql = "INSERT INTO `$tableName` ( $columnList ) VALUES ( $paramList )";
        $parameterArray = array_values($values);
        return array($sql, $parameterArray);
    }

    /**
     * @param string $formName
     * @param array|null $columnsToSelect
     * @return array
     */
    public static function selectFormByName(string $formName, array $columnsToSelect = null): array {
        $NAME = F_NAME; // can't use constants in strings directly
        $FORM = TABLE_NAME_FORM;
        $DELETED = F_DELETED;
        $sql = "SELECT " . self::columnListSelect($columnsToSelect) . " FROM `$FORM` AS f WHERE `f`.`$NAME` LIKE ? AND `f`.`$DELETED`='no'";
        return array($sql, [$formName]);
    }

    /**
     * @param int $fId
     * @param array|null $columnsToSelect
     * @return array
     */
    public static function selectFormById(int $fId, array $columnsToSelect = null): array {
        $FORM = TABLE_NAME_FORM; // can't use constants in strings directly
        $ID = F_ID;
        $DELETED = F_DELETED;
        $sql = "SELECT " . self::columnListSelect($columnsToSelect) . " FROM `$FORM` AS f WHERE `f`.`$ID`=? AND `f`.`$DELETED`='no'";
        return array($sql, [$fId]);
    }

    /**
     * @param int $feId
     * @param array|null $columnsToSelect
     * @return array
     */
    public static function selectFormElementsByFormId(int $formId, array $columnsToSelect = null): array {
        $FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT; // can't use constants in strings directly
        $FORM_ID = FE_FORM_ID;
        $ORD = FE_ORD;
        $ID = FE_ID;
        $sql = "SELECT " . self::columnListSelect($columnsToSelect) . " FROM `$FORM_ELEMENT` AS `fe` WHERE `fe`.`$FORM_ID` = ? ORDER BY `fe`.`$ORD`, `fe`.`$ID`";
        return array($sql, [$formId]);
    }

    /**
     * @param int $feId
     * @return array
     */
    public static function selectFormElementById(int $feId, array $columnsToSelect = null): array {
        $FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT;
        $ID = FE_ID;
        $sql = "SELECT " . self::columnListSelect($columnsToSelect) . "FROM `$FORM_ELEMENT` WHERE `$ID` = ? ";
        return array($sql, [$feId]);
    }

    /**
     * Selects a form element by its name and form name.
     *
     * @param string $feName The name of the form element.
     * @param string $fName The name of the form.
     *
     * @return array An array containing the SQL query and its parameters.
     */
    public static function selectFormElementByName(string $feName, string $fName): array {
        $FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT;
        $FORM = TABLE_NAME_FORM;
        $FE_NAME = FE_NAME;
        $FE_ID = FE_ID;
        $FE_FORM_ID = FE_FORM_ID;
        $F_NAME = F_NAME;
        $sql = "SELECT fe.* FROM `$FORM_ELEMENT` AS `fe` JOIN `$FORM` AS `f` ON `fe`.`$FE_FORM_ID` = `f`.`$FE_ID` WHERE `fe`.`$FE_NAME` = ? AND `f`.`$F_NAME` = ?";

        return array($sql, [$feName, $fName]);
    }

    /**
     * @param string $tableName
     * @param array|null $columnsToSelect
     * @return array
     */
    public static function selectInformationSchemaByTableName(string $tableName, array $columnsToSelect = null): array {
        $sql = "SELECT" . self::columnListSelect($columnsToSelect) . "FROM information_schema.tables WHERE `table_name` = '$tableName' LIMIT 1;";
        return array($sql, [$tableName]);
    }

    /**
     * @param array|null $columns
     * @return string
     */
    private static function columnListSelect(array $columns = null) {
        if ($columns === null) {
            return ' * ';
        } else {
            return '`' . implode('`, `', $columns) . '`';
        }
    }
}
