<?php
/**
 * Created by PhpStorm.
 * User: Ali Parnan
 * Date: 31.01.19
 * Time: 09:37
 */

class Trainingssystem_Plugin_Database_Registerkey_Daoimple implements Trainingssystem_Plugin_Database_RegisterKey_Dao{
    public static $dbprefix= TRAININGSSYSTEM_PLUGIN_DB_REGISTERKEY;


    public function __construct(){
        $this->iniDB();
    }

    public function updating() {}

    public function iniDB(){

        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $charset_collate = $wpdb->get_charset_collate();
        $tableversionsstr =[
            "CREATE TABLE IF NOT EXISTS $table_name(
				code BIGINT(20) NOT NULL,
				vorlage_id BIGINT(20) NOT NULL,
				creator_user_id BIGINT(20) NOT NULL,
				create_date DATETIME NOT NULL,
				count_code BIGINT(20) NOT NULL, 
				max_count BIGINT(20) NOT NULL,
				PRIMARY KEY (code)
                )$charset_collate;",
            "ALTER TABLE $table_name ADD `vorlage_id_optional` BIGINT(20) NOT NULL AFTER `vorlage_id`;",
            "ALTER TABLE $table_name ADD `company_id` BIGINT(20) NOT NULL AFTER `max_count`;",
            "ALTER TABLE $table_name ADD `companygroup_id` BIGINT(20) NOT NULL AFTER `company_id`;",
            "ALTER TABLE $table_name ADD `studygroup_id` VARCHAR(128) NULL AFTER `companygroup_id`;"
        ];
        Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);
    }

    /**
     * Checks if a Registerkey exists and can be redeemed (count_code <= max_count)
     * 
     * @return true/false
     */
    public function codeExists($code){
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $codes = $wpdb->get_results($wpdb->prepare(
            "SELECT code FROM $table_name WHERE code = %s AND count_code < max_count", $code)
        );
        if(sizeof($codes) == 1) {
            return true;
        }
        return false;
    }

    /**
     * Updates the count value of a Registerkey if it was used
     * 
     * @return true if success, false when not
     */
    public function codeUsed($code) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $codeDetail = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table_name WHERE code = %s AND count_code <= max_count", $code)
        );

        if($codeDetail != null){
            if(false === $wpdb->update(
                $table_name,
                array(
                    'count_code' => intval($codeDetail->count_code)+1	// integer (number)
                ),
                array( 'code' => $code),
                array(' %d '),
                array( '%d ')
            )) {
                return false;
            }
            return true;
        }
        return false;
    }
     
    /**
     * Get all Registercodes with Details out of the DB
     * 
     * @param allmine Boolean if only my registerkeys should be returned
     * 
     * @return array with Trainingssystem_Plugin_Database_Registerkey Objects
     */
    public function getAll($onlymine = false){
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $table_user = $wpdb->prefix . "users";

        $sql = "SELECT rk.*, users.display_name, users2.display_name AS display_name2 FROM $table_name as rk LEFT JOIN $table_user as users on rk.vorlage_id = users.ID LEFT JOIN $table_user as users2 on rk.vorlage_id_optional = users2.ID";
        if($onlymine) {
            $sql .= " WHERE creator_user_id = '" . get_current_user_id() . "'";
        }
        $data = $wpdb->get_results($sql, OBJECT);
        $result = [];

        foreach ($data as $row){
            $company = null;
            if(isset($row->company_id) && $row->company_id != 0) {
                $company = Trainingssystem_Plugin_Database::getInstance()->CompanyDao->getCompanyById($row->company_id);
            }
            $companygroup = null;
            if(isset($row->companygroup_id) && $row->companygroup_id != 0) {
                $companygroup = Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao->getCompanygroupById($row->companygroup_id);
            }
            $result[] = new Trainingssystem_Plugin_Database_Registerkey($row->code, $row->vorlage_id, $row->vorlage_id_optional, $row->creator_user_id, $row->create_date, $row->count_code,$row->max_count, add_query_arg("code", $row->code, wp_registration_url()), $row->display_name, $row->display_name2, $company, $companygroup, $row->studygroup_id);
        }
        return $result;

    }

    /**
     * Get my Registercodes with Details out of the DB - Alias
     * 
     * @return array with Trainingssystem_Plugin_Database_Registerkey Objects
     */
    public function getMy(){
        return $this->getAll(true);
    }

    /**
     * Get the Details on a sepcific Registerkey
     * 
     * @param code Registercode to look after
     * 
     * @return Trainingssystem_Plugin_Database_Registerkey Object
     */
    public function getCodeDetails($code, $createUrl = true){
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $table_user = $wpdb->prefix . "users";

        $row = $wpdb->get_row($wpdb->prepare( "SELECT rk.*, users.display_name, users2.display_name AS display_name2 FROM $table_name as rk LEFT JOIN $table_user as users on rk.vorlage_id = users.ID LEFT JOIN $table_user as users2 on rk.vorlage_id_optional = users2.ID WHERE rk.code = %s ", $code), OBJECT);
        
        if($row != null) {
            $company = null;
            if(isset($row->company_id) && $row->company_id != 0) {
                $company = Trainingssystem_Plugin_Database::getInstance()->CompanyDao->getCompanyById($row->company_id);
            }
            $companygroup = null;
            if(isset($row->companygroup_id) && $row->companygroup_id != 0) {
                $companygroup = Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao->getCompanygroupById($row->companygroup_id);
            }
            return new Trainingssystem_Plugin_Database_Registerkey($row->code, $row->vorlage_id, $row->vorlage_id_optional, $row->creator_user_id, $row->create_date, $row->count_code,$row->max_count, $createUrl ? add_query_arg("code", $row->code, wp_registration_url()) : '', $row->display_name, $row->display_name2, $company, $companygroup, $row->studygroup_id);
        }
        return null;
    }

    /**
     * Add a new Registerkey to the DB
     * 
     * @param code Registerkey
     * @param vorlage_id ID of Vorlagenuser
     * @param vorlage_id_optional ID of optional Vorlagenuser
     * @param creator_user_id ID of User that created the key
     * @param count_code number of times the code has been used
     * @param max_count number of times the code is allowed to be used
     * 
     * @return success true/false
     */
    public function addCode($code, $vorlage_id, $vorlage_id_optional, $creator_user_id, $count_code, $max_count, $company_id, $companygroup_id, $studygroup_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $fields = array(
            'code' => $code,
            'vorlage_id' => $vorlage_id,
            'vorlage_id_optional' => $vorlage_id_optional,
            'creator_user_id' => $creator_user_id,
            'create_date' => current_time('mysql'),
            'count_code' => $count_code,
            'max_count' => $max_count,
            'company_id' => $company_id,
            'companygroup_id' => $companygroup_id,
            'studygroup_id' => $studygroup_id
        );
        $formats = array(
            '%d',
            '%d',
            '%d',
            '%d',
            '%s',
            '%d',
            '%d',
            '%d',
            '%d',
            '%s',
        );

        $sql = $wpdb->insert($table_name, $fields, $formats);
        return $sql !== false;
    }

    /**
     * Delete a registerkey 
     * 
     * @param code Registerkey
     * 
     * @return success true/false
     */
    public function deleteCode($code) {
        global $wpdb;

        $table_name = $wpdb->prefix . self::$dbprefix;

        // Update Settings Offene Registrierung
        $settings = get_option(TRAININGSSYSTEM_PLUGIN_SETTINGS);
        if(isset($settings["open_register_code"])){
            if($code == $settings["open_register_code"]){
                $settings["open_register_code"] = "";
                if(!isset($settings["register_redeem_code_manually"])){
                    $settings["open_register_main_menu"] = false;
                }
                update_option(TRAININGSSYSTEM_PLUGIN_SETTINGS, $settings);
            }
        }


        $sql = $wpdb->delete($table_name,
            array(
                'code' => $code
            ),
            array(
                '%d'
            )
        );

        return $sql !== false;
    }

    /**
     * Update the maxCoutn value of a Registerkey
     * Can be updateted to update more values in the future
     * 
     * @param code Registerkey
     * @param maxCount number of times the code is allowed to be used
     * 
     * @return success true/false
     */
    public function updateCode($code, $maxCount) {
        global $wpdb;

        $table_name = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->update($table_name,
            array(
                "max_count" => $maxCount
            ),
            array(
                "code" => $code
            ),
            array(
                "%d"
            ),
            array(
                "%d"
            )
        );

        return $sql !== false;
    }

    /**
     * Update the vorlage of a Registerkey
     * 
     * @param code Registerkey
     * @param vorlagenid ID of Vorlagennutzer
     * 
     * @return success true/false
     */
    public function updateCodeVorlage($code, $vorlagenid) {
        global $wpdb;

        $table_name = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->update($table_name,
            array(
                "vorlage_id" => $vorlagenid
            ),
            array(
                "code" => $code
            ),
            array(
                "%d"
            ),
            array(
                "%d"
            )
        );

        return $sql !== false;
    }
}