<?php

class Trainingssystem_Plugin_Database_Calendar_Entry_Daoimple implements Trainingssystem_Plugin_Database_Calendar_Entry_Dao {

    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_PREFIX . 'calendar_entries';

    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 (
                `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
                `user_id` BIGINT(20) NOT NULL DEFAULT 0,
                `calendar_id` BIGINT(20) NOT NULL,
                `entry_date` DATE NOT NULL,
                `form_id` BIGINT(20) NULL,
                `form_field_id` BIGINT(20) NOT NULL DEFAULT 0,
                `form_data_id` BIGINT(20) NULL,
                `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                KEY `idx_calendar_date` (`calendar_id`, `entry_date`),
                KEY `idx_user_date` (`user_id`, `entry_date`),
                KEY `idx_form_id` (`form_id`),
                KEY `idx_form_data` (`form_data_id`),
                UNIQUE KEY `uniq_calendar_day_user_field` (`calendar_id`, `entry_date`, `user_id`, `form_field_id`)
            )$charset_collate;",
        ];

        Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix, $table_name, $tableversionsstr);
    }

    public function updating(){ }

    public function getEntriesByMonth($calendarId, $year, $month, $userId = 0){
        global $wpdb;
        $table = $wpdb->prefix . self::$dbprefix;
        $start = sprintf('%04d-%02d-01', $year, $month);
        $end = date('Y-m-t', strtotime($start));
        $whereUser = is_null($userId) ? '' : $wpdb->prepare(' AND (user_id = %d OR user_id = 0)', $userId);
        $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table WHERE calendar_id = %d AND entry_date BETWEEN %s AND %s $whereUser", $calendarId, $start, $end));
        $ret = [];
        foreach($rows as $r){
            $ret[] = new Trainingssystem_Plugin_Database_Calendar_Entry(
                $r->id,
                $r->calendar_id,
                $r->entry_date,
                $r->user_id,
                $r->form_id,
                $r->form_field_id,
                $r->form_data_id,
                $r->created_at,
                $r->updated_at
            );
        }
        return $ret;
    }

    public function insertEntry($calendarId, $date, $userId = 0, $formId = null, $formFieldId = 0, $formDataId = null){
        global $wpdb;
        $table = $wpdb->prefix . self::$dbprefix;
        $data = [
            'calendar_id' => $calendarId,
            'entry_date' => $date,
            'user_id' => $userId,
            'form_id' => $formId,
            'form_field_id' => $formFieldId,
            'form_data_id' => $formDataId
        ];
        $formats = ['%d','%s','%d','%d','%d','%d'];
        return false !== $wpdb->insert($table, $data, $formats);
    }

    public function deleteEntryById($entryId){
        global $wpdb;
        $table_entries = $wpdb->prefix . self::$dbprefix;
        $formdata_table = $wpdb->prefix . Trainingssystem_Plugin_Database_Formdata_Daoimple::$dbprefix;

        $row = $wpdb->get_row($wpdb->prepare("SELECT form_data_id FROM {$table_entries} WHERE id = %d", $entryId));
        if (!$row) { return false; }

        $wpdb->query('START TRANSACTION');
        $ok = true;

        if (!empty($row->form_data_id)) {
            $res1 = $wpdb->delete($formdata_table, ['ID' => intval($row->form_data_id)], ['%d']);
            if ($res1 === false) { $ok = false; }
        }

        if ($ok) {
            $res2 = $wpdb->delete($table_entries, ['id' => intval($entryId)], ['%d']);
            if ($res2 === false) { $ok = false; }
        }

        if ($ok) { $wpdb->query('COMMIT'); } else { $wpdb->query('ROLLBACK'); }
        return $ok;
    }

    public function deleteEntriesByCalendar($calendarId){
        global $wpdb;
        $table_entries = $wpdb->prefix . self::$dbprefix;
        $formdata_table = $wpdb->prefix . Trainingssystem_Plugin_Database_Formdata_Daoimple::$dbprefix;

        // Referenzierte FormData-IDs sammeln
        $formDataIds = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT form_data_id FROM {$table_entries} WHERE calendar_id = %d AND form_data_id IS NOT NULL",
            $calendarId
        ));

        // Transaktion (falls DB sie unterstützt)
        $wpdb->query('START TRANSACTION');

        $ok = true;
        if (!empty($formDataIds)) {
            $placeholders = implode(',', array_fill(0, count($formDataIds), '%d'));
            $sql = $wpdb->prepare("DELETE FROM {$formdata_table} WHERE ID IN ($placeholders)", $formDataIds);
            $res = $wpdb->query($sql);
            if ($res === false) { $ok = false; }
        }

        if ($ok) {
            $res2 = $wpdb->delete($table_entries, ['calendar_id' => $calendarId], ['%d']);
            if ($res2 === false) { $ok = false; }
        }

        if ($ok) { $wpdb->query('COMMIT'); }
        else { $wpdb->query('ROLLBACK'); }

        return $ok;
    }
}


