<?php
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class RR_Data {

    private $meta_table = 'of47_wp_rosins_restaurants_meta';
    private $post_type  = 'rosins-restaurants';
    private $taxonomy   = 'region';
    private $cache_ttl  = 3600; // 1 Stunde
    private $table_format = null; // 'jet' oder 'wp'

    /**
     * Erkennt das Tabellenformat:
     * - JetEngine custom tables haben eigene Spalten pro Feld (staffel, status, etc.)
     * - WP-Style hat meta_key / meta_value Spalten
     */
    private function detect_table_format() {
        if ( null !== $this->table_format ) {
            return $this->table_format;
        }

        global $wpdb;
        $columns = $wpdb->get_results( "SHOW COLUMNS FROM {$this->meta_table}" );

        if ( empty( $columns ) ) {
            $this->table_format = 'wp';
            return $this->table_format;
        }

        $col_names = array_map( function( $c ) { return $c->Field; }, $columns );

        // JetEngine custom tables haben die Feldnamen direkt als Spalten
        if ( in_array( 'status', $col_names, true ) ) {
            $this->table_format = 'jet';
        } else {
            $this->table_format = 'wp';
        }

        return $this->table_format;
    }

    /**
     * Build a lookup: post_id => [ field_name => value ]
     */
    private function build_meta_map() {
        $cached = get_transient( 'rr_meta_map' );
        if ( false !== $cached ) {
            return $cached;
        }

        global $wpdb;
        $posts_table = $wpdb->posts;
        $format = $this->detect_table_format();
        $map = [];

        if ( 'jet' === $format ) {
            // JetEngine: Spalten sind direkt die Feldnamen
            $results = $wpdb->get_results( $wpdb->prepare(
                "SELECT p.ID as post_id, p.post_title, m.*
                 FROM {$posts_table} p
                 INNER JOIN {$this->meta_table} m ON p.ID = m.object_ID
                 WHERE p.post_type = %s AND p.post_status = 'publish'",
                $this->post_type
            ) );

            foreach ( $results as $row ) {
                $row_arr = (array) $row;
                $pid = $row_arr['post_id'];
                unset( $row_arr['post_id'], $row_arr['post_title'], $row_arr['meta_ID'], $row_arr['object_ID'] );
                $map[ $pid ] = $row_arr;
                $map[ $pid ]['_title'] = $row->post_title;
            }
        } else {
            // WP-Style: meta_key / meta_value
            $results = $wpdb->get_results( $wpdb->prepare(
                "SELECT p.ID as post_id, p.post_title, m.meta_key, m.meta_value
                 FROM {$posts_table} p
                 INNER JOIN {$this->meta_table} m ON p.ID = m.post_id
                 WHERE p.post_type = %s AND p.post_status = 'publish'",
                $this->post_type
            ) );

            foreach ( $results as $row ) {
                $map[ $row->post_id ][ $row->meta_key ] = $row->meta_value;
                $map[ $row->post_id ]['_title'] = $row->post_title;
            }
        }

        set_transient( 'rr_meta_map', $map, $this->cache_ttl );
        return $map;
    }

    /**
     * Debug: Rohdaten für Diagnose
     */
    public function get_debug_info() {
        global $wpdb;

        $info = [];

        // Tabellenformat
        $info['table_format'] = $this->detect_table_format();

        // Spalten der Meta-Tabelle
        $columns = $wpdb->get_results( "SHOW COLUMNS FROM {$this->meta_table}" );
        $info['columns'] = $columns ? array_map( function( $c ) { return $c->Field; }, $columns ) : 'TABELLE NICHT GEFUNDEN';

        // Anzahl Zeilen
        $info['meta_row_count'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->meta_table}" );

        // Anzahl publizierte Posts
        $info['published_posts'] = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s AND post_status = 'publish'",
            $this->post_type
        ) );

        // Beispielzeile
        $info['sample_row'] = $wpdb->get_row( "SELECT * FROM {$this->meta_table} LIMIT 1", ARRAY_A );

        // Meta-Map Ergebnis
        // Transients löschen für frische Daten
        $this->clear_cache();
        $map = $this->build_meta_map();
        $info['meta_map_count'] = count( $map );
        $info['meta_map_sample'] = array_slice( $map, 0, 2, true );

        // Status-Werte die gefunden werden
        $info['status_counts'] = $this->get_status_counts();

        return $info;
    }

    /**
     * Cache löschen
     */
    public function clear_cache() {
        delete_transient( 'rr_meta_map' );
        delete_transient( 'rr_status_counts' );
        delete_transient( 'rr_staffel_status' );
        delete_transient( 'rr_testessen_verb' );
        delete_transient( 'rr_region_counts' );
        delete_transient( 'rr_region_status' );
        delete_transient( 'rr_staffel_avg' );
        delete_transient( 'rr_all_meta' );
    }

    /**
     * Normalisiert den Status-Wert für Vergleiche
     */
    private function normalize_status( $raw ) {
        $s = mb_strtolower( trim( $raw ) );
        // Verschiedene mögliche Schreibweisen abfangen
        if ( in_array( $s, [ 'geöffnet', 'geoeffnet', 'offen', 'geoffnet' ], true ) ) {
            return 'geöffnet';
        }
        if ( in_array( $s, [ 'geschlossen', 'zu', 'closed' ], true ) ) {
            return 'geschlossen';
        }
        if ( false !== strpos( $s, 'neuer' ) || false !== strpos( $s, 'betreiber' ) || false !== strpos( $s, 'new' ) ) {
            return 'neuer Betreiber';
        }
        return $s;
    }

    /**
     * 1. Status-Verteilung
     */
    public function get_status_counts() {
        $cached = get_transient( 'rr_status_counts' );
        if ( false !== $cached ) {
            return $cached;
        }

        $map    = $this->build_meta_map();
        $counts = [
            'geöffnet'        => 0,
            'geschlossen'     => 0,
            'neuer Betreiber' => 0,
        ];

        foreach ( $map as $meta ) {
            $status = $this->normalize_status( $meta['status'] ?? '' );
            if ( isset( $counts[ $status ] ) ) {
                $counts[ $status ]++;
            }
        }

        set_transient( 'rr_status_counts', $counts, $this->cache_ttl );
        return $counts;
    }

    /**
     * 2. Status pro Staffel
     */
    public function get_staffel_status() {
        $cached = get_transient( 'rr_staffel_status' );
        if ( false !== $cached ) {
            return $cached;
        }

        $map    = $this->build_meta_map();
        $result = [];

        foreach ( $map as $meta ) {
            $staffel = trim( $meta['staffel'] ?? '' );
            $status  = $this->normalize_status( $meta['status'] ?? '' );
            if ( '' === $staffel || '' === $status ) {
                continue;
            }
            if ( ! isset( $result[ $staffel ] ) ) {
                $result[ $staffel ] = [
                    'geöffnet'        => 0,
                    'geschlossen'     => 0,
                    'neuer Betreiber' => 0,
                ];
            }
            if ( isset( $result[ $staffel ][ $status ] ) ) {
                $result[ $staffel ][ $status ]++;
            }
        }

        ksort( $result, SORT_NUMERIC );
        set_transient( 'rr_staffel_status', $result, $this->cache_ttl );
        return $result;
    }

    /**
     * 3. Testessen-Verbesserung
     */
    public function get_testessen_verbesserung() {
        $cached = get_transient( 'rr_testessen_verb' );
        if ( false !== $cached ) {
            return $cached;
        }

        $map    = $this->build_meta_map();
        $result = [];

        foreach ( $map as $post_id => $meta ) {
            $e1 = floatval( $meta['ergebnis_1_testessen_normalisiert'] ?? 0 );
            $e2 = floatval( $meta['ergebnis_2_testessen_normalisiert'] ?? 0 );
            if ( $e1 <= 0 && $e2 <= 0 ) {
                continue;
            }
            $staffel = trim( $meta['staffel'] ?? '?' );
            $result[] = [
                'post_id'  => $post_id,
                'staffel'  => $staffel,
                'test1'    => $e1,
                'test2'    => $e2,
            ];
        }

        set_transient( 'rr_testessen_verb', $result, $this->cache_ttl );
        return $result;
    }

    /**
     * 4. Restaurants pro Region
     */
    public function get_region_counts() {
        $cached = get_transient( 'rr_region_counts' );
        if ( false !== $cached ) {
            return $cached;
        }

        $terms = get_terms( [
            'taxonomy'   => $this->taxonomy,
            'hide_empty' => true,
        ] );

        if ( is_wp_error( $terms ) ) {
            return [];
        }

        $result = [];
        foreach ( $terms as $term ) {
            $result[ $term->name ] = (int) $term->count;
        }

        arsort( $result );
        set_transient( 'rr_region_counts', $result, $this->cache_ttl );
        return $result;
    }

    /**
     * 5. Status pro Region
     */
    public function get_region_status() {
        $cached = get_transient( 'rr_region_status' );
        if ( false !== $cached ) {
            return $cached;
        }

        $terms = get_terms( [
            'taxonomy'   => $this->taxonomy,
            'hide_empty' => true,
        ] );

        if ( is_wp_error( $terms ) ) {
            return [];
        }

        $meta_map = $this->build_meta_map();
        $result   = [];

        foreach ( $terms as $term ) {
            $post_ids = get_objects_in_term( $term->term_id, $this->taxonomy );
            if ( is_wp_error( $post_ids ) ) {
                continue;
            }

            $counts = [
                'geöffnet'        => 0,
                'geschlossen'     => 0,
                'neuer Betreiber' => 0,
            ];

            foreach ( $post_ids as $pid ) {
                $status = $this->normalize_status( $meta_map[ $pid ]['status'] ?? '' );
                if ( isset( $counts[ $status ] ) ) {
                    $counts[ $status ]++;
                }
            }

            $result[ $term->name ] = $counts;
        }

        set_transient( 'rr_region_status', $result, $this->cache_ttl );
        return $result;
    }

    /**
     * 6. Durchschnittliche Testessen-Ergebnisse pro Staffel
     */
    public function get_staffel_ergebnis_avg() {
        $cached = get_transient( 'rr_staffel_avg' );
        if ( false !== $cached ) {
            return $cached;
        }

        $map  = $this->build_meta_map();
        $sums = [];

        foreach ( $map as $meta ) {
            $staffel = trim( $meta['staffel'] ?? '' );
            if ( '' === $staffel ) {
                continue;
            }

            $e1 = floatval( $meta['ergebnis_1_testessen_normalisiert'] ?? 0 );
            $e2 = floatval( $meta['ergebnis_2_testessen_normalisiert'] ?? 0 );

            if ( ! isset( $sums[ $staffel ] ) ) {
                $sums[ $staffel ] = [ 'sum1' => 0, 'sum2' => 0, 'count1' => 0, 'count2' => 0 ];
            }

            if ( $e1 > 0 ) {
                $sums[ $staffel ]['sum1'] += $e1;
                $sums[ $staffel ]['count1']++;
            }
            if ( $e2 > 0 ) {
                $sums[ $staffel ]['sum2'] += $e2;
                $sums[ $staffel ]['count2']++;
            }
        }

        $result = [];
        ksort( $sums, SORT_NUMERIC );
        foreach ( $sums as $staffel => $s ) {
            $result[ $staffel ] = [
                'avg1' => $s['count1'] > 0 ? round( $s['sum1'] / $s['count1'], 2 ) : 0,
                'avg2' => $s['count2'] > 0 ? round( $s['sum2'] / $s['count2'], 2 ) : 0,
            ];
        }

        set_transient( 'rr_staffel_avg', $result, $this->cache_ttl );
        return $result;
    }
}