<?php

declare(strict_types=1);

namespace Sarcio\WordPress\Assistant;

/**
 * Every change the assistant makes, with what it replaced, so any of them can
 * be put back from the Assistant page: WordPress keeps revisions for posts
 * and CSS, but nothing for menus, options, products or plugin state. Also
 * owns the plugin's schema version, which covers the conversations table.
 */
final class Journal
{
    public const DB_VERSION = '4';
    public const OPTION = 'sarcio_db_version';

    private const LIST_LIMIT = 50;

    public static function table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'sarcio_changes';
    }

    public static function install(): void
    {
        global $wpdb;
        if (!isset($wpdb) || !is_object($wpdb)) {
            return;
        }
        if (!function_exists('dbDelta')) {
            if (!is_file(ABSPATH . 'wp-admin/includes/upgrade.php')) {
                return;
            }
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        $table = self::table();
        $collate = $wpdb->get_charset_collate();
        dbDelta("CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            created_at datetime NOT NULL,
            user_id bigint(20) unsigned NOT NULL,
            ability varchar(64) NOT NULL,
            summary text NOT NULL,
            target varchar(190) NOT NULL DEFAULT '',
            before_state longtext NULL,
            after_state longtext NULL,
            undone_at datetime NULL,
            PRIMARY KEY  (id),
            KEY created_at (created_at)
        ) {$collate};");
        dbDelta(Conversations::schema($collate));
        dbDelta(Requests::schema($collate));
        update_option(self::OPTION, self::DB_VERSION);
    }

    public static function installIfBehind(): void
    {
        if ((string) get_option(self::OPTION, '') !== self::DB_VERSION) {
            self::install();
        }
    }

    /**
     * @param array<string,mixed> $before
     * @param array<string,mixed> $after
     */
    public static function record(string $ability, string $summary, string $target, array $before, array $after): int
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- the plugin's own table.
        $wpdb->insert(self::table(), [
            'ability' => $ability,
            'after_state' => (string) wp_json_encode($after),
            'before_state' => (string) wp_json_encode($before),
            'created_at' => current_time('mysql', true),
            'summary' => $summary,
            'target' => mb_substr($target, 0, 190),
            'user_id' => get_current_user_id(),
        ], ['%s', '%s', '%s', '%s', '%s', '%s', '%d']);
        return (int) $wpdb->insert_id;
    }

    /** @return array{id:int,ability:string,summary:string,target:string,before:array<string,mixed>,after:array<string,mixed>,createdAt:string,userId:int,undoneAt:?string}|null */
    public static function get(int $id): ?array
    {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table.
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id), ARRAY_A);
        return is_array($row) ? self::fromRow($row) : null;
    }

    /** @return array<int,array{id:int,ability:string,summary:string,target:string,before:array<string,mixed>,after:array<string,mixed>,createdAt:string,userId:int,undoneAt:?string}> */
    public static function recent(int $limit = self::LIST_LIMIT): array
    {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table.
        $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$table} ORDER BY id DESC LIMIT %d", max(1, min(self::LIST_LIMIT, $limit))), ARRAY_A);
        return array_map([self::class, 'fromRow'], is_array($rows) ? $rows : []);
    }

    /**
     * Every change made between two UTC times, oldest first, for the report.
     *
     * @return array<int,array{id:int,ability:string,summary:string,target:string,before:array<string,mixed>,after:array<string,mixed>,createdAt:string,userId:int,undoneAt:?string}>
     */
    public static function between(string $fromUtc, string $toUtc, int $limit = 500): array
    {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table.
        $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$table} WHERE created_at >= %s AND created_at < %s ORDER BY id ASC LIMIT %d", $fromUtc, $toUtc, max(1, min(500, $limit))), ARRAY_A);
        return array_map([self::class, 'fromRow'], is_array($rows) ? $rows : []);
    }

    public static function markUndone(int $id): void
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table.
        $wpdb->update(self::table(), ['undone_at' => current_time('mysql', true)], ['id' => $id], ['%s'], ['%d']);
    }

    public static function drop(): void
    {
        global $wpdb;
        $table = self::table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- uninstall drops the plugin's own table.
        $wpdb->query("DROP TABLE IF EXISTS {$table}");
        Conversations::drop();
        delete_option(self::OPTION);
    }

    /** @param array<string,mixed> $row */
    private static function fromRow(array $row): array
    {
        $before = json_decode((string) ($row['before_state'] ?? ''), true);
        $after = json_decode((string) ($row['after_state'] ?? ''), true);
        return [
            'ability' => (string) $row['ability'],
            'after' => is_array($after) ? $after : [],
            'before' => is_array($before) ? $before : [],
            'createdAt' => (string) $row['created_at'],
            'id' => (int) $row['id'],
            'summary' => (string) $row['summary'],
            'target' => (string) ($row['target'] ?? ''),
            'undoneAt' => isset($row['undone_at']) && $row['undone_at'] !== null && $row['undone_at'] !== '' ? (string) $row['undone_at'] : null,
            'userId' => (int) $row['user_id'],
        ];
    }
}