<?php
/**
 * The change journal in the plugin's tables.
 *
 * @package MazeAi
 * @since   0.1.0
 */

declare(strict_types=1);

namespace MazeAi\Tools;

defined('ABSPATH') || exit;

use MazeAi\I18n\StoredMessage;
use Illuminate\MazeAssistant\Database\ConnectionInterface;

/**
 * `maze_graph_changes`: one row per change a write tool made. `idem_key` is unique, so the same call can never be
 * recorded twice, and undo is one conditional UPDATE, so two clicks undo once.
 *
 * @since 0.1.0
 */
class DatabaseChangeJournal extends ChangeJournal
{
    public function __construct(protected ConnectionInterface $db)
    {
    }

    public function findByKey(string $key): ?array
    {
        return $this->decode($this->db->selectRow("SELECT * FROM {$this->table()} WHERE idem_key = %s", [$key]));
    }

    public function find(int $id): ?array
    {
        return $this->decode($this->db->selectRow("SELECT * FROM {$this->table()} WHERE id = %d", [$id]));
    }

    protected function insert(array $row): int
    {
        $row['before']     = (string) wp_json_encode($row['before'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
        $row['after']      = (string) wp_json_encode($row['after'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
        $row['summary']    = mb_substr((string) $row['summary'], 0, 250);
        $row['status']     = 'applied';
        $row['created_at'] = gmdate('Y-m-d H:i:s');

        $id = $this->db->insert($this->table(), $row);
        if ($id === false) {
            throw new \RuntimeException('The change could not be recorded.');
        }

        return (int) $id;
    }

    protected function markUndone(int $id): bool
    {
        return $this->db->statement(
            "UPDATE {$this->table()} SET status = 'undone', undone_at = %s WHERE id = %d AND status = 'applied'",
            [gmdate('Y-m-d H:i:s'), $id]
        ) === 1;
    }

    protected function markApplied(int $id): void
    {
        $this->db->statement("UPDATE {$this->table()} SET status = 'applied', undone_at = NULL WHERE id = %d", [$id]);
    }

    public function recent(int $userId, string $status = '', int $limit = 50, int $offset = 0): array
    {
        $sessions = $this->db->getTable('maze_graph_sessions');
        $sql      = "SELECT c.*, s.uuid AS session_uuid, s.title AS session_title, s.agent AS session_agent FROM {$this->table()} c LEFT JOIN {$sessions} s ON s.id = c.session_id WHERE 1 = 1";
        $bindings = [];
        if ($userId > 0) {
            $sql       .= ' AND c.user_id = %d';
            $bindings[] = $userId;
        }
        if (in_array($status, ['applied', 'undone'], true)) {
            $sql       .= ' AND c.status = %s';
            $bindings[] = $status;
        }
        $sql       .= ' ORDER BY c.id DESC LIMIT %d OFFSET %d';
        $bindings[] = max(1, min(200, $limit));
        $bindings[] = max(0, $offset);

        return array_values(array_filter(array_map([$this, 'decode'], $this->db->select($sql, $bindings))));
    }

    public function countRecent(int $userId, string $status = ''): int
    {
        $sql      = "SELECT COUNT(*) FROM {$this->table()} WHERE 1 = 1";
        $bindings = [];
        if ($userId > 0) {
            $sql       .= ' AND user_id = %d';
            $bindings[] = $userId;
        }
        if (in_array($status, ['applied', 'undone'], true)) {
            $sql       .= ' AND status = %s';
            $bindings[] = $status;
        }

        return (int) $this->db->selectVar($sql, $bindings);
    }

    public function latestForObject(string $objectType, int $objectId, array $tools): ?array
    {
        if ($tools === [] || $objectId <= 0) {
            return null;
        }
        $in = implode(',', array_fill(0, count($tools), '%s'));

        return $this->decode($this->db->selectRow(
            "SELECT * FROM {$this->table()} WHERE object_type = %s AND object_id = %d AND status = 'applied' AND tool IN ({$in}) ORDER BY id DESC LIMIT 1",
            [$objectType, $objectId, ...$tools]
        ));
    }

    /**
     * The bounded, privacy-minimized change history for one authorized object.
     *
     * @param list<string> $tools
     * @return list<array{tool: string, status: string, summary: string, created_at: string}>
     */
    public function forObject(string $objectType, int $objectId, array $tools, int $limit = 20): array
    {
        $tools = array_values(array_unique(array_filter($tools, static fn ($tool): bool => is_string($tool) && preg_match('#^[a-z0-9-]+/[a-z0-9-]+$#', $tool) === 1)));
        if ($objectType !== 'post' || $objectId <= 0 || $tools === []) {
            return [];
        }
        $in = implode(',', array_fill(0, count($tools), '%s'));
        $rows = $this->db->select(
            "SELECT tool, status, summary, created_at FROM {$this->table()} WHERE object_type = %s AND object_id = %d AND tool IN ({$in}) ORDER BY id DESC LIMIT %d",
            [$objectType, $objectId, ...$tools, max(1, min(20, $limit))]
        );

        return array_values(array_map(static fn (array $row): array => [
            'tool'       => (string) ($row['tool'] ?? ''),
            'status'     => (string) ($row['status'] ?? ''),
            'summary'    => StoredMessage::renderInSourceLanguage((string) ($row['summary'] ?? '')),
            'created_at' => (string) ($row['created_at'] ?? ''),
        ], $rows));
    }

    public function latestForSessions(array $sessionIds, array $tools): array
    {
        $sessionIds = array_values(array_unique(array_filter(array_map('intval', $sessionIds), static fn (int $id): bool => $id > 0)));
        if ($sessionIds === [] || $tools === []) {
            return [];
        }
        $ids = implode(',', array_fill(0, count($sessionIds), '%d'));
        $in  = implode(',', array_fill(0, count($tools), '%s'));
        $out = [];
        foreach ($this->db->select("SELECT * FROM {$this->table()} WHERE status = 'applied' AND tool IN ({$in}) AND session_id IN ({$ids}) ORDER BY id ASC", [...$tools, ...$sessionIds]) as $row) {
            $change = $this->decode($row);
            if ($change !== null) {
                $out[$change['session_id']] = $change;
            }
        }

        return $out;
    }

    public function latestForObjects(int $userId, string $objectType, array $objectIds, array $tools): array
    {
        $objectIds = array_values(array_unique(array_filter(array_map('intval', $objectIds), static fn (int $id): bool => $id > 0)));
        if ($objectIds === [] || $tools === []) {
            return [];
        }
        $ids = implode(',', array_fill(0, count($objectIds), '%d'));
        $in  = implode(',', array_fill(0, count($tools), '%s'));
        $out = [];
        foreach ($this->db->select(
            "SELECT * FROM {$this->table()} WHERE user_id = %d AND object_type = %s AND status = 'applied' AND tool IN ({$in}) AND object_id IN ({$ids}) ORDER BY id ASC",
            [$userId, $objectType, ...$tools, ...$objectIds]
        ) as $row) {
            $change = $this->decode($row);
            if ($change !== null) {
                $out[$change['object_id']] = $change;
            }
        }

        return $out;
    }

    public function latestFor(int $sessionId, array $tools): ?array
    {
        if ($tools === []) {
            return null;
        }
        $in = implode(',', array_fill(0, count($tools), '%s'));

        return $this->decode($this->db->selectRow(
            "SELECT * FROM {$this->table()} WHERE session_id = %d AND status = 'applied' AND tool IN ({$in}) ORDER BY id DESC LIMIT 1",
            [$sessionId, ...$tools]
        ));
    }

    public function forgetSessions(array $sessionIds): void
    {
        $ids = array_values(array_filter(array_map('intval', $sessionIds)));
        if ($ids === []) {
            return;
        }
        $in = implode(',', array_fill(0, count($ids), '%d'));
        $this->db->statement("DELETE FROM {$this->table()} WHERE session_id IN ({$in})", $ids);
    }

    /**
     * @param array<string, mixed>|null $row
     * @return array<string, mixed>|null
     */
    protected function decode(?array $row): ?array
    {
        if ($row === null) {
            return null;
        }
        foreach (['before', 'after'] as $key) {
            $row[$key] = is_string($row[$key] ?? null) ? (json_decode($row[$key], true) ?: []) : [];
        }
        foreach (['id', 'session_id', 'user_id', 'object_id'] as $key) {
            $row[$key] = (int) ($row[$key] ?? 0);
        }

        return $row;
    }

    protected function table(): string
    {
        return $this->db->getTable('maze_graph_changes');
    }
}