<?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_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';

    /**
     * Séminaires (cat 11) are not selected as whole posts: instead, each of
     * their séances (cat 12) is individually selectable. A séance is eligible
     * when its date_de_debut falls within the newsletter month, extended by
     * this margin (in days) past the end of the month.
     */
    private const SEANCE_WINDOW_MARGIN_DAYS = 5;

    /**
     * Categories to exclude from the newsletter UI, résolues par slug
     * (fallback sur les IDs historiques si un slug est introuvable).
     */
    private static function excluded_cats(): array {
        static $ids = null;
        if ($ids === null) {
            $map = [
                'vie-du-labo-intranet' => 9,  // Vie du labo (intranet)
                'seance-de-seminaire'  => 12, // Séance de séminaire
                'newsletter'           => 20, // Newsletter
                'non-classe'           => 31, // Non classé
            ];
            $ids = [];
            foreach ($map as $slug => $fallback) {
                $term  = get_term_by('slug', $slug, 'category');
                $ids[] = $term ? (int) $term->term_id : $fallback;
            }
        }
        return $ids;
    }

    /**
     * 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) {
            // Seminars: list individually-selectable séances grouped by seminar.
            if ($cat_id === THALIM_NL_CAT_SEMINAIRES) {
                $seances = $this->query_seminar_seances($month_start, $month_end);
                if (!empty($seances)) {
                    $result[$cat_id] = $seances;
                }
                continue;
            }

            $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;
    }

    /**
     * Build the flat list of selectable séances for the seminar category.
     *
     * We walk every published seminar (cat 11), read its `seances` meta
     * (array of séance post IDs), and keep the séances whose date_de_debut
     * falls within [month_start, month_end + SEANCE_WINDOW_MARGIN_DAYS].
     * Each returned item carries its parent seminar (id/title/permalink) so
     * the UI and the exporter can group séances under their seminar.
     *
     * @return array  Flat list of séance items (see shape below).
     */
    private function query_seminar_seances(int $month_start, int $month_end): array {
        global $wpdb;

        $window_end = $month_end + (self::SEANCE_WINDOW_MARGIN_DAYS * DAY_IN_SECONDS);

        $seminar_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT p.ID
             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
             WHERE p.post_type = 'post' AND p.post_status = 'publish'",
            THALIM_NL_CAT_SEMINAIRES
        ));

        $items = [];
        foreach ($seminar_ids as $seminar_id) {
            $seminar_id        = (int) $seminar_id;
            $seminar_title     = get_the_title($seminar_id);
            $seminar_permalink = get_permalink($seminar_id);

            foreach (get_post_meta($seminar_id, 'seances', false) as $sid) {
                $sid    = (int) $sid;
                $s_post = get_post($sid);
                if (!$s_post || $s_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 > $window_end) {
                    continue;
                }

                $items[] = [
                    'id'                => $sid,
                    'title'             => get_the_title($sid),
                    // Links straight to the séance anchor on the seminar page.
                    'permalink'         => $seminar_permalink . '#seance-' . $sid,
                    'datetime'          => '',
                    'date_debut'        => $raw_debut,
                    'date_fin'          => get_post_meta($sid, 'date_de_fin', true) ?: '',
                    'post_date'         => $s_post->post_date,
                    '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) ?: '',
                    'membres'           => $this->get_post_membres($sid),
                    'autrepersonnes'    => get_post_meta($sid, 'autrepersonnes', true) ?: '',
                    'seminar_id'        => $seminar_id,
                    'seminar_title'     => $seminar_title,
                    'seminar_permalink' => $seminar_permalink,
                ];
            }
        }

        // Sort by séance date: seminars naturally order by their earliest séance
        // when grouped by first appearance (see render / export grouping).
        usort($items, fn($a, $b) => strcmp($a['date_debut'], $b['date_debut']));

        return $items;
    }

    /**
     * Resolve the parent seminar (cat 11) that lists a given séance in its
     * `seances` meta. Mirrors the reverse lookup used by the theme's
     * #seance-{ID} redirect. Returns 0 when none is found.
     */
    public static function get_seminar_id_for_seance(int $seance_id): int {
        global $wpdb;

        $parent_id = $wpdb->get_var($wpdb->prepare(
            "SELECT pm.post_id
             FROM {$wpdb->postmeta} pm
             JOIN {$wpdb->posts} p ON p.ID = pm.post_id
             WHERE pm.meta_key = 'seances' AND pm.meta_value = %s
               AND p.post_status = 'publish'
             LIMIT 1",
            (string) $seance_id
        ));

        return (int) $parent_id;
    }

    /**
     * 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);
        }
        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): array {
        return [
            '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) ?: '',
        ];
    }

    /**
     * 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;
    }
}