<?php

namespace WPSN\Modules\Audit;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

/**
 * Stores per-scan audit issues (history).
 */
final class AuditScanIssueRepository
{
    private Logger $logger;

    public function __construct(Logger $logger)
    {
        $this->logger = $logger;
    }

    private function table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'wpsn_audit_scan_issues';
    }

    /**
     * @param array<string, mixed> $row
     */
    public function insert(int $scanId, array $row): void
    {
        global $wpdb;

        $scanId = (int) $scanId;
        if ($scanId <= 0) {
            return;
        }

        $table = $this->table();

        $defaults = [
            'object_type'    => '',
            'object_id'      => 0,
            'object_subtype' => '',
            'object_title'   => '',
            'object_url'     => '',
            'issue_code'     => '',
            'severity'       => 2,
            'message'        => '',
            'suggestion'     => '',
            'details'        => null,
            'detected_at'    => current_time('mysql'),
            'updated_at'     => current_time('mysql'),
        ];

        $row = array_merge($defaults, $row);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->replace(
                $table,
                [
                    'scan_id'       => $scanId,
                    'object_type'   => (string) $row['object_type'],
                    'object_id'     => (int) $row['object_id'],
                    'object_subtype'=> (string) $row['object_subtype'],
                    'object_title'  => (string) $row['object_title'],
                    'object_url'    => (string) $row['object_url'],
                    'issue_code'    => (string) $row['issue_code'],
                    'severity'      => (int) $row['severity'],
                    'message'       => (string) $row['message'],
                    'suggestion'    => (string) $row['suggestion'],
                    'details'       => $row['details'] !== null ? (string) $row['details'] : null,
                    'detected_at'   => (string) $row['detected_at'],
                    'updated_at'    => (string) $row['updated_at'],
                ],
                [
                    '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s'
                ]
            );
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan-issue insert failed: ' . $e->getMessage());
        }
    }

    public function countForScan(int $scanId): int
    {
        global $wpdb;

        $scanId = (int) $scanId;
        if ($scanId <= 0) {
            return 0;
        }

        $table = $this->table();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            return (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE scan_id = %d",
                $scanId
            ));
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan-issue count failed: ' . $e->getMessage());
            return 0;
        }
    }

    /**
     * @param array<string, mixed> $filters
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listForScan(int $scanId, int $page, int $perPage, array $filters = []): array
    {
        global $wpdb;

        $scanId = (int) $scanId;
        if ($scanId <= 0) {
            return ['items' => [], 'total' => 0];
        }

        $page    = max(1, $page);
        $perPage = max(1, min(500, $perPage));

        $table = $this->table();

        $where = 'scan_id = %d';
        $params = [$scanId];

        if (!empty($filters['issue_code'])) {
            $where .= ' AND issue_code = %s';
            $params[] = (string) $filters['issue_code'];
        }

        if (!empty($filters['severity'])) {
            $where .= ' AND severity = %d';
            $params[] = (int) $filters['severity'];
        }

        if (!empty($filters['object_type'])) {
            $where .= ' AND object_type = %s';
            $params[] = (string) $filters['object_type'];
        }

        if (!empty($filters['object_id'])) {
            $where .= ' AND object_id = %d';
            $params[] = (int) $filters['object_id'];
        }

        if (!empty($filters['s'])) {
            $like = '%' . $wpdb->esc_like((string) $filters['s']) . '%';
            $where .= ' AND object_title LIKE %s';
            $params[] = $like;
        }

        // Total.
        $sqlTotal = "SELECT COUNT(*) FROM {$table} WHERE {$where}";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $total = (int) $wpdb->get_var($wpdb->prepare($sqlTotal, $params));

        $offset = ($page - 1) * $perPage;
        $sqlItems = "SELECT * FROM {$table} WHERE {$where} ORDER BY severity DESC, updated_at DESC LIMIT %d OFFSET %d";
        $paramsItems = array_merge($params, [$perPage, $offset]);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $items = $wpdb->get_results($wpdb->prepare($sqlItems, $paramsItems), ARRAY_A);
        if (!is_array($items)) {
            $items = [];
        }

        return [
            'items' => $items,
            'total' => $total,
        ];
    }


    /**
     * List distinct objects for a given scan matched by filters (for bulk actions).
     *
     * @param array<string, mixed> $filters
     * @return array<int, array{object_type:string,object_id:int,object_subtype:string}>
     */
    public function listDistinctObjects(int $scanId, array $filters = [], int $limit = 500, int $offset = 0): array
    {
        global $wpdb;

        $scanId = (int) $scanId;
        if ($scanId <= 0) {
            return [];
        }

        $limit = max(1, min(5000, (int) $limit));
        $offset = max(0, (int) $offset);

        $table = $this->table();

        $where = 'scan_id = %d';
        $params = [$scanId];

        if (!empty($filters['issue_code'])) {
            $where .= ' AND issue_code = %s';
            $params[] = (string) $filters['issue_code'];
        }

        if (!empty($filters['severity'])) {
            $where .= ' AND severity = %d';
            $params[] = (int) $filters['severity'];
        }

        if (!empty($filters['object_type'])) {
            $where .= ' AND object_type = %s';
            $params[] = (string) $filters['object_type'];
        }

        if (!empty($filters['object_id'])) {
            $where .= ' AND object_id = %d';
            $params[] = (int) $filters['object_id'];
        }

        if (!empty($filters['s'])) {
            $like = '%' . $wpdb->esc_like((string) $filters['s']) . '%';
            $where .= ' AND object_title LIKE %s';
            $params[] = $like;
        }

        $sql = "SELECT object_type, object_id, object_subtype,
                       MAX(severity) AS sort_severity,
                       MAX(updated_at) AS sort_updated_at
                FROM {$table}
                WHERE {$where}
                GROUP BY object_type, object_id, object_subtype
                ORDER BY sort_severity DESC, sort_updated_at DESC, object_type ASC, object_id ASC
                LIMIT %d OFFSET %d";

        $paramsSql = array_merge($params, [$limit, $offset]);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results($wpdb->prepare($sql, $paramsSql), ARRAY_A);
        if (!is_array($rows)) {
            return [];
        }

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }
            $out[] = [
                'object_type' => isset($r['object_type']) ? (string) $r['object_type'] : '',
                'object_id' => isset($r['object_id']) ? (int) $r['object_id'] : 0,
                'object_subtype' => isset($r['object_subtype']) ? (string) $r['object_subtype'] : '',
            ];
        }

        return $out;
    }

    /**
     * List distinct objects for a given scan together with the issue codes that
     * matched the current filters.
     *
     * Used by queued bulk SEO actions so the generator can narrow updates to
     * the affected fields (title vs description) and actually change duplicates
     * when the current filter is "All issues".
     *
     * @param array<string, mixed> $filters
     * @return array<int, array{object_type:string,object_id:int,object_subtype:string,issue_codes:array<int,string>}>
     */
    public function listDistinctObjectsWithIssueCodes(int $scanId, array $filters = [], int $limit = 500, int $offset = 0): array
    {
        global $wpdb;

        $scanId = (int) $scanId;
        if ($scanId <= 0) {
            return [];
        }

        $limit = max(1, min(5000, (int) $limit));
        $offset = max(0, (int) $offset);

        $table = $this->table();

        $where = 'scan_id = %d';
        $params = [$scanId];

        if (!empty($filters['issue_code'])) {
            $where .= ' AND issue_code = %s';
            $params[] = (string) $filters['issue_code'];
        }

        if (!empty($filters['severity'])) {
            $where .= ' AND severity = %d';
            $params[] = (int) $filters['severity'];
        }

        if (!empty($filters['object_type'])) {
            $where .= ' AND object_type = %s';
            $params[] = (string) $filters['object_type'];
        }

        if (!empty($filters['object_id'])) {
            $where .= ' AND object_id = %d';
            $params[] = (int) $filters['object_id'];
        }

        if (!empty($filters['s'])) {
            $like = '%' . $wpdb->esc_like((string) $filters['s']) . '%';
            $where .= ' AND object_title LIKE %s';
            $params[] = $like;
        }

        $sql = "SELECT object_type, object_id, object_subtype,
                       MAX(severity) AS sort_severity,
                       MAX(updated_at) AS sort_updated_at,
                       GROUP_CONCAT(DISTINCT issue_code ORDER BY issue_code SEPARATOR ',') AS issue_codes
                FROM {$table}
                WHERE {$where}
                GROUP BY object_type, object_id, object_subtype
                ORDER BY sort_severity DESC, sort_updated_at DESC, object_type ASC, object_id ASC
                LIMIT %d OFFSET %d";

        $paramsSql = array_merge($params, [$limit, $offset]);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results($wpdb->prepare($sql, $paramsSql), ARRAY_A);
        if (!is_array($rows)) {
            return [];
        }

        $out = [];
        foreach ($rows as $r) {
            if (!is_array($r)) {
                continue;
            }

            $codes = [];
            $rawCodes = isset($r['issue_codes']) ? (string) $r['issue_codes'] : '';
            if ($rawCodes !== '') {
                foreach (explode(',', $rawCodes) as $code) {
                    $code = sanitize_key((string) $code);
                    if ($code !== '') {
                        $codes[$code] = 1;
                    }
                }
            }

            $out[] = [
                'object_type' => isset($r['object_type']) ? (string) $r['object_type'] : '',
                'object_id' => isset($r['object_id']) ? (int) $r['object_id'] : 0,
                'object_subtype' => isset($r['object_subtype']) ? (string) $r['object_subtype'] : '',
                'issue_codes' => array_keys($codes),
            ];
        }

        return $out;
    }

    /**
     * @return array<string, int>
     */
    public function countsByCode(int $scanId): array
    {
        global $wpdb;

        $scanId = (int) $scanId;
        if ($scanId <= 0) {
            return [];
        }

        $table = $this->table();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT issue_code, COUNT(*) AS c FROM {$table} WHERE scan_id = %d GROUP BY issue_code",
            $scanId
        ), ARRAY_A);

        if (!is_array($rows)) {
            return [];
        }

        $out = [];
        foreach ($rows as $r) {
            $code = isset($r['issue_code']) ? (string) $r['issue_code'] : '';
            if ($code === '') {
                continue;
            }
            $out[$code] = (int) ($r['c'] ?? 0);
        }

        return $out;
    }
}
