<?php

namespace WPSN\Modules\SchemaValidator;

defined('ABSPATH') || exit;

final class SchemaIssueRepository
{
    /** @var \wpdb */
    private $db;

    private string $table;

    public function __construct(?\wpdb $db = null)
    {
        global $wpdb;
        $this->db = $db ?: $wpdb;
        $this->table = $this->db->prefix . 'wpsn_schema_issues';
    }

    /**
     * @param array<string,mixed> $issue
     */
    public function insert(int $scanId, array $issue): void
    {
        if ($scanId <= 0) {
            return;
        }

        $now = current_time('mysql');

        $row = [
            'scan_id' => $scanId,
            'object_type' => (string) ($issue['object_type'] ?? ''),
            'object_id' => (int) ($issue['object_id'] ?? 0),
            'object_subtype' => (string) ($issue['object_subtype'] ?? ''),
            'object_title' => (string) ($issue['object_title'] ?? ''),
            'object_url' => (string) ($issue['object_url'] ?? ''),
            'issue_code' => (string) ($issue['issue_code'] ?? ''),
            'severity' => (int) ($issue['severity'] ?? 1),
            'message' => (string) ($issue['message'] ?? ''),
            'suggestion' => (string) ($issue['suggestion'] ?? ''),
            'details' => isset($issue['details']) ? (string) $issue['details'] : null,
            'detected_at' => $now,
            'updated_at' => $now,
        ];

        if ($row['object_type'] === '' || $row['issue_code'] === '') {
            return;
        }

        $this->db->insert(
            $this->table,
            $row,
            ['%d', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s']
        );
    }

    /**
     * @param array<string,mixed> $filters
     * @return array{rows:array<int,array<string,mixed>>, total:int}
     */
    public function listByScan(int $scanId, array $filters, int $paged, int $perPage): array
    {
        $paged = max(1, (int) $paged);
        $perPage = max(1, min(200, (int) $perPage));
        $offset = ($paged - 1) * $perPage;

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

        if (!empty($filters['severity'])) {
            $sev = (int) $filters['severity'];
            if ($sev >= 1 && $sev <= 3) {
                $where[] = 'severity = %d';
                $params[] = $sev;
            }
        }

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

        if (!empty($filters['s'])) {
            $s = (string) $filters['s'];
            $s = trim($s);
            if ($s !== '') {
                $where[] = '(object_title LIKE %s OR object_url LIKE %s OR message LIKE %s)';
                $like = '%' . $this->db->esc_like($s) . '%';
                $params[] = $like;
                $params[] = $like;
                $params[] = $like;
            }
        }

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

        // Total count.
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $total = (int) $this->db->get_var($this->db->prepare("SELECT COUNT(*) FROM {$this->table} WHERE {$whereSql}", $params));

        // Rows.
        $paramsRows = array_merge($params, [$perPage, $offset]);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $this->db->get_results(
            $this->db->prepare(
                "SELECT * FROM {$this->table} WHERE {$whereSql} ORDER BY severity DESC, id DESC LIMIT %d OFFSET %d",
                $paramsRows
            ),
            ARRAY_A
        );

        return [
            'rows' => is_array($rows) ? $rows : [],
            'total' => $total,
        ];
    }

    /**
     * @return array<int,array{issue_code:string,count:int}>
     */
    public function countByCode(int $scanId): array
    {
        if ($scanId <= 0) {
            return [];
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $this->db->get_results(
            $this->db->prepare(
                "SELECT issue_code, COUNT(*) as cnt FROM {$this->table} WHERE scan_id = %d GROUP BY issue_code ORDER BY cnt DESC",
                $scanId
            ),
            ARRAY_A
        );

        $out = [];
        if (is_array($rows)) {
            foreach ($rows as $r) {
                if (!is_array($r) || empty($r['issue_code'])) {
                    continue;
                }
                $out[] = [
                    'issue_code' => (string) $r['issue_code'],
                    'count' => (int) ($r['cnt'] ?? 0),
                ];
            }
        }
        return $out;
    }


    /**
     * Get distinct object IDs for a scan by issue code(s).
     *
     * @param array<int,string> $issueCodes
     * @return array<int,int>
     */
    public function distinctObjectIds(int $scanId, string $objectType, array $issueCodes): array
    {
        if ($scanId <= 0) {
            return [];
        }

        $objectType = sanitize_key($objectType);
        if ($objectType === '') {
            return [];
        }

        $codes = array_values(array_filter(array_map(static fn ($c) => sanitize_key((string) $c), $issueCodes)));
        if (!$codes) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($codes), '%s'));
        $params = array_merge([$scanId, $objectType], $codes);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $sql = $this->db->prepare(
            "SELECT DISTINCT object_id FROM {$this->table} WHERE scan_id = %d AND object_type = %s AND issue_code IN ({$placeholders}) ORDER BY object_id ASC",
            $params
        );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $this->db->get_col($sql);

        $out = [];
        if (is_array($rows)) {
            foreach ($rows as $id) {
                $id = (int) $id;
                if ($id > 0) {
                    $out[] = $id;
                }
            }
        }

        return $out;
    }


    /**
     * @return array{high:int,medium:int,low:int,total:int}
     */
    public function countsBySeverity(int $scanId): array
    {
        if ($scanId <= 0) {
            return ['high' => 0, 'medium' => 0, 'low' => 0, 'total' => 0];
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $this->db->get_results(
            $this->db->prepare(
                "SELECT severity, COUNT(*) as cnt FROM {$this->table} WHERE scan_id = %d GROUP BY severity",
                $scanId
            ),
            ARRAY_A
        );

        $out = ['high' => 0, 'medium' => 0, 'low' => 0, 'total' => 0];
        if (is_array($rows)) {
            foreach ($rows as $r) {
                if (!is_array($r) || !isset($r['severity'])) {
                    continue;
                }
                $sev = (int) $r['severity'];
                $cnt = (int) ($r['cnt'] ?? 0);
                $out['total'] += $cnt;
                if ($sev === 3) {
                    $out['high'] = $cnt;
                } elseif ($sev === 2) {
                    $out['medium'] = $cnt;
                } else {
                    $out['low'] += $cnt;
                }
            }
        }

        return $out;
    }
    public function clearAll(): void
    {
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $this->db->query("TRUNCATE TABLE {$this->table}");
    }
}
