<?php
/**
 * Post Query Class — fetches posts per category for a given month
 */

if (!defined('ABSPATH')) {
    exit;
}

class Thalim_NL_Post_Query {

    /**
     * Window type per category ID (special cases).
     * - datetime_to_fin     : window = [datetime ∥ post_date, date_de_fin ∥ datetime ∥ post_date]
     * - debut_minus35_to_fin: window = [date_de_debut - 35d ∥ post_date - 35d, date_de_fin ∥ date_de_debut ∥ post_date]
     * - datetime_plus3m     : window = [datetime ∥ post_date, datetime ∥ post_date + 3 months]
     *
     * All other categories use 'datetime_plus35d':
     *   window = [datetime ∥ post_date, datetime ∥ post_date + 35 days]
     */
    private const SPECIAL_WINDOW_TYPES = [
        THALIM_NL_CAT_APPELS      => 'datetime_to_fin',
        THALIM_NL_CAT_COLLOQUES   => 'debut_minus35_to_fin',
        THALIM_NL_CAT_SEMINAIRES  => 'debut_minus35_to_fin',
        THALIM_NL_CAT_COMMS       => 'debut_minus35_to_fin',
        THALIM_NL_CAT_SOUTENANCES => 'datetime_to_fin',
        THALIM_NL_CAT_OUVRAGES    => 'datetime_plus3m',
        THALIM_NL_CAT_ARTICLES    => 'datetime_plus3m',
    ];

    private const DEFAULT_WINDOW_TYPE = 'datetime_plus35d';

    /** Categories to exclude from the newsletter UI */
    private const EXCLUDED_CATS = [
        12, // Séance de séminaire
        20, // Newsletter
        31, // Non classé
    ];

    /**
     * Get all newsletter-eligible categories, grouped by parent.
     * Returns [ parent_id => ['name' => string, 'children' => [cat_id => name, ...]], ... ]
     */
    public static function get_eligible_categories(): array {
        $all_cats = get_categories([
            'taxonomy'   => 'category',
            'hide_empty' => false,
            'orderby'    => 'term_id',
            'order'      => 'ASC',
        ]);

        $by_parent = [];
        $parents   = [];

        foreach ($all_cats as $cat) {
            if (in_array($cat->term_id, self::EXCLUDED_CATS, true)) {
                continue;
            }
            if ($cat->parent == 0) {
                $parents[$cat->term_id] = $cat->name;
            }
        }

        foreach ($parents as $pid => $pname) {
            $by_parent[$pid] = ['name' => $pname, 'children' => []];
        }

        foreach ($all_cats as $cat) {
            if (in_array($cat->term_id, self::EXCLUDED_CATS, true)) {
                continue;
            }
            if ($cat->parent == 0) {
                continue; // parents handled above
            }
            $p = $cat->parent;
            if (!isset($by_parent[$p])) {
                continue;
            }
            $by_parent[$p]['children'][$cat->term_id] = $cat->name;
        }

        return $by_parent;
    }

    /**
     * Flat list of all eligible category IDs (excluding EXCLUDED_CATS).
     */
    public static function get_all_eligible_cat_ids(): array {
        $groups = self::get_eligible_categories();
        $ids = [];
        foreach ($groups as $pid => $group) {
            $ids[] = $pid;
            foreach ($group['children'] as $cid => $name) {
                $ids[] = $cid;
            }
        }
        return $ids;
    }

    /**
     * Get the window type for a given category.
     */
    public static function get_window_type(int $cat_id): string {
        return self::SPECIAL_WINDOW_TYPES[$cat_id] ?? self::DEFAULT_WINDOW_TYPE;
    }

    /**
     * Get posts grouped by category for the given year-month (e.g. "2026-03").
     *
     * @param string $year_month  Format: YYYY-MM
     * @return array  ['cat_id' => [post_data, ...], ...]
     */
    public function get_posts_for_month(string $year_month): array {
        $month_start = strtotime($year_month . '-01 00:00:00');
        if (!$month_start) {
            return [];
        }
        $month_end = strtotime('last day of ' . $year_month . ' 23:59:59');

        $result = [];
        foreach (self::get_all_eligible_cat_ids() as $cat_id) {
            $window_type = self::get_window_type($cat_id);
            $posts = $this->query_category($cat_id, $window_type, $month_start, $month_end);
            if (!empty($posts)) {
                $result[$cat_id] = $posts;
            }
        }
        return $result;
    }

