<?php

/**
 * Functions for Formfields Database Table
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 * @author     Max Sternitzke
 */
class Trainingssystem_Plugin_Database_Formfield_Daoimple implements Trainingssystem_Plugin_Database_Formfield_Dao {

    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_FORM_FIELDS;

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

    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 (
                `ID` bigint(20) NOT NULL AUTO_INCREMENT,
                `form_ID` bigint(20) NOT NULL,
                `type` varchar(32) NOT NULL,
                `attributes` text NOT NULL,
                `sort_index` int(11) NOT NULL,
                PRIMARY KEY (`ID`)
              )$charset_collate;"
        ];
        Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);
    }

    /**
     * database updating beim plugin update
     *
     * @since    1.0.0
     */
	public function updating(){

    }

    /**
     * Adds a new Formfield Dataset
     * 
     * @param int form ID
     * @param String type
     * @param String JSON encoded attributes
     * @param int sort_index
     * 
     * @return boolean success
     */
    public function addFormfield($formid, $type, $attributes, $sort_index) {
        global $wpdb;

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

        $sql = $wpdb->insert($table_name,
            array(
            'form_ID' => $formid,
            'type' => $type,
            'attributes' => $attributes,
            'sort_index' => $sort_index,
            ),
            array(
                '%d',
                '%s',
                '%s',
                '%d'
            )
        );
        return $sql !== false;
    }

    /**
     * Updates a formfield entry
     * 
     * @param int Formfield ID
     * @param String type
     * @param String JSON encoded attributes
     * 
     * @return boolean success
     */
    public function updateFormfield($fieldid, $type, $attributes) {
        global $wpdb;

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

        $sql = $wpdb->update($table_name,
            array(
                'attributes' => $attributes,
            ),
            array(
                'ID' => $fieldid,
                'type' => $type,
            ),
            array(
                '%s'
            ),
            array(
                '%d',
                '%s'
            )
        );

        return $sql !== false;
    }

     /**
     * Return a Formfield Object
     * 
     * @param int formfield ID
     * 
     * @return Trainingssystem_Plugin_Database_Formfield Object or null if not found
     */
    public function getFormfield($fieldid) {
        global $wpdb;

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

        $row = $wpdb->get_row($wpdb->prepare("SELECT $table_name.*, COUNT($table_name_data.ID) as anzahl FROM $table_name LEFT JOIN $table_name_data ON $table_name.ID = $table_name_data.field_ID WHERE $table_name.ID = %d", $fieldid), OBJECT);
        
        if($row != null) {
            return new Trainingssystem_Plugin_Database_Formfield($row->ID, $row->form_ID, $row->type, $row->attributes, $row->sort_index, null, $row->anzahl);
        }
        return null;
    }

    /**
     * Gets the number of Form Fields for a given form
     * 
     * @param int form ID
     * 
     * @return int count of fields for formid, default 0
     */
    public function getFormfieldsCount($formid) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $row = $wpdb->get_row($wpdb->prepare( "SELECT COUNT(ID) as count FROM $table_name WHERE form_ID = %d ", $formid), OBJECT);
        
        if($row != null) {
            return $row->count;
        }
        return 0;
    }
    
    /**
     * Returns an array of FormField Objects for a given form
     * 
     * @param int form ID
     * 
     * @return Array of Trainingssystem_Plugin_Database_Formfield Objects, empty as default
     */
    public function getFormfieldsByForm($formid) {

        global $wpdb;

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

        $data = $wpdb->get_results($wpdb->prepare("SELECT $table_name.*, COUNT($table_name_data.ID) as anzahl FROM $table_name LEFT JOIN $table_name_data ON $table_name.ID = $table_name_data.field_ID WHERE form_ID = %d GROUP BY $table_name.ID ORDER BY sort_index ASC", $formid), OBJECT);
        $result = [];

        foreach ($data as $row){

            $result[] = new Trainingssystem_Plugin_Database_Formfield($row->ID, $row->form_ID, $row->type, $row->attributes, $row->sort_index, null, $row->anzahl);
        }
        return $result;

    }

    /**
     * Returns an array of a field's FormField Objects filled with FormData Objects for a specific user 
     * if the user id is not null, otherwise FormData will stay null
     * 
     * @param int form ID
     * @param int user ID
     * 
     * @return Array of Trainingssystem_Plugin_Database_Formfield Objects, empty as default
     */
    public function getUserFormfieldsByForm($formid, $userid) {

        global $wpdb;

        $ret = array();

        $formgroupCount = Trainingssystem_Plugin_Database::getInstance()->Formdata->getFormgroupCount($formid, $userid);

        for($i = 0; $i <= $formgroupCount; $i++) {
            $ret[$i] = $this->getFormfieldsByForm($formid);

            if($userid != 0) {
                foreach($ret[$i] as $formfield) {
                    $formfield->setFormdata(Trainingssystem_Plugin_Database::getInstance()->Formdata->getFormfieldData($formfield->getFieldId(), $userid, $i));
                }
            }
        }

        return $ret;

    }

    /**
     * Sets a new order for a formfield of a given form
     * 
     * @param int form ID
     * @param int field ID
     * @param int new Index
     * 
     * @return boolean success
     */
    public function setFormfieldOrder($formid, $fieldid, $ordernew) {
        
        global $wpdb;

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

        $sql = $wpdb->update($table_name,
            array(
                "sort_index" => $ordernew
            ),
            array(
                "form_ID" => $formid,
                "ID" => $fieldid
            ),
            array(
                "%d"
            ),
            array(
                "%d", "%d"
            )
        );

        return $sql !== false;
    }

    /**
     * Deletes a form field of a form
     * 
     * @param int form ID
     * @param int field ID
     * 
     * @return boolean success
     */
    public function removeFormfield($formid, $fieldid) {

        global $wpdb;

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

        $sql = $wpdb->delete($table_name,
            array(
                'ID' => $fieldid,
                'form_ID' => $formid
            ),
            array(
                '%d', '%d'
            )
        );

        return $sql !== false;
    }

    /**
     * Delete all form fields of a form
     * 
     * @param int form ID
     * 
     * @return boolean success
     */
    public function deleteFormfieldsByFormId($formid) {
        global $wpdb;

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

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

        return $sql !== false;
    }

    /**
     * Imports a set of form fields for a specific form ID
     * 
     * @param int form ID
     * @param Array Form Fields Array
     * 
     * @return boolean success
     */
    public function importFormfields($formid, $dataArray) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . self::$dbprefix;

        foreach($dataArray as $field) {
            if(!$this->addFormfield($formid, $field['type'], $field['attributes'], $field['sort_index'])) {
                $this->deleteFormfieldsByFormId($formid);
                return false;
            }
        }

        return true;
    }

    public function getScaleMatrixForms() {
        global $wpdb;

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

        $data = $wpdb->get_results("SELECT DISTINCT $table_name.form_ID, $table_posts.post_title FROM $table_name INNER JOIN $table_posts ON $table_name.form_ID = $table_posts.ID WHERE ($table_name.type = 'scale' OR $table_name.type = 'matrix') ORDER BY $table_name.form_ID DESC", OBJECT);
        $result = [];

        foreach ($data as $row) {
            $result[$row->form_ID] = $row->post_title;
        }
        return $result;
    }

    /**
     * Get data for all $tsformids in one query for the DataExport Module
     * 
     * @param Integer-Array TS Form IDs
     * 
     * @return Array
     *          |-> TS Form ID
     *              |-> Field 1 as Formfield-Object
     *              |-> Field 2 etc.
     */
    public function getDataExportData($tsformids) {
        global $wpdb;

        $table_name = $wpdb->prefix . self::$dbprefix;
        $data = $wpdb->get_results("SELECT * FROM $table_name WHERE form_ID IN (" . implode(",", $tsformids) . ") ORDER BY form_ID, sort_index");

        $ret = array();

        foreach($data as $row) {
            if(!isset($ret[$row->form_ID])) {
                $ret[$row->form_ID] = array();
            }
            $ret[$row->form_ID][] = new Trainingssystem_Plugin_Database_Formfield($row->ID, $row->form_ID, $row->type, $row->attributes, $row->sort_index, null);
        }
        
        return $ret;
    }
}
