<?php

namespace WPSN\Modules\Audit;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

/**
 * Stores audit scan runs (history) and supports retention purge.
 */
final class AuditScanRepository
{
    private Logger $logger;

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

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

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

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

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

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

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

    public function createScan(bool $includeTerms, bool $checkSocial): int
    {
        global $wpdb;

        $table = $this->tableScans();
        $now = current_time('mysql');

        try {
            // NOTE: we omit nullable columns (finished_at, issues_by_code) on insert.
            // This avoids strict SQL modes rejecting empty/zero dates.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $ok = $wpdb->insert(
                $table,
                [
                    'status'        => 'running',
                    'started_at'    => $now,
                    'duration_sec'  => 0,
                    'include_terms' => $includeTerms ? 1 : 0,
                    'check_social'  => $checkSocial ? 1 : 0,
                    'scanned_posts' => 0,
                    'scanned_terms' => 0,
                    'issues_total'  => 0,
                    'error_message' => '',
                    'created_at'    => $now,
                    'updated_at'    => $now,
                ],
                [
                    '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%d', '%s', '%s', '%s'
                ]
            );

            if (!$ok) {
                return 0;
            }

            return (int) $wpdb->insert_id;
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan create failed: ' . $e->getMessage());
            return 0;
        }
    }

    /**
     * @param array<string, mixed> $summary
     */
    public function finishScan(int $scanId, array $summary, string $status = 'completed', string $errorMessage = ''): void
    {
        global $wpdb;

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

        $table = $this->tableScans();
        $now = current_time('mysql');

        $issuesByCode = null;
        if (isset($summary['issues_by_code']) && is_array($summary['issues_by_code'])) {
            $issuesByCode = wp_json_encode($summary['issues_by_code']);
        }

        $finalStatus = sanitize_key($status) ?: 'completed';
        $phase = $finalStatus === 'completed' ? 'completed' : ($finalStatus === 'failed' ? 'failed' : $finalStatus);

        $fields = [
            'status'       => $finalStatus,
            'phase'        => $phase,
            'finished_at'  => $now,
            'duration_sec' => (int) ($summary['duration_sec'] ?? 0),
            'total_posts'  => isset($summary['total_posts']) ? (int) $summary['total_posts'] : null,
            'total_terms'  => isset($summary['total_terms']) ? (int) $summary['total_terms'] : null,
            'scanned_posts'=> (int) ($summary['scanned_posts'] ?? 0),
            'scanned_terms'=> (int) ($summary['scanned_terms'] ?? 0),
            'news_eligible' => isset($summary['news_eligible']) ? (int) $summary['news_eligible'] : null,
            'video_candidates' => isset($summary['video_candidates']) ? (int) $summary['video_candidates'] : null,
            'video_eligible' => isset($summary['video_eligible']) ? (int) $summary['video_eligible'] : null,
            'issues_total' => (int) ($summary['issues_total'] ?? 0),
            'issues_by_code' => $issuesByCode,
            'error_message' => $errorMessage !== '' ? sanitize_text_field($errorMessage) : '',
            'updated_at'   => $now,
        ];

        // Remove nulls so $wpdb formats stay consistent.
        $fields = array_filter($fields, static fn($v) => $v !== null);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $formats = [];
            foreach ($fields as $k => $v) {
                if (in_array($k, ['duration_sec','total_posts','total_terms','scanned_posts','scanned_terms','news_eligible','video_candidates','video_eligible','issues_total'], true)) {
                    $formats[] = '%d';
                } elseif ($k === 'issues_by_code') {
                    $formats[] = '%s';
                } else {
                    $formats[] = '%s';
                }
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->update($table, $fields, ['id' => $scanId], $formats, ['%d']);
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan finish failed: ' . $e->getMessage());
        }
    }

    /**
     * Initialize totals and frozen config for an async scan.
     *
     * @param array<string,mixed> $config
     */
    public function initAsyncScan(int $scanId, int $totalPosts, int $totalTerms, array $config): void
    {
        global $wpdb;

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

        $table = $this->tableScans();
        $now = current_time('mysql');

        $configJson = wp_json_encode($config);
        if (!is_string($configJson) || $configJson === '') {
            $configJson = null;
        }

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->update(
                $table,
                [
                    'phase' => 'queued',
                    'total_posts' => max(0, (int) $totalPosts),
                    'total_terms' => max(0, (int) $totalTerms),
                    'config_json' => $configJson,
                    'updated_at' => $now,
                ],
                ['id' => $scanId],
                ['%s', '%d', '%d', '%s', '%s'],
                ['%d']
            );
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan init failed: ' . $e->getMessage());
        }
    }


    /**
     * Update only the stored issue summary for an existing scan.
     *
     * This is used after per-object queued fixes refresh the live latest-issues table,
     * so history cards / Tasks Hub totals stay in sync without a full re-run.
     *
     * @param array<string,int> $issuesByCode
     */
    public function updateIssueSummary(int $scanId, int $issuesTotal, array $issuesByCode): void
    {
        global $wpdb;

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

        $table = $this->tableScans();
        $now = current_time('mysql');
        $issuesJson = wp_json_encode($issuesByCode);
        if (!is_string($issuesJson) || $issuesJson === '') {
            $issuesJson = '{}';
        }

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->update(
                $table,
                [
                    'issues_total' => max(0, (int) $issuesTotal),
                    'issues_by_code' => $issuesJson,
                    'updated_at' => $now,
                ],
                ['id' => $scanId],
                ['%d', '%s', '%s'],
                ['%d']
            );
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan issue summary update failed: ' . $e->getMessage());
        }
    }

    public function setPhase(int $scanId, string $phase): void
    {
        global $wpdb;

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

        $phase = sanitize_key($phase);
        if ($phase === '') {
            return;
        }

        $table = $this->tableScans();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->update(
                $table,
                [
                    'phase' => $phase,
                    'updated_at' => current_time('mysql'),
                ],
                ['id' => $scanId],
                ['%s', '%s'],
                ['%d']
            );
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan phase update failed: ' . $e->getMessage());
        }
    }

    public function incrementPosts(int $scanId, int $deltaPosts, int $deltaNewsEligible = 0, int $deltaVideoCandidates = 0, int $deltaVideoEligible = 0): void
    {
        global $wpdb;

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

        $table = $this->tableScans();

        $deltaPosts = max(0, (int) $deltaPosts);
        $deltaNewsEligible = max(0, (int) $deltaNewsEligible);
        $deltaVideoCandidates = max(0, (int) $deltaVideoCandidates);
        $deltaVideoEligible = max(0, (int) $deltaVideoEligible);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table}
                 SET scanned_posts = scanned_posts + %d,
                     news_eligible = news_eligible + %d,
                     video_candidates = video_candidates + %d,
                     video_eligible = video_eligible + %d,
                     phase = %s,
                     updated_at = %s
                 WHERE id = %d AND status = %s",
                $deltaPosts,
                $deltaNewsEligible,
                $deltaVideoCandidates,
                $deltaVideoEligible,
                'posts',
                current_time('mysql'),
                $scanId,
                'running'
            ));
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan increment posts failed: ' . $e->getMessage());
        }
    }

    public function incrementTerms(int $scanId, int $deltaTerms): void
    {
        global $wpdb;

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

        $table = $this->tableScans();
        $deltaTerms = max(0, (int) $deltaTerms);

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table}
                 SET scanned_terms = scanned_terms + %d,
                     phase = %s,
                     updated_at = %s
                 WHERE id = %d AND status = %s",
                $deltaTerms,
                'terms',
                current_time('mysql'),
                $scanId,
                'running'
            ));
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan increment terms failed: ' . $e->getMessage());
        }
    }

    public function markFailed(int $scanId, string $errorMessage): void
    {
        $this->finishScan($scanId, [], 'failed', $errorMessage);
    }

    /**
     * @return array<string, mixed>|null
     */
    public function get(int $scanId): ?array
    {
        global $wpdb;

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

        $table = $this->tableScans();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $scanId), ARRAY_A);
        return is_array($row) ? $row : null;
    }

    public function latestRunning(): ?array
    {
        global $wpdb;

        $table = $this->tableScans();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $row = $wpdb->get_row(
                "SELECT * FROM {$table} WHERE status = 'running' ORDER BY id DESC LIMIT 1",
                ARRAY_A
            );
            return is_array($row) ? $row : null;
        } catch (\Throwable $e) {
            $this->logger->error('Audit scan latestRunning failed: ' . $e->getMessage());
            return null;
        }
    }

    /**
     * @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(100, $perPage));

        $table = $this->tableScans();
        $where = '1=1';
        $params = [];

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

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

    /**
     * Purge scan history keeping only last $keep scans.
     *
     * - $keep = 0 means keep forever (no purge).
     *
     * @return array{deleted_scans:int, deleted_issues:int, threshold_id:int}
     */
    public function purgeKeepLast(int $keep): array
    {
        global $wpdb;

        $keep = (int) $keep;
        if ($keep <= 0) {
            return ['deleted_scans' => 0, 'deleted_issues' => 0, 'threshold_id' => 0];
        }

        $scans = $this->tableScans();
        $issues = $this->tableScanIssues();
        $titles = $this->tableScanTitles();
        $descriptions = $this->tableScanDescriptions();
        $titleKeys = $this->tableScanTitleKeys();
        $descriptionKeys = $this->tableScanDescriptionKeys();

        // Find the ID threshold: keep last N scans, delete older ones.
        $offset = max(0, $keep - 1);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $threshold = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$scans} ORDER BY id DESC LIMIT 1 OFFSET %d",
            $offset
        ));

        if ($threshold <= 0) {
            return ['deleted_scans' => 0, 'deleted_issues' => 0, 'threshold_id' => 0];
        }

        $deletedIssues = 0;
        $deletedScans = 0;

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $deletedIssues = (int) $wpdb->query($wpdb->prepare(
                "DELETE FROM {$issues} WHERE scan_id < %d",
                $threshold
            ));

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$titles} WHERE scan_id < %d",
                $threshold
            ));

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$descriptions} WHERE scan_id < %d",
                $threshold
            ));

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$titleKeys} WHERE scan_id < %d",
                $threshold
            ));

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$descriptionKeys} WHERE scan_id < %d",
                $threshold
            ));

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $deletedScans = (int) $wpdb->query($wpdb->prepare(
                "DELETE FROM {$scans} WHERE id < %d",
                $threshold
            ));
        } catch (\Throwable $e) {
            $this->logger->error('Audit purge failed: ' . $e->getMessage());
        }

        return [
            'deleted_scans' => max(0, $deletedScans),
            'deleted_issues' => max(0, $deletedIssues),
            'threshold_id' => $threshold,
        ];
    }

    public function clearAll(): void
    {
        global $wpdb;

        $scans = $this->tableScans();
        $issues = $this->tableScanIssues();
        $titles = $this->tableScanTitles();
        $descriptions = $this->tableScanDescriptions();
        $titleKeys = $this->tableScanTitleKeys();
        $descriptionKeys = $this->tableScanDescriptionKeys();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DELETE FROM {$issues}");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DELETE FROM {$titles}");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DELETE FROM {$descriptions}");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DELETE FROM {$titleKeys}");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DELETE FROM {$descriptionKeys}");
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DELETE FROM {$scans}");
        } catch (\Throwable $e) {
            $this->logger->error('Audit clear history failed: ' . $e->getMessage());
        }
    }

    /**
     * Repair frozen scan totals from the saved per-scan issue rows.
     *
     * Older builds updated issues_total on completed scans after bulk fixes,
     * which made history rows drift away from their original snapshot. This
     * method recalculates the saved summary from the immutable scan issue table.
     */
    public function repairFrozenSummaries(int $limit = 20): int
    {
        global $wpdb;

        $limit = max(1, min(100, $limit));
        $scansTable = $this->tableScans();
        $scanIssuesTable = $this->tableScanIssues();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $scanRows = $wpdb->get_results($wpdb->prepare(
                "SELECT id, issues_total, issues_by_code FROM {$scansTable} ORDER BY id DESC LIMIT %d",
                $limit
            ), ARRAY_A);

            if (!is_array($scanRows) || empty($scanRows)) {
                return 0;
            }

            $scanIds = [];
            $current = [];
            foreach ($scanRows as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $scanId = isset($row['id']) ? (int) $row['id'] : 0;
                if ($scanId <= 0) {
                    continue;
                }
                $scanIds[] = $scanId;

                $existingCounts = [];
                if (!empty($row['issues_by_code'])) {
                    $decoded = json_decode((string) $row['issues_by_code'], true);
                    if (is_array($decoded)) {
                        foreach ($decoded as $code => $count) {
                            $code = sanitize_key((string) $code);
                            if ($code !== '') {
                                $existingCounts[$code] = (int) $count;
                            }
                        }
                    }
                }
                ksort($existingCounts);

                $current[$scanId] = [
                    'issues_total' => isset($row['issues_total']) ? (int) $row['issues_total'] : 0,
                    'issues_by_code' => $existingCounts,
                ];
            }

            if (empty($scanIds)) {
                return 0;
            }

            $placeholders = implode(', ', array_fill(0, count($scanIds), '%d'));
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $issueRows = $wpdb->get_results($wpdb->prepare(
                "SELECT scan_id, issue_code, COUNT(*) AS c
                 FROM {$scanIssuesTable}
                 WHERE scan_id IN ({$placeholders})
                 GROUP BY scan_id, issue_code",
                $scanIds
            ), ARRAY_A);

            $countsByScan = [];
            if (is_array($issueRows)) {
                foreach ($issueRows as $row) {
                    if (!is_array($row)) {
                        continue;
                    }
                    $scanId = isset($row['scan_id']) ? (int) $row['scan_id'] : 0;
                    $code = isset($row['issue_code']) ? sanitize_key((string) $row['issue_code']) : '';
                    if ($scanId <= 0 || $code === '') {
                        continue;
                    }
                    if (!isset($countsByScan[$scanId]) || !is_array($countsByScan[$scanId])) {
                        $countsByScan[$scanId] = [];
                    }
                    $countsByScan[$scanId][$code] = isset($row['c']) ? (int) $row['c'] : 0;
                }
            }

            $repaired = 0;
            foreach ($scanIds as $scanId) {
                $counts = isset($countsByScan[$scanId]) && is_array($countsByScan[$scanId]) ? $countsByScan[$scanId] : [];
                ksort($counts);

                $total = 0;
                foreach ($counts as $count) {
                    $total += (int) $count;
                }

                $currentTotal = isset($current[$scanId]['issues_total']) ? (int) $current[$scanId]['issues_total'] : 0;
                $currentCounts = isset($current[$scanId]['issues_by_code']) && is_array($current[$scanId]['issues_by_code']) ? $current[$scanId]['issues_by_code'] : [];

                if ($currentTotal === $total && $currentCounts === $counts) {
                    continue;
                }

                $issuesJson = wp_json_encode($counts);
                if (!is_string($issuesJson) || $issuesJson === '') {
                    $issuesJson = '{}';
                }

                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $wpdb->update(
                    $scansTable,
                    [
                        'issues_total' => $total,
                        'issues_by_code' => $issuesJson,
                        'updated_at' => current_time('mysql'),
                    ],
                    ['id' => $scanId],
                    ['%d', '%s', '%s'],
                    ['%d']
                );
                $repaired++;
            }

            return $repaired;
        } catch (\Throwable $e) {
            $this->logger->error('Audit repairFrozenSummaries failed: ' . $e->getMessage());
            return 0;
        }
    }
}
