<?php

namespace WPSN\Modules\Audit;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

final class AuditIssueRepository
{
    private Logger $logger;

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

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

    public function truncate(): void
    {
        global $wpdb;
        $table = $this->table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query("TRUNCATE TABLE {$table}");
    }

    /**
     * @param array<string, mixed> $row
     */
    public function insert(array $row): void
    {
        global $wpdb;
        $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,
                [
                    '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'],
                ],
                [
                    '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s'
                ]
            );
        } catch (\Throwable $e) {
            $this->logger->error('Audit insert failed: ' . $e->getMessage());
        }
    }

    public function countAll(): int
    {
        global $wpdb;
        $table = $this->table();

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

    public function deleteByObject(string $objectType, int $objectId): int
    {
        global $wpdb;

        $objectType = sanitize_key($objectType);
        $objectId = (int) $objectId;
        if ($objectType === '' || $objectId <= 0) {
            return 0;
        }

        $table = $this->table();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            return (int) $wpdb->query($wpdb->prepare(
                "DELETE FROM {$table} WHERE object_type = %s AND object_id = %d",
                $objectType,
                $objectId
            ));
        } catch (\Throwable $e) {
            $this->logger->error('Audit deleteByObject failed: ' . $e->getMessage());
            return 0;
        }
    }

    /**
     * Delete matching latest-issue rows for specific objects/codes.
     *
     * @param array<int,int> $objectIds
     * @param array<int,string> $issueCodes
     */
    public function deleteByObjectsAndCodes(string $objectType, array $objectIds, array $issueCodes): int
    {
        global $wpdb;

        $objectType = sanitize_key($objectType);
        $objectIds = array_values(array_unique(array_filter(array_map('intval', $objectIds), static fn ($v) => $v > 0)));
        $issueCodes = array_values(array_unique(array_filter(array_map('sanitize_key', $issueCodes), static fn ($v) => $v !== '')));

        if ($objectType === '' || empty($objectIds) || empty($issueCodes)) {
            return 0;
        }

        $table = $this->table();
        $idPlaceholders = implode(', ', array_fill(0, count($objectIds), '%d'));
        $codePlaceholders = implode(', ', array_fill(0, count($issueCodes), '%s'));

        $sql = "DELETE FROM {$table} WHERE object_type = %s AND object_id IN ({$idPlaceholders}) AND issue_code IN ({$codePlaceholders})";
        $args = array_merge([$objectType], $objectIds, $issueCodes);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            return (int) $wpdb->query($wpdb->prepare($sql, $args));
        } catch (\Throwable $e) {
            $this->logger->error('Audit deleteByObjectsAndCodes failed: ' . $e->getMessage());
            return 0;
        }
    }

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

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

        $table = $this->table();

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

        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}";
        if (!empty($params)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $sqlTotal = $wpdb->prepare($sqlTotal, $params);
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
        $total = (int) $wpdb->get_var($sqlTotal);

        $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,
        ];
    }

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

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results("SELECT issue_code, COUNT(*) AS c FROM {$table} GROUP BY issue_code", 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;
    }


    /**
     * List distinct objects 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(array $filters = [], int $limit = 500, int $offset = 0): array
    {
        global $wpdb;

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

        $table = $this->table();

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

        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 together with the issue codes that matched the
     * current filters.
     *
     * Used by queued bulk SEO actions so the generator can tell whether it
     * should repair titles, descriptions, or both even 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(array $filters = [], int $limit = 500, int $offset = 0): array
    {
        global $wpdb;

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

        $table = $this->table();

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

        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;
    }

    public function clear(): void
    {
        global $wpdb;
        $table = $this->table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->query("DELETE FROM {$table}");
    }

    /**
     * Restore the live/latest issues table from a stored scan snapshot.
     *
     * This lets admins return the current Audit view to a previous saved scan
     * without re-running the scan itself.
     */
    public function restoreFromScan(int $scanId): bool
    {
        global $wpdb;

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

        $issuesTable = $this->table();
        $scanIssuesTable = $wpdb->prefix . 'wpsn_audit_scan_issues';
        $cols = 'object_type, object_id, object_subtype, object_title, object_url, issue_code, severity, message, suggestion, details, detected_at, updated_at';

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("TRUNCATE TABLE {$issuesTable}");

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO {$issuesTable} ({$cols}) SELECT {$cols} FROM {$scanIssuesTable} WHERE scan_id = %d",
                $scanId
            ));

            return true;
        } catch (\Throwable $e) {
            $this->logger->error('Audit restoreFromScan failed: ' . $e->getMessage(), [
                'scan_id' => $scanId,
            ]);
            return false;
        }
    }
}
