<?php
/**
 * Copyright (C) 2026 Plugixa (https://plugixa.com)
 * License: GNU General Public License v2 or later
 * License URI: http://www.gnu.org/licenses/gpl-2.0.txt
 */

namespace PlugixaRecipe\Api;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.SlowDBQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom-table data layer: table identifiers are built with $wpdb->prefix and are never user input; all request values are bound via $wpdb->prepare(); ORDER BY / LIMIT fragments come out of internal allow-list maps; content reads go through PlugixaRecipe\Cache (the static sniff cannot trace caching through the wrapper); meta_key/meta_value are dedicated plugin columns, not WP_Query.

defined( 'ABSPATH' ) || exit;

use PlugixaRecipe\Cache;
use WP_REST_Request;
use WP_REST_Response;
use WP_Error;

/**
 * Reviews REST Controller
 */
class ReviewsController extends RestController {

    public function __construct() {
        parent::__construct();
        $this->rest_base = 'reviews';
    }

    public function register_routes(): void {
        register_rest_route($this->namespace, '/' . $this->rest_base, [
            [
                'methods'             => 'GET',
                'callback'            => [$this, 'get_items'],
                'permission_callback' => [$this, 'check_permission'],
                'args'                => $this->get_collection_params(),
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/(?P<id>\d+)', [
            [
                'methods'             => 'GET',
                'callback'            => [$this, 'get_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
            [
                'methods'             => 'PUT',
                'callback'            => [$this, 'update_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
            [
                'methods'             => 'DELETE',
                'callback'            => [$this, 'delete_item'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/(?P<id>\d+)/status', [
            [
                'methods'             => 'PATCH',
                'callback'            => [$this, 'update_status'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/bulk', [
            [
                'methods'             => 'DELETE',
                'callback'            => [$this, 'bulk_delete'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);

        register_rest_route($this->namespace, '/' . $this->rest_base . '/bulk-action', [
            [
                'methods'             => 'POST',
                'callback'            => [$this, 'bulk_action'],
                'permission_callback' => [$this, 'check_permission'],
            ],
        ]);
    }

    /**
     * Get reviews list with filters, includes recipe title via JOIN.
     */
    public function get_items($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix  = $wpdb->prefix . 'plugixarecipe_';
        $filters = $this->get_filters($request);
        $pagination = $this->get_pagination($request);
        $sort = $this->get_sort($request);

        $where  = ['1=1'];
        $values = [];

        if (!empty($filters['search'])) {
            $search = '%' . $wpdb->esc_like($filters['search']) . '%';
            $where[] = '(rv.author_name LIKE %s OR rv.review_text LIKE %s OR rv.author_email LIKE %s)';
            $values[] = $search;
            $values[] = $search;
            $values[] = $search;
        }

        if (!empty($filters['status'])) {
            $where[] = 'rv.status = %s';
            $values[] = $filters['status'];
        }

        if (!empty($filters['recipe_id'])) {
            $where[] = 'rv.recipe_id = %d';
            $values[] = (int) $filters['recipe_id'];
        }

        if (!empty($filters['rating'])) {
            $where[] = 'rv.rating = %d';
            $values[] = (int) $filters['rating'];
        }

        if (!empty($filters['min_rating'])) {
            $where[] = 'rv.rating >= %d';
            $values[] = (int) $filters['min_rating'];
        }

        $where_clause = implode(' AND ', $where);

        $allowed_orderby = ['created_at', 'rating', 'author_name', 'helpful_count'];
        $orderby = in_array($sort['orderby'], $allowed_orderby) ? $sort['orderby'] : 'created_at';
        $order   = $sort['order'] === 'ASC' ? 'ASC' : 'DESC';

        // Count — read-through object cache; key = SQL + bound values (captures every filter).
        $count_sql = "SELECT COUNT(*) FROM {$prefix}reviews rv WHERE {$where_clause}";
        $total = (int) Cache::remember('reviews', 'count|' . $count_sql . '|' . wp_json_encode($values), function () use ($wpdb, $count_sql, $values) {
            return !empty($values)
                ? (int) $wpdb->get_var($wpdb->prepare($count_sql, ...$values))
                : (int) $wpdb->get_var($count_sql);
        });

        // Main query with recipe title
        $sql = "SELECT rv.*, r.title AS recipe_title
                FROM {$prefix}reviews rv
                LEFT JOIN {$prefix}recipes r ON rv.recipe_id = r.id
                WHERE {$where_clause}
                ORDER BY rv.{$orderby} {$order}
                LIMIT %d OFFSET %d";
        $values[] = $pagination['limit'];
        $values[] = $pagination['offset'];

        // Read-through object cache; key = SQL + bound values (captures filters/sort/pagination).
        $items = Cache::remember('reviews', 'list|' . $sql . '|' . wp_json_encode($values), function () use ($wpdb, $sql, $values) {
            return $wpdb->get_results($wpdb->prepare($sql, ...$values), ARRAY_A) ?: [];
        });

        foreach ($items as &$item) {
            $item['rating'] = (int) $item['rating'];
            $item['helpful_count'] = (int) $item['helpful_count'];
            $item['is_verified'] = (bool) $item['is_verified'];
            if (!empty($item['images'])) {
                $item['images'] = json_decode($item['images'], true);
            }
        }

        return $this->success([
            'items'  => $items,
            'total'  => $total,
            'limit'  => $pagination['limit'],
            'offset' => $pagination['offset'],
        ]);
    }

    /**
     * Get single review.
     */
    public function get_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');

        // Read-through object cache; key = review id.
        $item = Cache::remember('reviews', 'item|' . $id, function () use ($wpdb, $prefix, $id) {
            return $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT rv.*, r.title AS recipe_title
                    FROM {$prefix}reviews rv
                    LEFT JOIN {$prefix}recipes r ON rv.recipe_id = r.id
                    WHERE rv.id = %d",
                    $id
                ),
                ARRAY_A
            );
        });

        if (!$item) {
            return $this->error(__('Review not found.', 'plugixarecipe'), 404);
        }

        $item['rating'] = (int) $item['rating'];
        $item['helpful_count'] = (int) $item['helpful_count'];
        $item['is_verified'] = (bool) $item['is_verified'];
        if (!empty($item['images'])) {
            $item['images'] = json_decode($item['images'], true);
        }

        return $this->success($item);
    }

    /**
     * Update a review.
     */
    public function update_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');
        $params = $request->get_json_params();

        $existing = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}reviews WHERE id = %d", $id), ARRAY_A);
        if (!$existing) {
            return $this->error(__('Review not found.', 'plugixarecipe'), 404);
        }

        $data = [];

        if (isset($params['author_name'])) {
            $data['author_name'] = sanitize_text_field($params['author_name']);
        }
        if (isset($params['author_email'])) {
            $data['author_email'] = sanitize_email($params['author_email']);
        }
        if (isset($params['rating'])) {
            $data['rating'] = max(1, min(5, (int) $params['rating']));
        }
        if (isset($params['review_text'])) {
            $data['review_text'] = wp_kses_post($params['review_text']);
        }
        if (isset($params['status'])) {
            $data['status'] = sanitize_text_field($params['status']);
        }
        if (isset($params['is_verified'])) {
            $data['is_verified'] = (int) $params['is_verified'];
        }
        if (isset($params['images'])) {
            $data['images'] = wp_json_encode($params['images']);
        }
        if (isset($params['admin_reply'])) {
            $reply = wp_kses_post( trim( $params['admin_reply'] ) );
            $data['admin_reply'] = $reply ?: null;
            $data['admin_reply_at'] = $reply ? current_time('mysql') : null;
        }

        $data['modified_at'] = current_time('mysql');

        $wpdb->update($prefix . 'reviews', $data, ['id' => $id]);

        // Recalculate recipe rating
        $this->recalculate_recipe_rating((int) $existing['recipe_id']);

        // Content write: a review also changes the recipe's rating aggregate shown in lists.
        Cache::flush('reviews', 'recipes');

        return $this->get_item($request);
    }

    /**
     * Delete a review.
     */
    public function delete_item($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');

        $review = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}reviews WHERE id = %d", $id), ARRAY_A);
        if (!$review) {
            return $this->error(__('Review not found.', 'plugixarecipe'), 404);
        }

        $wpdb->delete($prefix . 'reviews', ['id' => $id]);

        // Recalculate recipe rating
        $this->recalculate_recipe_rating((int) $review['recipe_id']);

        // Content write: a review also changes the recipe's rating aggregate shown in lists.
        Cache::flush('reviews', 'recipes');

        return $this->success(null, __('Review deleted.', 'plugixarecipe'));
    }

    /**
     * Update review status.
     */
    public function update_status($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $id = (int) $request->get_param('id');
        $params = $request->get_json_params();
        $status = sanitize_text_field($params['status'] ?? '');

        $allowed = ['pending', 'approved', 'rejected', 'spam'];
        if (!in_array($status, $allowed)) {
            return $this->error(__('Invalid status.', 'plugixarecipe'));
        }

        $review = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$prefix}reviews WHERE id = %d", $id), ARRAY_A);
        if (!$review) {
            return $this->error(__('Review not found.', 'plugixarecipe'), 404);
        }

        $wpdb->update($prefix . 'reviews', [
            'status'      => $status,
            'modified_at' => current_time('mysql'),
        ], ['id' => $id]);

        // Recalculate recipe rating
        $this->recalculate_recipe_rating((int) $review['recipe_id']);

        // Content write: approve/reject/spam changes the recipe's rating aggregate shown in lists.
        Cache::flush('reviews', 'recipes');

        return $this->success(['id' => $id, 'status' => $status], __('Review status updated.', 'plugixarecipe'));
    }

    /**
     * Bulk delete reviews.
     */
    public function bulk_delete($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $params = $request->get_json_params();
        $ids = array_map('intval', $params['ids'] ?? []);

        if (empty($ids)) {
            return $this->error(__('No IDs provided.', 'plugixarecipe'));
        }

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));

        // Get affected recipe IDs for recalculation
        $recipe_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT recipe_id FROM {$prefix}reviews WHERE id IN ({$placeholders})",
            ...$ids
        ));

        $deleted = $wpdb->query($wpdb->prepare(
            "DELETE FROM {$prefix}reviews WHERE id IN ({$placeholders})",
            ...$ids
        ));

        // Recalculate ratings for affected recipes
        foreach ($recipe_ids as $recipe_id) {
            $this->recalculate_recipe_rating((int) $recipe_id);
        }

        // Content write: reviews also change the recipes' rating aggregates shown in lists.
        Cache::flush('reviews', 'recipes');

        return $this->success(['deleted' => $deleted], __('Reviews deleted.', 'plugixarecipe'));
    }

    /**
     * Bulk action on reviews (approve, reject, spam).
     */
    public function bulk_action($request): WP_REST_Response|WP_Error {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';
        $params = $request->get_json_params();
        $ids    = array_map('intval', $params['ids'] ?? []);
        $action = sanitize_text_field($params['action'] ?? '');

        if (empty($ids) || empty($action)) {
            return $this->error(__('IDs and action are required.', 'plugixarecipe'));
        }

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $now = current_time('mysql');

        $status_map = [
            'approve' => 'approved',
            'reject'  => 'rejected',
            'spam'    => 'spam',
            'pending' => 'pending',
        ];

        if (!isset($status_map[$action])) {
            return $this->error(__('Invalid action.', 'plugixarecipe'));
        }

        // Get affected recipe IDs
        $recipe_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT recipe_id FROM {$prefix}reviews WHERE id IN ({$placeholders})",
            ...$ids
        ));

