<?php
/**
 * Quotes Data Handler Class
 * 
 * Optimized data handler with direct SQL queries for maximum performance.
 * 
 * @package EasyQuotes
 * @since 1.0.0
 */
declare(strict_types=1);

// Exit if accessed directly.
if (!defined('ABSPATH')) {
    exit;
}

class Easy_Quotes_Data
{
    function __construct() {
        add_action('rest_api_init', [$this, 'rest_api_init'] );
    }

    function rest_api_init() {
        register_rest_route('easy-quotes/v1', '/quotes', [
            'methods' => 'GET',
            'callback' => [$this, 'get_quotes_rest'],
            'permission_callback' => function() {
                return current_user_can('edit_posts');
            }
        ]);
    }

    /**
     * Get quotes with optional category, limit and random ordering
     * 
     * @param int $category_id Category ID (-1 for all categories)
     * @param int $limit Number of quotes to retrieve (0 for all)
     * @param bool $random Whether to return quotes in random order
     * @return array Array of quote objects with meta data
     */
    public static function get_quotes(int $category_id = -1, int $limit = 0, bool $random = false) {
        global $wpdb;
        
        $order_by = $random ? "RAND()" : "post.post_date DESC";
        
        if ($category_id === -1) {
            // No category filter - simple query
            $sql = $wpdb->prepare("
                SELECT
                    post.ID,
                    post.post_date,
                    post.post_content,
                    post.post_title,
                    IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
                    IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
                    IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating
                FROM %i post
                LEFT JOIN %i meta ON post.ID = meta.post_id
                WHERE post.post_status = 'publish' AND post.post_type = 'quote'
                GROUP BY post.ID
                ORDER BY {$order_by}
            ", $wpdb->posts, $wpdb->postmeta);
        } else {
            // Simple category filter - no recursive children
            $sql = $wpdb->prepare("
                SELECT
                    post.ID,
                    post.post_date,
                    post.post_content,
                    post.post_title,
                    IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
                    IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
                    IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating
                FROM %i post
                LEFT JOIN %i tr ON post.ID = tr.object_id
                LEFT JOIN %i meta ON post.ID = meta.post_id
                WHERE post.post_status = 'publish' 
                  AND post.post_type = 'quote'
                  AND tr.term_taxonomy_id = %d
                GROUP BY post.ID
                ORDER BY {$order_by}
            ", $wpdb->posts, $wpdb->term_relationships, $wpdb->postmeta, $category_id);
        }
        
        // Apply limit if needed
        if ($limit > 0) {
            $sql .= $wpdb->prepare(" LIMIT %d", $limit);
        }

        $results = $wpdb->get_results($sql);
        
        return self::render_post_content($results);
    }

    /**
     * Get fallback quote (helper for get_daily_quotes)
     * 
     * Returns the most recent published quote as a fallback when no quote
     * matches the current date. This ensures there's always a quote to display.
     * 
     * @param int $category_id Category ID (-1 for all categories)
     * @return array Array with single fallback quote object
     */
    private static function get_daily_quotes_fallback(int $category_id)
    {
        global $wpdb;

        $sql = $wpdb->prepare("
            SELECT
                post.ID,
                post.post_date,
                post.post_content,
                post.post_title,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating,
                DATE_FORMAT(post.post_date, '%%m%%d') AS daily
            FROM %i post
            LEFT JOIN %i meta ON post.ID = meta.post_id
            LEFT JOIN %i tr ON post.ID = tr.object_id
            WHERE post.post_status = 'publish'
                AND post.post_type = 'quote'
                AND (%d = -1 OR tr.term_taxonomy_id = %d)
            GROUP BY post.ID
            ORDER BY post.post_date DESC
            LIMIT 1
        ", $wpdb->posts, $wpdb->postmeta, $wpdb->term_relationships, $category_id, $category_id);

        return $wpdb->get_results($sql);
    }

    /**
     * Get quotes within the date window (helper for get_daily_quotes)
     * 
     * Uses a self-join to get only the most recent quote for each MMDD date
     * within the specified date range. Excludes quotes where a newer quote
     * exists with the same MMDD date.
     * 
     * @param int $category_id Category ID (-1 for all categories)
     * @return array Array of quote objects matching the date window
     */
    private static function get_daily_quotes_window(int $category_id) {
        global $wpdb;

        $sql = $wpdb->prepare("
            SELECT
                post.ID,
                post.post_date,
                post.post_content,
                post.post_title,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating,
                DATE_FORMAT(post.post_date, '%%m%%d') AS daily
            FROM %i post
            LEFT JOIN %i meta ON post.ID = meta.post_id
            LEFT JOIN %i tr ON post.ID = tr.object_id
            LEFT JOIN %i newer
                ON DATE_FORMAT(newer.post_date, '%%m%%d') = DATE_FORMAT(post.post_date, '%%m%%d')
                AND newer.post_date > post.post_date
                AND newer.post_status = 'publish'
                AND newer.post_type = 'quote'
            WHERE post.post_status = 'publish'
                AND post.post_type = 'quote'
                AND newer.ID IS NULL
                AND (%d = -1 OR tr.term_taxonomy_id = %d)
                AND (
                    (@start_int <= @end_int AND CAST(DATE_FORMAT(post.post_date,'%%m%%d') AS UNSIGNED)
                        BETWEEN @start_int AND @end_int)
                OR (@start_int > @end_int AND (
                    CAST(DATE_FORMAT(post.post_date,'%%m%%d') AS UNSIGNED) >= @start_int
                    OR CAST(DATE_FORMAT(post.post_date,'%%m%%d') AS UNSIGNED) <= @end_int
                ))
            )
            GROUP BY post.ID
            ORDER BY post.post_date DESC
        ", $wpdb->posts, $wpdb->postmeta, $wpdb->term_relationships, $wpdb->posts, $category_id, $category_id);

        return $wpdb->get_results($sql);
    }

    /**
     * Get daily quotes for the next X days (cache-proof)
     * 
     * Returns:
     * - Index 0: Fallback quote (most recent published)
     * - Index 1+: All quotes matching dates in the next X days
     * 
     * This allows the frontend to work with cached pages by having all possible
     * quotes pre-rendered, then JavaScript selects the correct one based on current date.
     * 
     * @param int $category_id Category ID (-1 for all categories)  
     * @param int $days Number of days to look ahead (default 14)
     * @return array Array of quote objects with meta data
     */
    public static function get_daily_quotes(int $category_id = -1, int $days = 14) {
        global $wpdb;

        // Set rolling window (session vars are fine here)
        $wpdb->query("SET @start_int = DATE_FORMAT(CURDATE(), '%m%d') + 0");
        $wpdb->query(
            $wpdb->prepare(
                "SET @end_int = DATE_FORMAT(CURDATE() + INTERVAL %d DAY, '%%m%%d') + 0",
                $days
            )
        );

        $fallback = self::get_daily_quotes_fallback($category_id);
        $daily    = self::get_daily_quotes_window($category_id);

        // Always row[0] = fallback
        $results = [];

        if (!empty($fallback)) {
            $results[] = $fallback[0];
        }

        if (!empty($daily)) {
            $results = array_merge($results, $daily);
        }

        return self::render_post_content($results);
    }

    /**
     * Get a specific quote by post ID
     * 
     * @param int $post_id Quote post ID
     * @return object|null Quote object with meta data or null
     */
    public static function get_quote(int $post_id): ?object
    {
        if ($post_id <= 0) {
            return null;
        }

        global $wpdb;
        
        $sql = $wpdb->prepare("
            SELECT
                post.ID,
                post.post_date,
                post.post_content,
                post.post_title,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_author' THEN meta.meta_value END), '') AS meta_author,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_date' THEN meta.meta_value END), '') AS meta_date,
                IFNULL(MAX(CASE WHEN meta.meta_key = 'quote_rating' THEN meta.meta_value END), 0) AS meta_rating
            FROM %i post
            LEFT JOIN %i meta ON post.ID = meta.post_id
            WHERE post.ID = %d AND post.post_status = 'publish' AND post.post_type = 'quote'
            GROUP BY post.ID
        ", $wpdb->posts, $wpdb->postmeta, $post_id);
        
        $result = $wpdb->get_row($sql);
        if ($result) {
            $results = [$result];
            $results = self::render_post_content($results);
            return $results[0];
        }
        return null;
    }

    /**
     * Get quote suggestions for autocomplete
     * 
     * @param string $search Search term to filter quotes
     * @param int $limit Maximum number of suggestions to return
     * @return array Array of quote objects with minimal fields
     */
    public static function get_quote_suggestions(string $search = '', int $limit = 8): array
    {
        global $wpdb;
        
        if (empty($search)) {
            // Return recent quotes when no search term provided
            $sql = $wpdb->prepare("
                SELECT
                    post.ID,
                    post.post_title
                FROM %i post
                WHERE post.post_status = 'publish' 
                AND post.post_type = 'quote'
                ORDER BY post.post_date DESC
                LIMIT %d
            ", $wpdb->posts, $limit);
        } else {
            // Return quotes matching search term
            $sql = $wpdb->prepare("
                SELECT
                    post.ID,
                    post.post_title
                FROM %i post
                WHERE post.post_status = 'publish' 
                AND post.post_type = 'quote'
                AND post.post_title LIKE %s
                ORDER BY post.post_date DESC
                LIMIT %d
            ", $wpdb->posts, '%' . $wpdb->esc_like($search) . '%', $limit);
        }
        
        return $wpdb->get_results($sql);
    }

    /**
     * Get quotes for our REST API endpoint
     * 
     * @param WP_REST_Request $request
     * @return WP_REST_Response
     */
    public function get_quotes_rest(WP_REST_Request $request) {
        // Extract parameters
        $mode = $request->get_param('mode') ?: 'all';
        $category_id = (int)($request->get_param('category') ?: -1);
        $limit = (int)($request->get_param('limit') ?: 0);
        $specific_id = (int)($request->get_param('id') ?: 0);
        $search = $request->get_param('search') ?: '';
        
        // Determine which function to call based on mode
        switch ($mode) {
            case 'suggestions':
                // Get title suggestions for autocomplete
                $result = self::get_quote_suggestions($search);
                break;

            case 'random':
                // Get a single random quote
                $result = self::get_quotes($category_id, 1, true);
                break;
                
            case 'daily':
                // Get today's daily quote
                $quotes = self::get_daily_quotes($category_id, 0);
                // $quotes[1] = exact day match / $quotes[0] = fallback to the most recent published in this category
                $result = $quotes[1] ?? $quotes[0] ?? [];
                break;
                
            case 'specific':
                // Get a specific quote by ID
                if ($specific_id > 0) {
                    $result = self::get_quote($specific_id);
                    if (!$result) {
                        return new WP_Error('not_found', 'Quote not found', ['status' => 404]);
                    }
                } else {
                    return new WP_Error('invalid_id', 'Invalid quote ID', ['status' => 400]);
                }
                break;
                
            case 'list':
                // Get a limited list of quotes
                $result = self::get_quotes($category_id, $limit);
                break;
                
            case 'rotation':
            case 'all':
            default:
                // Get all quotes (possibly filtered by category)
                $result = self::get_quotes($category_id);
                break;
        }
        
        // Return the result
        return rest_ensure_response($result);
    }

    /**
     * Adds rendered post content to quote results
     * 
     * @param array $results Quote result objects
     * @return array Modified results with post_content_rendered added
     */
    private static function render_post_content($results) {
        // Add rendered content to each result
        foreach ($results as $quote) {
            $quote->post_content_rendered = wp_kses_post(wpautop(do_shortcode($quote->post_content)));
        }
        
        return $results; // Return the modified results
    }

}