<?php
/**
 * Neue, performante DAO-Implementierung für RegisterKey mit Pagination & Filter
 */

class Trainingssystem_Plugin_Database_Registerkey_Daoimpl_V2 {
    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_REGISTERKEY;
    
    public function __construct() {
        // ggf. Initialisierung
    }

    public function updating() {
        // Leere updating-Methode für Kompatibilität
    }

    /**
     * Paginierte Abfrage aller RegisterKeys (optional: nur eigene, optional: Filter)
     * @param int $limit
     * @param int $offset
     * @param bool $onlymine
     * @param string $search Suchbegriff
     * @param string $column Spalte für die Suche
     * @return array
     */
    public function getPaginated($limit = 10, $offset = 0, $onlymine = false, $search = '', $column = 'all') {

        
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $table_user = $wpdb->prefix . "users";
        $table_company = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanyDao::$dbprefix;
        $table_companygroup = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;

        $sql = "SELECT rk.*, users.display_name, users2.display_name AS display_name2, ".
               "c.name AS company_name, cg.name AS companygroup_name ".
               "FROM $table_name as rk ".
               "LEFT JOIN $table_user as users on rk.vorlage_id = users.ID ".
               "LEFT JOIN $table_user as users2 on rk.vorlage_id_optional = users2.ID ".
               "LEFT JOIN $table_company as c on rk.company_id = c.id ".
               "LEFT JOIN $table_companygroup as cg on rk.companygroup_id = cg.id ";
        $where = [];
        if ($onlymine) {
            $where[] = "rk.creator_user_id = '" . get_current_user_id() . "'";
        }
        
        // Suchbedingungen hinzufügen
        if (!empty($search)) {
            $search_conditions = [];
            $escaped_search = esc_sql($search);
            
            switch($column) {
                case 'all':
                    // Einfache LIKE-Suche für alle Felder
                    $search_conditions[] = "rk.code LIKE '%$escaped_search%'";
                    $search_conditions[] = "users.display_name LIKE '%$escaped_search%'";
                    $search_conditions[] = "users2.display_name LIKE '%$escaped_search%'";
                    $search_conditions[] = "rk.count_code LIKE '%$escaped_search%'";
                    $search_conditions[] = "c.name LIKE '%$escaped_search%'";
                    $search_conditions[] = "cg.name LIKE '%$escaped_search%'";
                    $search_conditions[] = "rk.studygroup_id LIKE '%$escaped_search%'";
                    $search_conditions[] = "DATE_FORMAT(rk.create_date, '%d.%m.%Y %H:%i') LIKE '%$escaped_search%'";
                    break;
                case 'code':
                    $search_conditions[] = "rk.code LIKE '%$escaped_search%'";
                    break;
                case 'vorlagen':
                    $search_conditions[] = "users.display_name LIKE '%$escaped_search%'";
                    $search_conditions[] = "users2.display_name LIKE '%$escaped_search%'";
                    break;
                case 'count_code':
                    $search_conditions[] = "rk.count_code LIKE '%$escaped_search%'";
                    break;
                case 'company_name':
                    $search_conditions[] = "c.name LIKE '%$escaped_search%'";
                    break;
                case 'companygroup_name':
                    $search_conditions[] = "cg.name LIKE '%$escaped_search%'";
                    break;
                case 'studygroup_id':
                    $search_conditions[] = "rk.studygroup_id LIKE '%$escaped_search%'";
                    break;
                case 'create_date':
                    $search_conditions[] = "DATE_FORMAT(rk.create_date, '%d.%m.%Y %H:%i') LIKE '%$escaped_search%'";
                    break;
            }
            
            if (!empty($search_conditions)) {
                $where[] = "(" . implode(" OR ", $search_conditions) . ")";
            }
        }
        
        if (!empty($where)) {
            $sql .= " WHERE " . implode(" AND ", $where);
        }
        $sql .= $wpdb->prepare(" ORDER BY rk.create_date DESC LIMIT %d OFFSET %d", $limit, $offset);
        $data = $wpdb->get_results($sql, OBJECT);
        // Zusätzliche Felder
        $register_url = wp_registration_url();
        foreach ($data as $row) {
            $row->registerlink = add_query_arg("code", $row->code, $register_url);
            $row->countCode = $row->count_code;
            $row->maxCount = $row->max_count;
            
            if (empty($row->display_name) && !empty($row->vorlage_id)) {
                $user = get_user_by('ID', $row->vorlage_id);
                $row->vorlagennutzerName = $user ? $user->display_name : '';
            } else {
                $row->vorlagennutzerName = $row->display_name;
            }
            
            if (empty($row->display_name2) && !empty($row->vorlage_id_optional)) {
                $user2 = get_user_by('ID', $row->vorlage_id_optional);
                $row->vorlagennutzerName2 = $user2 ? $user2->display_name : '';
            } else {
                $row->vorlagennutzerName2 = $row->display_name2;
            }
            
            $row->vorlageid = $row->vorlage_id;
        }
        return $data;
    }

