<?php


class Trainingssystem_Plugin_Database_Nachrichten_Daoimple {



	public static $dbprefix=TRAININGSSYSTEM_PLUGIN_DB_TRAINING_NACHRICHTEN;
	/**
	 * Initialize the collections used to maintain the actions and filters.
	 *
	 * @since    1.0.0
	 */
	public function __construct() {

		$this->iniDB();
	}

	private function iniDB(){



		global $wpdb;
		$table_name = $wpdb->prefix . self::$dbprefix;
		$charset_collate = $wpdb->get_charset_collate();
        $tableversionsstr =[
			"CREATE TABLE IF NOT EXISTS $table_name(
				message_id BIGINT(20) NOT NULL AUTO_INCREMENT,
				from_id BIGINT(20) NOT NULL,
				to_id BIGINT(20) NOT NULL,
				post_id BIGINT(20) NOT NULL,
				send_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
				read_at DATETIME DEFAULT NULL,
				deleted_by_sender_at DATETIME DEFAULT NULL,
				deleted_by_recipient_at DATETIME DEFAULT NULL,
				type_flag VARCHAR(100) DEFAULT NULL,
				PRIMARY KEY (message_id)
			)$charset_collate;",
			"ALTER TABLE $table_name CHANGE send_at send_at timestamp NOT NULL default CURRENT_TIMESTAMP;",
			"ALTER TABLE $table_name ADD lektion_id BIGINT(20) DEFAULT NULL AFTER `type_flag`;",
		];
		Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);
		



	}

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

	}

	/**
	 * Funktion wird aufgerufen, wenn der User sich selbst löscht oder gelöscht wird
	 * 
	 * @param User-ID des zu löschenden Users
	 */
	function deleteUserInput($user_id){
        
    }

	public function getUnreadMessagesByUser($user_id){

		global $wpdb;
		$table_name = $wpdb->prefix . self::$dbprefix;
		$table_name_posts = $wpdb->posts;
		$table_name_users = $wpdb->users;

		$sql = $wpdb->prepare("SELECT users.display_name AS sender, posts.post_title AS title FROM $table_name nachrichten INNER JOIN $table_name_posts posts ON nachrichten.post_id = posts.ID INNER JOIN $table_name_users users ON nachrichten.from_id = users.ID WHERE nachrichten.read_at IS NULL AND nachrichten.to_id = %d",$user_id);

		$result = $wpdb->get_results($sql);

		return $result;
	}
}
