<?php

namespace WPSN\Modules\Notifications;

defined('ABSPATH') || exit;

final class NotificationsRepository
{
    private function table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'wpsn_notifications';
    }

    public function tableExists(): bool
    {
        global $wpdb;
        $table = $this->table();
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));
        return is_string($found) && $found === $table;
    }

    /**
     * Insert a notification event if not already present.
     *
     * @param array<string,mixed> $data
     */
    public function insert(array $data): int
    {
        global $wpdb;
        $table = $this->table();

        if (!$this->tableExists()) {
            return 0;
        }

        $eventKey = isset($data['event_key']) ? (string) $data['event_key'] : '';
        if ($eventKey === '') {
            return 0;
        }

        // Deduplicate by event_key.
        $existing = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE event_key = %s LIMIT 1", $eventKey));
        if ($existing) {
            return (int) $existing;
        }

        $row = [
            'created_at' => isset($data['created_at']) ? (string) $data['created_at'] : gmdate('Y-m-d H:i:s'),
            'source' => isset($data['source']) ? sanitize_key((string) $data['source']) : '',
            'type' => isset($data['type']) ? sanitize_key((string) $data['type']) : '',
            'severity' => isset($data['severity']) ? sanitize_key((string) $data['severity']) : 'info',
            'event_key' => sanitize_text_field((string) $eventKey),
            'title' => isset($data['title']) ? sanitize_text_field((string) $data['title']) : '',
            'message' => isset($data['message']) ? sanitize_text_field((string) $data['message']) : '',
            'url' => isset($data['url']) ? esc_url_raw((string) $data['url']) : '',
            'meta' => null,
            'acknowledged_at' => null,
            'muted_until' => null,
            'sent_at' => null,
            'send_attempts' => 0,
            'last_error' => null,
        ];

        $meta = $data['meta'] ?? null;
        if (is_array($meta) || is_object($meta)) {
            $row['meta'] = wp_json_encode($meta);
        } elseif (is_string($meta) && $meta !== '') {
            $row['meta'] = $meta;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $ok = $wpdb->insert($table, $row);

        if (!$ok) {
            return 0;
        }

        return (int) $wpdb->insert_id;
    }

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

        if ($id <= 0 || !$this->tableExists()) {
            return null;
        }

        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id), ARRAY_A);
        if (!is_array($row)) {
            return null;
        }

        $row['meta_decoded'] = [];
        if (!empty($row['meta']) && is_string($row['meta'])) {
            $decoded = json_decode((string) $row['meta'], true);
            if (is_array($decoded)) {
                $row['meta_decoded'] = $decoded;
            }
        }

        return $row;
    }

    /**
     * @param array<string,mixed> $filters
     * @return array<int,array<string,mixed>>
     */
    public function list(array $filters, int $limit = 50, int $offset = 0): array
    {
        global $wpdb;
        $table = $this->table();

        if (!$this->tableExists()) {
            return [];
        }

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

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

        $view = isset($filters['view']) ? (string) $filters['view'] : 'open';
        $now = gmdate('Y-m-d H:i:s');

        if ($view === 'open') {
            $where .= ' AND acknowledged_at IS NULL AND (muted_until IS NULL OR muted_until < %s)';
            $params[] = $now;
        } elseif ($view === 'acked') {
            $where .= ' AND acknowledged_at IS NOT NULL';
        } elseif ($view === 'muted') {
            $where .= ' AND muted_until IS NOT NULL AND muted_until >= %s';
            $params[] = $now;
        }

        $source = isset($filters['source']) ? sanitize_key((string) $filters['source']) : '';
        if ($source !== '') {
            $where .= ' AND source = %s';
            $params[] = $source;
        }

        $severity = isset($filters['severity']) ? sanitize_key((string) $filters['severity']) : '';
        if ($severity !== '') {
            $where .= ' AND severity = %s';
            $params[] = $severity;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $sql = "SELECT id, created_at, source, type, severity, title, message, url, meta, acknowledged_at, muted_until, sent_at, send_attempts FROM {$table} WHERE {$where} ORDER BY id DESC LIMIT %d OFFSET %d";
        $params[] = $limit;
        $params[] = $offset;

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

        return $rows;
    }

    /**
     * @param array<int,int> $ids
     */
    public function acknowledge(array $ids): int
    {
        global $wpdb;
        $table = $this->table();
        if (!$this->tableExists()) {
            return 0;
        }

        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), static function ($x) {
            return $x > 0;
        })));
        if (empty($ids)) {
            return 0;
        }

        $in = implode(',', array_fill(0, count($ids), '%d'));
        $sql = "UPDATE {$table} SET acknowledged_at = %s WHERE id IN ({$in})";
        $params = array_merge([gmdate('Y-m-d H:i:s')], $ids);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return (int) $wpdb->query($wpdb->prepare($sql, $params));
    }

    /**
     * @param array<int,int> $ids
     */
    public function mute(array $ids, int $days): int
    {
        global $wpdb;
        $table = $this->table();
        if (!$this->tableExists()) {
            return 0;
        }

        $days = max(1, min(365, $days));
        $until = gmdate('Y-m-d H:i:s', time() + ($days * DAY_IN_SECONDS));

        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), static function ($x) {
            return $x > 0;
        })));
        if (empty($ids)) {
            return 0;
        }

        $in = implode(',', array_fill(0, count($ids), '%d'));
        $sql = "UPDATE {$table} SET muted_until = %s WHERE id IN ({$in})";
        $params = array_merge([$until], $ids);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return (int) $wpdb->query($wpdb->prepare($sql, $params));
    }

    /**
     * @param array<int,int> $ids
     */
    public function unmute(array $ids): int
    {
        global $wpdb;
        $table = $this->table();
        if (!$this->tableExists()) {
            return 0;
        }

        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), static function ($x) {
            return $x > 0;
        })));
        if (empty($ids)) {
            return 0;
        }

        $in = implode(',', array_fill(0, count($ids), '%d'));
        $sql = "UPDATE {$table} SET muted_until = NULL WHERE id IN ({$in})";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return (int) $wpdb->query($wpdb->prepare($sql, $ids));
    }

    /**
     * @param array<int,int> $ids
     */
    public function delete(array $ids): int
    {
        global $wpdb;
        $table = $this->table();
        if (!$this->tableExists()) {
            return 0;
        }

        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), static function ($x) {
            return $x > 0;
        })));
        if (empty($ids)) {
            return 0;
        }

        $in = implode(',', array_fill(0, count($ids), '%d'));
        $sql = "DELETE FROM {$table} WHERE id IN ({$in})";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return (int) $wpdb->query($wpdb->prepare($sql, $ids));
    }

    public function markSent(int $id, int $attempts, string $lastError = ''): void
    {
        global $wpdb;
        $table = $this->table();
        if ($id <= 0 || !$this->tableExists()) {
            return;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->update(
            $table,
            [
                'sent_at' => gmdate('Y-m-d H:i:s'),
                'send_attempts' => max(0, $attempts),
                'last_error' => $lastError !== '' ? sanitize_text_field($lastError) : null,
            ],
            ['id' => $id],
            ['%s', '%d', '%s'],
            ['%d']
        );
    }

    public function bumpAttempt(int $id, int $attempts, string $lastError): void
    {
        global $wpdb;
        $table = $this->table();
        if ($id <= 0 || !$this->tableExists()) {
            return;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->update(
            $table,
            [
                'send_attempts' => max(0, $attempts),
                'last_error' => $lastError !== '' ? sanitize_text_field($lastError) : null,
            ],
            ['id' => $id],
            ['%d', '%s'],
            ['%d']
        );
    }


    /**
     * List open (not acked, not muted) notifications for digests.
     *
     * @return array<int,array<string,mixed>>
     */
    public function listOpenForDigest(string $minSeverity = 'warn', int $limit = 25): array
    {
        if (!$this->tableExists()) {
            return [];
        }

        $minSeverity = sanitize_key($minSeverity);
        if (!in_array($minSeverity, ['info', 'warn', 'critical'], true)) {
            $minSeverity = 'warn';
        }

        $limit = max(1, min(200, $limit));

        $sevs = ['info', 'warn', 'critical'];
        if ($minSeverity === 'warn') {
            $sevs = ['warn', 'critical'];
        } elseif ($minSeverity === 'critical') {
            $sevs = ['critical'];
        }

        global $wpdb;
        $table = $this->table();
        $now = gmdate('Y-m-d H:i:s');

        $place = implode(',', array_fill(0, count($sevs), '%s'));

        $sql = "SELECT * FROM {$table}
                WHERE acknowledged_at IS NULL
                  AND (muted_until IS NULL OR muted_until < %s)
                  AND severity IN ({$place})
                ORDER BY (severity = 'critical') DESC, (severity = 'warn') DESC, id DESC
                LIMIT %d";

        $params = array_merge([$now], $sevs, [$limit]);

        /** @var array<int,array<string,mixed>> $rows */
        $rows = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);
        return is_array($rows) ? $rows : [];
    }

}