        $wpdb->query($wpdb->prepare(
            "UPDATE {$prefix}reviews SET status = %s, modified_at = %s WHERE id IN ({$placeholders})",
            $status_map[$action], $now, ...$ids
        ));

        // Recalculate ratings for affected recipes
        foreach ($recipe_ids as $recipe_id) {
            $this->recalculate_recipe_rating((int) $recipe_id);
        }

        // Content write: bulk status changes also change the recipes' rating aggregates.
        Cache::flush('reviews', 'recipes');

        /* translators: %s: the bulk action that was applied (e.g. approve, unapprove, delete). */
        return $this->success(null, sprintf(__('Bulk %s completed.', 'plugixarecipe'), $action));
    }

    /**
     * Recalculate the average rating and count for a recipe based on approved reviews.
     */
    private function recalculate_recipe_rating(int $recipe_id): void {
        global $wpdb;
        $prefix = $wpdb->prefix . 'plugixarecipe_';

        $stats = $wpdb->get_row($wpdb->prepare(
            "SELECT AVG(rating) AS avg_rating, COUNT(*) AS count
            FROM {$prefix}reviews
            WHERE recipe_id = %d AND status = 'approved'",
            $recipe_id
        ));

        $wpdb->update($prefix . 'recipes', [
            'rating_average' => $stats->avg_rating ? round((float) $stats->avg_rating, 2) : 0,
            'rating_count'   => (int) $stats->count,
        ], ['id' => $recipe_id]);
    }
}
