<?php

namespace WPSN\Modules\Redirects;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

final class NotFoundLogRepository
{
    private Logger $logger;

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

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

    /**
     * @return array<string,mixed>|null
     */
    public function getById(int $id): ?array
    {
        global $wpdb;
        if ($id <= 0) {
            return null;
        }

        $table = $this->tableName();
        $row = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$table} WHERE id = %d LIMIT 1", $id),
            ARRAY_A
        );

        return is_array($row) ? $row : null;
    }

    public function log(string $url, string $referrer = '', string $ua = ''): void
    {
        global $wpdb;

        $url = trim($url);
        if ($url === '') {
            return;
        }

        // Keep within column limits.
        $url = substr($url, 0, 255);
        $referrer = substr(trim($referrer), 0, 255);
        $ua = substr(trim($ua), 0, 255);

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

        // Unique by url.
        $sql = $wpdb->prepare(
            "INSERT INTO {$table} (url, hits, first_seen, last_seen, last_referrer, last_user_agent)\n             VALUES (%s, 1, %s, %s, %s, %s)\n             ON DUPLICATE KEY UPDATE\n                hits = hits + 1,\n                last_seen = VALUES(last_seen),\n                last_referrer = VALUES(last_referrer),\n                last_user_agent = VALUES(last_user_agent)",
            $url,
            $now,
            $now,
            $referrer,
            $ua
        );

        $wpdb->query($sql);
    }

    /**
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function list(int $page, int $perPage, string $search = ''): array
    {
        global $wpdb;
        $table = $this->tableName();

        if ($page < 1) {
            $page = 1;
        }
        if ($perPage < 1) {
            $perPage = 20;
        }
        if ($perPage > 200) {
            $perPage = 200;
        }

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

        $search = trim($search);
        if ($search !== '') {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $where .= ' AND (url LIKE %s OR last_referrer LIKE %s)';
            $params[] = $like;
            $params[] = $like;
        }

        $offset = ($page - 1) * $perPage;

        $sqlTotal = "SELECT COUNT(*) FROM {$table} WHERE {$where}";
        if (!empty($params)) {
            $sqlTotal = $wpdb->prepare($sqlTotal, $params);
        }
        $total = (int) $wpdb->get_var($sqlTotal);

        $sqlItems = "SELECT * FROM {$table} WHERE {$where} ORDER BY last_seen DESC, hits DESC LIMIT %d OFFSET %d";
        $paramsItems = $params;
        $paramsItems[] = $perPage;
        $paramsItems[] = $offset;
        $sqlItems = $wpdb->prepare($sqlItems, $paramsItems);

        $items = $wpdb->get_results($sqlItems, ARRAY_A);
        if (!is_array($items)) {
            $items = [];
        }

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

    public function delete(int $id): bool
    {
        global $wpdb;
        if ($id <= 0) {
            return false;
        }
        $table = $this->tableName();
        $ok = $wpdb->delete($table, ['id' => $id]);
        return $ok !== false;
    }

    public function clear(): void
    {
        global $wpdb;
        $table = $this->tableName();
        $wpdb->query("TRUNCATE TABLE {$table}");
    }

    public function prune(int $maxRows): void
    {
        global $wpdb;
        if ($maxRows <= 0) {
            return;
        }

        $table = $this->tableName();
        $count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
        $excess = $count - $maxRows;
        if ($excess <= 0) {
            return;
        }

        // Delete the oldest entries by last_seen.
        $sql = $wpdb->prepare(
            "DELETE FROM {$table} WHERE id IN (\n                SELECT id FROM (\n                    SELECT id FROM {$table} ORDER BY last_seen ASC LIMIT %d\n                ) t\n            )",
            $excess
        );
        $wpdb->query($sql);
    }
}
