<?php

namespace WPSN\Modules\Analysis\Admin;

use WPSN\SEO\MetaKeys;

defined('ABSPATH') || exit;

final class LowScoreQuery
{
    /**
     * Fetch items for the Low score pages report.
     *
     * @param array{pt:string,status:string,max:int,include_na:bool,s:string,orderby?:string,order?:string} $filters
     * @return array{0:array<int,array<string,mixed>>,1:int} [items, total]
     */
    public function fetch(array $filters, int $perPage, int $offset): array
    {
        global $wpdb;

        $perPage = max(1, min(200, (int) $perPage));
        $offset = max(0, (int) $offset);

        $pt = isset($filters['pt']) ? (string) $filters['pt'] : 'any';
        $status = isset($filters['status']) ? (string) $filters['status'] : 'publish';
        $max = isset($filters['max']) ? (int) $filters['max'] : 54;
        $max = max(0, min(100, $max));
        $includeNa = !empty($filters['include_na']);
        $search = isset($filters['s']) ? (string) $filters['s'] : '';

        $orderBy = isset($filters['orderby']) ? sanitize_key((string) $filters['orderby']) : 'score';
        $order = isset($filters['order']) ? strtoupper((string) $filters['order']) : 'ASC';
        $order = in_array($order, ['ASC', 'DESC'], true) ? $order : 'ASC';

        $postTypes = [];
        if ($pt !== 'any' && $pt !== '' && post_type_exists($pt) && $pt !== 'attachment') {
            $postTypes = [$pt];
        } else {
            $postTypes = get_post_types(['public' => true], 'names');
            $postTypes = array_values(array_filter(array_map('strval', $postTypes)));
            $postTypes = array_values(array_diff($postTypes, ['attachment']));
        }

        if (empty($postTypes)) {
            return [[], 0];
        }

        $allowedStatus = ['publish', 'draft', 'pending', 'private', 'future'];
        $statuses = [];
        if ($status === 'any') {
            $statuses = $allowedStatus;
        } elseif (in_array($status, $allowedStatus, true)) {
            $statuses = [$status];
        } else {
            $statuses = ['publish'];
        }

        $ptPh = implode(',', array_fill(0, count($postTypes), '%s'));
        $stPh = implode(',', array_fill(0, count($statuses), '%s'));

        $params = [];
        $params[] = MetaKeys::ANALYSIS_SCORE;
        $params = array_merge($params, $postTypes, $statuses);

        $sql = "SELECT SQL_CALC_FOUND_ROWS p.ID, p.post_title, p.post_type, p.post_status, p.post_modified_gmt, pm.meta_value AS score\n";
        $sql .= "FROM {$wpdb->posts} p\n";
        $sql .= "LEFT JOIN {$wpdb->postmeta} pm ON (p.ID = pm.post_id AND pm.meta_key = %s)\n";
        $sql .= "WHERE p.post_type IN ({$ptPh})\n";
        $sql .= "AND p.post_status IN ({$stPh})\n";

        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $sql .= "AND p.post_title LIKE %s\n";
            $params[] = $like;
        }

        // Score condition.
        if ($includeNa) {
            $sql .= "AND ( (CAST(pm.meta_value AS SIGNED) <= %d) OR pm.meta_value IS NULL OR pm.meta_value = '' )\n";
        } else {
            $sql .= "AND (CAST(pm.meta_value AS SIGNED) <= %d)\n";
        }
        $params[] = $max;

        // Order.
        $scoreExpr = "COALESCE(CAST(pm.meta_value AS SIGNED), -1)";
        if ($orderBy === 'title') {
            $sql .= "ORDER BY p.post_title {$order}, {$scoreExpr} ASC\n";
        } elseif ($orderBy === 'modified') {
            $sql .= "ORDER BY p.post_modified_gmt {$order}, {$scoreExpr} ASC\n";
        } else {
            // Default: worst first.
            $sql .= "ORDER BY {$scoreExpr} {$order}, p.post_modified_gmt DESC\n";
        }

        $sql .= "LIMIT %d OFFSET %d";
        $params[] = $perPage;
        $params[] = $offset;

        $prepared = $wpdb->prepare($sql, $params);
        if (!is_string($prepared)) {
            return [[], 0];
        }

        $items = $wpdb->get_results($prepared, ARRAY_A);
        $items = is_array($items) ? $items : [];

        $total = (int) $wpdb->get_var('SELECT FOUND_ROWS()');

        return [$items, $total];
    }
}
