<?php

declare(strict_types=1);

namespace WPStack\Sandbox\Infrastructure;

final class SessionRepository
{
    private \wpdb $db;
    private string $table;

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

    public function install(): void
    {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $this->db->get_charset_collate();

        dbDelta(
            "CREATE TABLE {$this->table} (
                session_id char(36) NOT NULL,
                blog_id bigint(20) unsigned NOT NULL DEFAULT 0,
                user_id bigint(20) unsigned NOT NULL DEFAULT 0,
                plugin_slug varchar(64) NOT NULL,
                visitor_hash char(64) NOT NULL,
                login_hash char(64) NOT NULL,
                login_used tinyint(1) unsigned NOT NULL DEFAULT 0,
                status varchar(20) NOT NULL DEFAULT 'provisioning',
                created_at datetime NOT NULL,
                expires_at datetime NOT NULL,
                extended tinyint(1) unsigned NOT NULL DEFAULT 0,
                initial_minutes smallint(5) unsigned NOT NULL DEFAULT 6,
                extension_minutes smallint(5) unsigned NOT NULL DEFAULT 6,
                maximum_minutes smallint(5) unsigned NOT NULL DEFAULT 50,
                extensions_allowed tinyint(1) unsigned NOT NULL DEFAULT 1,
                reset_count smallint(5) unsigned NOT NULL DEFAULT 0,
                last_error text NULL,
                PRIMARY KEY  (session_id),
                KEY visitor_status (visitor_hash,status),
                KEY expiry_status (expires_at,status),
                KEY blog_id (blog_id),
                KEY user_id (user_id),
                UNIQUE KEY login_hash (login_hash)
            ) {$charset};"
        );
    }

    /**
     * @param array<string, scalar|null> $session
     */
    public function insert(array $session): bool
    {
        return $this->db->insert($this->table, $session) !== false;
    }

    public function markActive(string $sessionId, int $blogId, int $userId): bool
    {
        return $this->db->update(
            $this->table,
            ['status' => 'active', 'blog_id' => $blogId, 'user_id' => $userId],
            ['session_id' => $sessionId],
            ['%s', '%d', '%d'],
            ['%s']
        ) !== false;
    }

    public function markFailed(string $sessionId, string $message): void
    {
        $this->db->update(
            $this->table,
            ['status' => 'failed', 'last_error' => mb_substr($message, 0, 1000)],
            ['session_id' => $sessionId],
            ['%s', '%s'],
            ['%s']
        );
    }

    /**
     * @return array<string, mixed>|null
     */
    public function findByLoginHash(string $hash): ?array
    {
        $sql = $this->db->prepare(
            "SELECT * FROM {$this->table} WHERE login_hash = %s LIMIT 1",
            $hash
        );
        $row = $this->db->get_row($sql, ARRAY_A);

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

    public function consumeLogin(string $sessionId): bool
    {
        $sql = $this->db->prepare(
            "UPDATE {$this->table}
             SET login_used = 1
             WHERE session_id = %s AND login_used = 0 AND status = 'active'",
            $sessionId
        );
        $this->db->query($sql);

        return $this->db->rows_affected === 1;
    }

    public function activeCount(string $visitorHash): int
    {
        $sql = $this->db->prepare(
            "SELECT COUNT(*) FROM {$this->table}
             WHERE visitor_hash = %s AND status = 'active' AND expires_at > %s",
            $visitorHash,
            gmdate('Y-m-d H:i:s')
        );

        return (int) $this->db->get_var($sql);
    }

    /**
     * @return array<string, mixed>|null
     */
    public function findActiveByVisitor(string $visitorHash): ?array
    {
        $sql = $this->db->prepare(
            "SELECT * FROM {$this->table}
             WHERE visitor_hash = %s AND status = 'active' AND expires_at > %s
             ORDER BY created_at DESC LIMIT 1",
            $visitorHash,
            gmdate('Y-m-d H:i:s')
        );
        $row = $this->db->get_row($sql, ARRAY_A);

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

    public function recentLaunchCount(string $visitorHash): int
    {
        $sql = $this->db->prepare(
            "SELECT COUNT(*) FROM {$this->table}
             WHERE visitor_hash = %s AND created_at >= %s",
            $visitorHash,
            gmdate('Y-m-d H:i:s', time() - HOUR_IN_SECONDS)
        );

        return (int) $this->db->get_var($sql);
    }

    public function globalActiveCount(): int
    {
        $sql = $this->db->prepare(
            "SELECT COUNT(*) FROM {$this->table}
             WHERE status = 'active' AND expires_at > %s",
            gmdate('Y-m-d H:i:s')
        );

        return (int) $this->db->get_var($sql);
    }

    /**
     * @return array<string, mixed>|null
     */
    public function findActiveByUser(int $userId): ?array
    {
        $sql = $this->db->prepare(
            "SELECT * FROM {$this->table}
             WHERE user_id = %d AND status = 'active' AND expires_at > %s
             ORDER BY created_at DESC LIMIT 1",
            $userId,
            gmdate('Y-m-d H:i:s')
        );
        $row = $this->db->get_row($sql, ARRAY_A);

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

    /**
     * @return list<array<string, mixed>>
     */
    public function dueForCleanup(int $limit = 25): array
    {
        $sql = $this->db->prepare(
            "SELECT * FROM {$this->table}
             WHERE (status = 'active' AND expires_at <= %s)
                OR status IN ('expired','replaced')
             ORDER BY expires_at ASC LIMIT %d",
            gmdate('Y-m-d H:i:s'),
            $limit
        );
        $rows = $this->db->get_results($sql, ARRAY_A);

        return is_array($rows) ? $rows : [];
    }

    public function updateExpiry(string $sessionId, string $expiresAt): bool
    {
        $sql = $this->db->prepare(
            "UPDATE {$this->table}
             SET expires_at = %s, extended = extended + 1
             WHERE session_id = %s AND status = 'active'",
            $expiresAt,
            $sessionId
        );
        $this->db->query($sql);

        return $this->db->rows_affected === 1;
    }

    public function setStatus(string $sessionId, string $status): bool
    {
        return $this->db->update(
            $this->table,
            ['status' => $status],
            ['session_id' => $sessionId],
            ['%s'],
            ['%s']
        ) !== false;
    }

    public function updateVisitorHash(string $sessionId, string $visitorHash): bool
    {
        return $this->db->update(
            $this->table,
            ['visitor_hash' => $visitorHash],
            ['session_id' => $sessionId, 'status' => 'active'],
            ['%s'],
            ['%s', '%s']
        ) !== false;
    }

    public function incrementReset(string $sessionId): void
    {
        $sql = $this->db->prepare(
            "UPDATE {$this->table} SET reset_count = reset_count + 1 WHERE session_id = %s",
            $sessionId
        );
        $this->db->query($sql);
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function recent(int $limit = 100): array
    {
        $sql = $this->db->prepare(
            "SELECT * FROM {$this->table} ORDER BY created_at DESC LIMIT %d",
            $limit
        );
        $rows = $this->db->get_results($sql, ARRAY_A);

        return is_array($rows) ? $rows : [];
    }
}