    /**
     * Zählt alle RegisterKeys (optional: nur eigene, optional: mit Suchfilter)
     * @param bool $onlymine
     * @param string $search Suchbegriff
     * @param string $column Spalte für die Suche
     * @return int
     */
    public function countAll($onlymine = false, $search = '', $column = 'all') {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $table_user = $wpdb->prefix . "users";
        $table_company = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanyDao::$dbprefix;
        $table_companygroup = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;

        $sql = "SELECT COUNT(*) FROM $table_name as rk ".
               "LEFT JOIN $table_user as users on rk.vorlage_id = users.ID ".
               "LEFT JOIN $table_user as users2 on rk.vorlage_id_optional = users2.ID ".
               "LEFT JOIN $table_company as c on rk.company_id = c.id ".
               "LEFT JOIN $table_companygroup as cg on rk.companygroup_id = cg.id ";
        
        $where = [];
        if ($onlymine) {
            $where[] = "rk.creator_user_id = '" . get_current_user_id() . "'";
        }
        
        // Suchbedingungen hinzufügen
        if (!empty($search)) {
            $search_conditions = [];
            $escaped_search = esc_sql($search);
            
            switch($column) {
                case 'all':
                    // Einfache LIKE-Suche für alle Felder
                    $search_conditions[] = "rk.code LIKE '%$escaped_search%'";
                    $search_conditions[] = "users.display_name LIKE '%$escaped_search%'";
                    $search_conditions[] = "users2.display_name LIKE '%$escaped_search%'";
                    $search_conditions[] = "rk.count_code LIKE '%$escaped_search%'";
                    $search_conditions[] = "c.name LIKE '%$escaped_search%'";
                    $search_conditions[] = "cg.name LIKE '%$escaped_search%'";
                    $search_conditions[] = "rk.studygroup_id LIKE '%$escaped_search%'";
                    $search_conditions[] = "DATE_FORMAT(rk.create_date, '%d.%m.%Y %H:%i') LIKE '%$escaped_search%'";
                    break;
                case 'code':
                    $search_conditions[] = "rk.code LIKE '%$escaped_search%'";
                    break;
                case 'vorlagen':
                    $search_conditions[] = "users.display_name LIKE '%$escaped_search%'";
                    $search_conditions[] = "users2.display_name LIKE '%$escaped_search%'";
                    break;
                case 'count_code':
                    $search_conditions[] = "rk.count_code LIKE '%$escaped_search%'";
                    break;
                case 'company_name':
                    $search_conditions[] = "c.name LIKE '%$escaped_search%'";
                    break;
                case 'companygroup_name':
                    $search_conditions[] = "cg.name LIKE '%$escaped_search%'";
                    break;
                case 'studygroup_id':
                    $search_conditions[] = "rk.studygroup_id LIKE '%$escaped_search%'";
                    break;
                case 'create_date':
                    $search_conditions[] = "DATE_FORMAT(rk.create_date, '%d.%m.%Y %H:%i') LIKE '%$escaped_search%'";
                    break;
            }
            
            if (!empty($search_conditions)) {
                $where[] = "(" . implode(" OR ", $search_conditions) . ")";
            }
        }
        
        if (!empty($where)) {
            $sql .= " WHERE " . implode(" AND ", $where);
        }
        
        return (int)$wpdb->get_var($sql);
    }
} 