    /**
     * Query a single category with the appropriate window expression.
     */
    private function query_category(int $cat_id, string $window_type, int $month_start, int $month_end): array {
        global $wpdb;

        // Build window SQL expressions (UNIX_TIMESTAMP values for comparison)
        switch ($window_type) {
            case 'datetime_to_fin':
                $start_expr = $this->sql_datetime_start();
                $end_expr   = $this->sql_datetime_to_fin_end();
                $order_expr = "COALESCE(NULLIF(pm_dt.meta_value, ''), p.post_date)";
                break;

            case 'debut_minus35_to_fin':
                $start_expr = $this->sql_debut_minus35_start();
                $end_expr   = $this->sql_debut_to_fin_end();
                $order_expr = "COALESCE(NULLIF(pm_deb.meta_value, ''), p.post_date)";
                break;

            case 'datetime_plus3m':
                $start_expr = $this->sql_datetime_start();
                $end_expr   = $this->sql_datetime_plus3m_end();
                $order_expr = "COALESCE(NULLIF(pm_dt.meta_value, ''), p.post_date)";
                break;

            case 'datetime_plus35d':
                $start_expr = $this->sql_datetime_start();
                $end_expr   = $this->sql_datetime_plus35d_end();
                $order_expr = "COALESCE(NULLIF(pm_dt.meta_value, ''), p.post_date)";
                break;

            default:
                return [];
        }

        $sql = $wpdb->prepare(
            "SELECT DISTINCT p.ID, p.post_title, p.post_date
             FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->term_relationships} tr  ON tr.object_id = p.ID
             INNER JOIN {$wpdb->term_taxonomy}      tt  ON tt.term_taxonomy_id = tr.term_taxonomy_id
                 AND tt.taxonomy = 'category' AND tt.term_id = %d
             LEFT JOIN {$wpdb->postmeta} pm_dt  ON pm_dt.post_id  = p.ID AND pm_dt.meta_key  = 'datetime'
             LEFT JOIN {$wpdb->postmeta} pm_deb ON pm_deb.post_id = p.ID AND pm_deb.meta_key = 'date_de_debut'
             LEFT JOIN {$wpdb->postmeta} pm_fin ON pm_fin.post_id = p.ID AND pm_fin.meta_key = 'date_de_fin'
             WHERE p.post_type   = 'post'
               AND p.post_status = 'publish'
               AND {$start_expr} <= %d
               AND {$end_expr}   >= %d
             ORDER BY {$order_expr} ASC",
            $cat_id,
            $month_end,
            $month_start
        );

        $rows = $wpdb->get_results($sql);
        if (!$rows) {
            return [];
        }

