<?php

/**
 * Functions for Certificate Database Table
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 * @author     Tim Mallwitz
 */
class Trainingssystem_Plugin_Database_Certificate_Daoimple implements Trainingssystem_Plugin_Database_Certificate_Dao {

    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_CERTIFICATE;

    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,
                `certificate_ID` bigint(20) NOT NULL,
                `training_ID` bigint(20) NOT NULL,
                `certificate_header` text NOT NULL,
                `certificate_content` text NOT NULL,
                `certificate_footer` text 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(){

    }

    /**
     * Inserts a new certificate dataset
     * 
     * @param int certificate_id
     * @param int training_id
     * @param String certificate_header
     * @param String certificate_content
     * @param String certificate_footer 
     * 
     * @return boolean success
     *  */    
    public function insertCertificateData($certificate_id, $training_id, $certificate_header, $certificate_content, $certificate_footer){
        
        global $wpdb;
        $table_name_certificate = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->insert($table_name_certificate,
                                array(
                                    "certificate_ID" => $certificate_id,
                                    "training_ID" => $training_id,
                                    "certificate_header" => $certificate_header,
                                    "certificate_content" => $certificate_content,
                                    "certificate_footer" => $certificate_footer,
                                ),
                                array("%d", "%d", "%s", "%s", "%s")
                            );
        return $sql !== false;
    }

    /**
     * Deletes a certificate connected to a specific certificate_id
     * 
     * @param int certificate_id
     * 
     * @return boolean success
     */
    public function deleteCertificateData($certificate_id){
        global $wpdb;
        $table_name_certificate = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->delete($table_name_certificate,
                                array(
                                    "certificate_ID" => $certificate_id
                                ),
                                array(
                                    "%d"
                                )
                            );

        return $sql !== false;
    }

    public function getAllCertificates(){
        global $wpdb;
        $table_name_certificate = $wpdb->prefix . self::$dbprefix;

        $sql = "SELECT * FROM $table_name_certificate c INNER JOIN $wpdb->posts p ON c.certificate_ID = p.ID";

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

        $ret = [];

        foreach($result as $row){
            $ret[] = new Trainingssystem_Plugin_Database_Certificate($row->ID, $row->certificate_ID, $row->training_ID, $row->certificate_header, $row->certificate_content, $row->certificate_footer, $row->post_title);
        }

        return $ret;
    }

    public function getCertificateById($certificate_id){
        global $wpdb;
        $table_name_certificate = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->prepare("SELECT * FROM $table_name_certificate WHERE certificate_ID = %d", $certificate_id);
        $result = $wpdb->get_row($sql);

        return new Trainingssystem_Plugin_Database_Certificate($result->ID, $result->certificate_ID, $result->training_ID, $result->certificate_header, $result->certificate_content, $result->certificate_footer);
    }

    public function getCertificateHeaderById($certificate_id){
        global $wpdb;
        $table_name_certificate = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->prepare("SELECT certificate_header FROM $table_name_certificate WHERE certificate_ID = %d", $certificate_id);
        $result = $wpdb->get_row($sql);
        $certificate_header = array();

        if(!empty($result)){
            $certificate_header = json_decode($result->certificate_header);
        }
        return $certificate_header;
        
    }

    public function getCertificateContentById($certificate_id){
        global $wpdb;
        $table_name_certificate = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->prepare("SELECT certificate_content FROM $table_name_certificate WHERE certificate_ID = %d", $certificate_id);
        $result = $wpdb->get_row($sql);
        $certificate_content = array();

        if(!empty($result)){
            $certificate_content = json_decode($result->certificate_content);
        }
        return $certificate_content;
    }

    public function getCertificateFooterById($certificate_id){
        global $wpdb;
        $table_name_certificate = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->prepare("SELECT certificate_footer FROM $table_name_certificate WHERE certificate_ID = %d", $certificate_id);
        $result = $wpdb->get_row($sql);
        $certificate_footer = array();

        if(!empty($result)){
            $certificate_footer = json_decode($result->certificate_footer);
        }

        return $certificate_footer;
    }

}
