<?php

namespace FleekDash\Controllers\Posts;

use FleekDash\Services\ResponseService;

if (!\function_exists('fleekdash_log_debug')) {
    require_once FLEEKDASH_PLUGIN_DIR . 'includes/Core/debug-helpers.php';
}

/**
 * Helper for Posts Actions - Optimized posts query (legacy CSRM endpoint).
 * Extracted to reduce cognitive complexity in Actions::getPostsOptimized().
 *
 * @package FleekDash\Controllers\Posts
 */
class PostsOptimizedHelper
{
    private const COLUMN_MAP = [
        'id' => 'ID',
        'title' => 'post_title',
        'date' => 'post_date',
        'status' => 'post_status',
        'author' => 'post_author',
    ];

    /**
     * Execute optimized fetch: auth log, query build, execute, format.
     * Returns WP_REST_Response for proper REST API and cacheOrExecute compatibility.
     *
     * @param array<string, mixed> $requestParams
     * @return \WP_REST_Response
     */
    public static function executeOptimizedFetch(array $requestParams): \WP_REST_Response
    {
        try {
            self::logOptimizedAuthResult();
            [$where_clause, $where_values] = self::buildWhereClause($requestParams);
            $order_clause = self::buildOrderClause($requestParams);
            $formatted_rows = self::executeQuery($where_clause, $where_values, $order_clause);

            return \rest_ensure_response(['success' => true, 'data' => $formatted_rows]);
        } catch (\Exception $e) {
            \fleekdash_log_error('CSRM Optimized Posts Error: ' . $e->getMessage() . PHP_EOL . $e->getTraceAsString());

            return ResponseService::error(
                'Failed to fetch optimized posts data: ' . $e->getMessage(),
                null,
                500
            );
        }
    }

    /**
     * Log auth result for optimized posts access (graceful fallback).
     */
    public static function logOptimizedAuthResult(): void
    {
        try {
            $auth_check = \FleekDash\Core\AuthMiddleware::check('editor');
            if (!$auth_check['authenticated']) {
                \fleekdash_log_debug(
                    "FleekDash: Posts access without proper authentication - User ID: " .
                    \get_current_user_id() . " - Reason: " . ($auth_check['reason'] ?? 'Unknown')
                );
            } else {
                \fleekdash_log_debug("FleekDash: Authenticated posts access - User ID: " . $auth_check['user_id']);
            }
        } catch (\Exception $e) {
            \fleekdash_log_error("FleekDash: AuthMiddleware error in posts API: " . $e->getMessage());
        }
    }

    /**
     * Build WHERE clause and values for optimized query.
     *
     * @param array<string, mixed> $params
     * @return array{0: string, 1: array}
     */
    public static function buildWhereClause(array $params): array
    {
        global $wpdb;
        $post_type = $params['post_type'] ?? 'post';
        $status = $params['status'] ?? null;
        $search = $params['search'] ?? null;

        $where_conditions = ["p.post_type = %s"];
        $where_values = [$post_type];

        if ($status && $status !== 'all') {
            $where_conditions[] = "p.post_status = %s";
            $where_values[] = $status;
        } else {
            $where_conditions[] = "p.post_status NOT IN ('auto-draft', 'inherit')";
        }

        if ($search) {
            $where_conditions[] = "(p.post_title LIKE %s OR p.post_content LIKE %s)";
            $escaped = $wpdb->esc_like($search);
            $where_values[] = '%' . $escaped . '%';
            $where_values[] = '%' . $escaped . '%';
        }

        return [\implode(' AND ', $where_conditions), $where_values];
    }

    /**
     * Build ORDER BY clause for optimized query.
     *
     * @param array<string, mixed> $params
     * @return string
     */
    public static function buildOrderClause(array $params): string
    {
        $orderby = $params['orderby'] ?? 'date';
        $order = \strtoupper($params['order'] ?? 'DESC') === 'ASC' ? 'asc' : 'desc';
        $db_column = self::COLUMN_MAP[$orderby] ?? self::COLUMN_MAP['date'];

        return "{$db_column} {$order}";
    }

    /**
     * Execute optimized query and return formatted rows.
     *
     * @param string $where_clause
     * @param array $where_values
     * @param string $order_clause
     * @return array<int, array<string, mixed>>
     */
    public static function executeQuery(string $where_clause, array $where_values, string $order_clause): array
    {
        global $wpdb;
        $sql = "
            SELECT
                p.ID,
                p.post_author,
                p.post_title,
                p.post_content,
                p.post_status,
                p.post_date,
                p.post_type,
                u.display_name as author_name,
                t.name as category_name,
                pm.meta_value as thumbnail_id
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->users} u ON p.post_author = u.ID
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_thumbnail_id'
            LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
            LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'category'
            LEFT JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
            WHERE {$where_clause}
            GROUP BY p.ID
            ORDER BY {$order_clause}
        ";

        $rows = empty($where_values)
            ? $wpdb->get_results($sql, ARRAY_A)
            : $wpdb->get_results($wpdb->prepare($sql, $where_values), ARRAY_A);

        $placeholder_url = \plugin_dir_url(\dirname(\dirname(__DIR__))) . 'public/images/placeholder.svg';

        $result = [];
        foreach ($rows as $post) {
            $result[] = self::formatRow($post, $placeholder_url);
        }

        return $result;
    }

    /**
     * Format a single row for optimized response.
     *
     * @param array<string, mixed> $post
     * @param string $placeholder_url
     * @return array<string, mixed>
     */
    public static function formatRow(array $post, string $placeholder_url): array
    {
        $author_name = $post['author_name'] ?? 'Unknown Author';
        $first_category = $post['category_name'] ?? 'Uncategorized';
        $thumbnail_url = null;
        if (!empty($post['thumbnail_id'])) {
            $thumbnail_url = \wp_get_attachment_image_url((int) $post['thumbnail_id'], 'thumbnail');
        }
        $subtitle = \wp_trim_words(\strip_tags($post['post_content'] ?? ''), 15);

        return [
            'id' => $post['ID'],
            'title' => $post['post_title'],
            'date' => $post['post_date'],
            'author' => $author_name,
            'status' => $post['post_status'],
            'thumb' => $thumbnail_url ?: $placeholder_url,
            'subtitle' => $subtitle,
            'category' => $first_category,
        ];
    }
}