        $posts = [];
        foreach ($rows as $row) {
            $posts[] = $this->build_post_data((int) $row->ID, $row->post_title, $row->post_date, $month_start, $month_end);
        }
        return $posts;
    }

    // -------------------------------------------------------------------------
    // SQL window expression helpers (return raw SQL strings, not prepared)
    // -------------------------------------------------------------------------

    /**
     * UNIX_TIMESTAMP of: datetime meta if valid, else post_date
     */
    private function sql_datetime_start(): string {
        return "UNIX_TIMESTAMP(CASE
            WHEN pm_dt.meta_value IS NOT NULL
                 AND pm_dt.meta_value != ''
                 AND LEFT(pm_dt.meta_value, 4) != '0000'
            THEN pm_dt.meta_value
            ELSE p.post_date
        END)";
    }

    /**
     * UNIX_TIMESTAMP of: date_de_fin if valid, else datetime if valid, else post_date
     */
    private function sql_datetime_to_fin_end(): string {
        return "UNIX_TIMESTAMP(CASE
            WHEN pm_fin.meta_value IS NOT NULL
                 AND pm_fin.meta_value != ''
                 AND LEFT(pm_fin.meta_value, 4) != '0000'
            THEN pm_fin.meta_value
            WHEN pm_dt.meta_value IS NOT NULL
                 AND pm_dt.meta_value != ''
                 AND LEFT(pm_dt.meta_value, 4) != '0000'
            THEN pm_dt.meta_value
            ELSE p.post_date
        END)";
    }

    /**
     * UNIX_TIMESTAMP of (date_de_debut if valid, else post_date) minus 35 days (3024000 seconds)
     */
    private function sql_debut_minus35_start(): string {
        return "(UNIX_TIMESTAMP(CASE
            WHEN pm_deb.meta_value IS NOT NULL
                 AND pm_deb.meta_value != ''
                 AND LEFT(pm_deb.meta_value, 4) != '0000'
            THEN pm_deb.meta_value
            ELSE p.post_date
        END) - 3024000)";
    }

    /**
     * UNIX_TIMESTAMP of: date_de_fin if valid, else date_de_debut if valid, else post_date
     */
    private function sql_debut_to_fin_end(): string {
        return "UNIX_TIMESTAMP(CASE
            WHEN pm_fin.meta_value IS NOT NULL
                 AND pm_fin.meta_value != ''
                 AND LEFT(pm_fin.meta_value, 4) != '0000'
            THEN pm_fin.meta_value
            WHEN pm_deb.meta_value IS NOT NULL
                 AND pm_deb.meta_value != ''
                 AND LEFT(pm_deb.meta_value, 4) != '0000'
            THEN pm_deb.meta_value
            ELSE p.post_date
        END)";
    }

    /**
     * UNIX_TIMESTAMP of (datetime if valid, else post_date) + 3 months
     */
    private function sql_datetime_plus3m_end(): string {
        return "UNIX_TIMESTAMP(DATE_ADD(CASE
            WHEN pm_dt.meta_value IS NOT NULL
                 AND pm_dt.meta_value != ''
                 AND LEFT(pm_dt.meta_value, 4) != '0000'
            THEN pm_dt.meta_value
            ELSE p.post_date
        END, INTERVAL 3 MONTH))";
    }

    /**
     * UNIX_TIMESTAMP of (datetime if valid, else post_date) + 35 days
     */
    private function sql_datetime_plus35d_end(): string {
        return "(UNIX_TIMESTAMP(CASE
            WHEN pm_dt.meta_value IS NOT NULL
                 AND pm_dt.meta_value != ''
                 AND LEFT(pm_dt.meta_value, 4) != '0000'
            THEN pm_dt.meta_value
            ELSE p.post_date
        END) + 3024000)";
    }

    // -------------------------------------------------------------------------
    // Post data builder
    // -------------------------------------------------------------------------

    private function build_post_data(int $post_id, string $post_title, string $post_date, int $month_start = 0, int $month_end = 0): array {
        $data = [
            'id'             => $post_id,
            'title'          => $post_title,
            'permalink'      => get_permalink($post_id),
            'datetime'       => get_post_meta($post_id, 'datetime',       true) ?: '',
            'date_debut'     => get_post_meta($post_id, 'date_de_debut',  true) ?: '',
            'date_fin'       => get_post_meta($post_id, 'date_de_fin',    true) ?: '',
            'post_date'      => $post_date,
            'membres'        => $this->get_post_membres($post_id),
            'autrepersonnes' => get_post_meta($post_id, 'autrepersonnes', true) ?: '',
            'seances'        => [],
        ];

        // For seminars, fetch séances within the month window
        if ($month_start && $month_end && has_category(THALIM_NL_CAT_SEMINAIRES, $post_id)) {
            $data['seances'] = $this->get_seances_in_window($post_id, $month_start, $month_end);
        }

        return $data;
    }

    /**
     * Fetch séances linked to a seminar that fall within the given time window.
     */
    private function get_seances_in_window(int $seminar_id, int $month_start, int $month_end): array {
        $seance_ids = get_post_meta($seminar_id, 'seances', false);
        if (empty($seance_ids)) {
            return [];
        }

        $seances = [];
        foreach ($seance_ids as $sid) {
            $sid = (int) $sid;
            $post = get_post($sid);
            if (!$post || $post->post_status !== 'publish') {
                continue;
            }

            $raw_debut = get_post_meta($sid, 'date_de_debut', true) ?: '';
            $ts = $raw_debut ? strtotime($raw_debut) : false;
            if (!$ts || $ts < $month_start || $ts > $month_end) {
                continue;
            }

            $seances[] = [
                'id'             => $sid,
                'title'          => get_the_title($sid),
                'date_debut'     => $raw_debut,
                'date_fin'       => get_post_meta($sid, 'date_de_fin', true) ?: '',
                'heure_de_debut' => substr(get_post_meta($sid, 'heure_de_debut', true) ?: '', 0, 5),
                'heure_de_fin'   => substr(get_post_meta($sid, 'heure_de_fin', true) ?: '', 0, 5),
                'lieu'           => get_post_meta($sid, 'lieu', true) ?: '',
            ];
        }

        // Sort by date_de_debut ascending
        usort($seances, function ($a, $b) {
            return strcmp($a['date_debut'], $b['date_debut']);
        });

        return $seances;
    }

    /**
     * Resolve 'membres' postmeta rows to display names.
     *
     * @return string[]
     */
    public function get_post_membres(int $post_id): array {
        $uids  = get_post_meta($post_id, 'membres', false);
        $names = [];
        foreach ($uids as $uid) {
            $user = get_userdata((int) $uid);
            if ($user) {
                $names[] = $user->display_name;
            }
        }
        return $names;
    }
}