<?php

/**
 * Functions for Exercise Database Table
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 * @author     Max Sternitzke
 */
class Trainingssystem_Plugin_Database_Exercise_Daoimple implements Trainingssystem_Plugin_Database_Exercise_Dao {

    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_EXERCISE;

    public static $availableOrders = [
        "title_asc" => " ORDER BY ep.post_title ASC",
        "title_desc" => " ORDER BY ep.post_title DESC",
        "date_asc" => " ORDER BY ex.favorite_date ASC",
        "date_desc" => " ORDER BY ex.favorite_date DESC",
    ];

    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,
                `exercise_ID` bigint(20) NOT NULL,
                `user_ID` bigint(20) NOT NULL,
                `favorite_date` timestamp NOT NULL DEFAULT current_timestamp(),
                `training_ID` bigint(20) NULL DEFAULT NULL,
                `lektion_ID` bigint(20) NULL DEFAULT NULL,
                `seiten_ID` bigint(20) NULL DEFAULT NULL,
                `url` varchar(512) 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(){

    }

    /**
     * Determines if an exercise has been favorited by a user
     * 
     * @return boolean favorite
     */
    public function exerciseIsFavorite($exercise_id, $user_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $row = $wpdb->get_row($wpdb->prepare( "SELECT COUNT(ID) as count FROM $table_name WHERE exercise_ID = %d AND user_ID = %d", $exercise_id, $user_id), OBJECT);
        
        if($row != null) {
            return $row->count;
        }
        return false;
    }

    /**
     * Counts how often an exercise has been favorised
     * 
     * @return int
     */
    public function countExerciseFavorite($exercise_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $row = $wpdb->get_row($wpdb->prepare( "SELECT COUNT(ID) as count FROM $table_name WHERE exercise_ID = %d", $exercise_id), OBJECT);
        
        if($row != null) {
            return $row->count;
        }
        return 0;
    }

    /**
     * Getting all the favorite exercises for a user
     * 
     * @return Array
     */
    public function getFavoriteExerciseByUser($user_id, $orderby = null) {
        global $wpdb;

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

        if($orderby == null || !isset(self::$availableOrders[$orderby]))
            $orderby = 'title_asc';

        $sql = "SELECT * FROM $table_name ex INNER JOIN $wpdb->posts ep ON ex.exercise_ID = ep.ID WHERE user_ID = %d" . self::$availableOrders[$orderby];
        
        $data = $wpdb->get_results($wpdb->prepare($sql, $user_id ), OBJECT);
        $result = [];

        foreach ($data as $row){

            $result[] = new Trainingssystem_Plugin_Database_Exercise($row->ID, $row->exercise_ID, $row->user_ID, $row->favorite_date, $row->training_ID, $row->lektion_ID, $row->seiten_ID, $row->url, $row->post_title, $row->post_content);
        }

        return $result;
    }

    /**
     * Favorites an exercise by inserting a row into the DB table
     * 
     * @return boolean success
     */
    public function favoriteExercise($exercise_id, $user_id, $training_id, $lektion_id, $seiten_id, $url) {
        global $wpdb;

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

        $sql = $wpdb->insert($table_name,
                                array(
                                    "exercise_ID" => $exercise_id,
                                    "user_ID" => $user_id,
                                    "training_ID" => $training_id,
                                    "lektion_ID" => $lektion_id,
                                    "seiten_ID" => $seiten_id,
                                    "url" => $url,
                                ),
                                array("%d", "%d", "%d", "%d", "%d", "%s")
                            );
        return $sql !== false;
    }

    /**
     * Unfavorites an exercise for a user by deleting the row in the DB table
     * 
     * @return boolean success
     */
    public function unfavoriteExercise($exercise_id, $user_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->delete($table_name,
                                array(
                                    "exercise_ID" => $exercise_id,
                                    "user_ID" => $user_id
                                ),
                                array(
                                    "%d", "%d"
                                )
                            );

        return $sql !== false;
    }

    /**
     * Deletes all exercise entries connected to a specific exercise id. Usually called when exercise is deleted
     * 
     * @return boolean success
     */
    public function deleteAllExercises($exercise_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->delete($table_name,
                                array(
                                    "exercise_ID" => $exercise_id
                                ),
                                array(
                                    "%d"
                                )
                            );

        return $sql !== false;
    }
}
