<?php

namespace WPSN\Modules\Revisions;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

final class RevisionsRepository
{
    private Logger $logger;

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

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

    /**
     * @param array{object_type:string,object_id?:int,object_subtype?:string,action?:string,source?:string,actor_user_id?:int,before?:mixed,after?:mixed,meta?:mixed,created_at?:string} $row
     */
    public function insert(array $row): int
    {
        global $wpdb;

        $objectType = isset($row['object_type']) ? sanitize_key((string) $row['object_type']) : '';
        if ($objectType === '') {
            return 0;
        }

        $objectId = isset($row['object_id']) ? (int) $row['object_id'] : 0;
        $objectId = max(0, $objectId);

        $objectSubtype = isset($row['object_subtype']) ? sanitize_key((string) $row['object_subtype']) : '';
        $action = isset($row['action']) ? sanitize_key((string) $row['action']) : 'update';
        $source = isset($row['source']) ? sanitize_key((string) $row['source']) : '';
        $actor = isset($row['actor_user_id']) ? (int) $row['actor_user_id'] : 0;
        $actor = max(0, $actor);

        $createdAt = isset($row['created_at']) ? (string) $row['created_at'] : '';
        if ($createdAt === '') {
            $createdAt = current_time('mysql', true);
        }

        $beforeJson = null;
        if (array_key_exists('before', $row)) {
            $beforeJson = wp_json_encode($row['before']);
        }
        $afterJson = null;
        if (array_key_exists('after', $row)) {
            $afterJson = wp_json_encode($row['after']);
        }
        $metaJson = null;
        if (array_key_exists('meta', $row)) {
            $metaJson = wp_json_encode($row['meta']);
        }

        $res = $wpdb->insert(
            $this->table(),
            [
                'object_type' => $objectType,
                'object_id' => $objectId,
                'object_subtype' => $objectSubtype,
                'action' => $action,
                'source' => $source,
                'actor_user_id' => $actor,
                'before_json' => $beforeJson,
                'after_json' => $afterJson,
                'meta_json' => $metaJson,
                'created_at' => $createdAt,
                'rolled_back_at' => null,
                'rolled_back_by' => 0,
            ],
            [
                '%s',
                '%d',
                '%s',
                '%s',
                '%s',
                '%d',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%d',
            ]
        );

        if (!$res) {
            $this->logger->error('Revisions insert failed: ' . (string) $wpdb->last_error);
            return 0;
        }

        return (int) $wpdb->insert_id;
    }

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

        $id = (int) $id;
        if ($id <= 0) {
            return null;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $row = $wpdb->get_row($wpdb->prepare('SELECT * FROM ' . $this->table() . ' WHERE id = %d', $id), ARRAY_A);
        if (!is_array($row)) {
            return null;
        }

        $row['before_decoded'] = $this->decodeJson($row['before_json'] ?? null);
        $row['after_decoded'] = $this->decodeJson($row['after_json'] ?? null);
        $row['meta_decoded'] = $this->decodeJson($row['meta_json'] ?? null);

        return $row;
    }

    /**
     * @param array{source?:string,object_type?:string,object_id?:int,actor_user_id?:int,limit?:int,offset?:int} $args
     * @return array{rows:array<int,array<string,mixed>>,total:int}
     */
    public function list(array $args = []): array
    {
        global $wpdb;

        $where = [];
        $params = [];

        if (!empty($args['source'])) {
            $where[] = 'source = %s';
            $params[] = sanitize_key((string) $args['source']);
        }
        if (!empty($args['object_type'])) {
            $where[] = 'object_type = %s';
            $params[] = sanitize_key((string) $args['object_type']);
        }
        if (!empty($args['object_id'])) {
            $where[] = 'object_id = %d';
            $params[] = (int) $args['object_id'];
        }
        if (!empty($args['actor_user_id'])) {
            $where[] = 'actor_user_id = %d';
            $params[] = (int) $args['actor_user_id'];
        }

        $sqlWhere = '';
        if (!empty($where)) {
            $sqlWhere = ' WHERE ' . implode(' AND ', $where);
        }

        $limit = isset($args['limit']) ? (int) $args['limit'] : 50;
        $limit = max(1, min(200, $limit));
        $offset = isset($args['offset']) ? (int) $args['offset'] : 0;
        $offset = max(0, $offset);

        $countSql = 'SELECT COUNT(*) FROM ' . $this->table() . $sqlWhere;
        if (!empty($params)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $countSql = $wpdb->prepare($countSql, $params);
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $total = (int) $wpdb->get_var($countSql);

        $listSql = 'SELECT * FROM ' . $this->table() . $sqlWhere . ' ORDER BY id DESC LIMIT %d OFFSET %d';
        $listParams = array_merge($params, [$limit, $offset]);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare($listSql, $listParams);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($prepared, ARRAY_A);
        if (!is_array($rows)) {
            $rows = [];
        }

        foreach ($rows as &$r) {
            if (is_array($r)) {
                $r['before_decoded'] = $this->decodeJson($r['before_json'] ?? null);
                $r['after_decoded'] = $this->decodeJson($r['after_json'] ?? null);
                $r['meta_decoded'] = $this->decodeJson($r['meta_json'] ?? null);
            }
        }

        return [
            'rows' => array_values(array_filter($rows, 'is_array')),
            'total' => $total,
        ];
    }

    /**
     * Find the latest revision row for a specific object.
     *
     * Typical usage: "undo last change" buttons that need the newest not-yet-rolled-back update.
     *
     * @param array{object_type:string,object_id:int,sources?:array<int,string>,action?:string,only_unrolled?:bool} $args
     * @return array<string,mixed>|null
     */
    public function findLatest(array $args): ?array
    {
        global $wpdb;

        $objectType = isset($args['object_type']) ? sanitize_key((string) $args['object_type']) : '';
        $objectId = isset($args['object_id']) ? (int) $args['object_id'] : 0;
        if ($objectType === '' || $objectId <= 0) {
            return null;
        }

        $where = ['object_type = %s', 'object_id = %d'];
        $params = [$objectType, $objectId];

        if (!empty($args['action'])) {
            $where[] = 'action = %s';
            $params[] = sanitize_key((string) $args['action']);
        }

        $sources = [];
        if (!empty($args['sources']) && is_array($args['sources'])) {
            foreach ($args['sources'] as $s) {
                $s = sanitize_key((string) $s);
                if ($s !== '') {
                    $sources[] = $s;
                }
            }
            $sources = array_values(array_unique($sources));
        }

        if (!empty($sources)) {
            $ph = implode(',', array_fill(0, count($sources), '%s'));
            $where[] = 'source IN (' . $ph . ')';
            $params = array_merge($params, $sources);
        }

        if (!empty($args['only_unrolled'])) {
            // rolled_back_at can be NULL, empty, or "0000-00-00 00:00:00" depending on DB settings.
            $where[] = "(rolled_back_at IS NULL OR rolled_back_at = '' OR rolled_back_at = '0000-00-00 00:00:00')";
        }

        $sql = 'SELECT * FROM ' . $this->table() . ' WHERE ' . implode(' AND ', $where) . ' ORDER BY id DESC LIMIT 1';
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare($sql, $params);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $row = $wpdb->get_row($prepared, ARRAY_A);
        if (!is_array($row)) {
            return null;
        }

        $row['before_decoded'] = $this->decodeJson($row['before_json'] ?? null);
        $row['after_decoded'] = $this->decodeJson($row['after_json'] ?? null);
        $row['meta_decoded'] = $this->decodeJson($row['meta_json'] ?? null);

        return $row;
    }

    public function markRolledBack(int $id, int $byRevisionId): void
    {
        global $wpdb;
        $id = (int) $id;
        $byRevisionId = (int) $byRevisionId;
        if ($id <= 0 || $byRevisionId <= 0) {
            return;
        }

        $wpdb->update(
            $this->table(),
            [
                'rolled_back_at' => current_time('mysql', true),
                'rolled_back_by' => $byRevisionId,
            ],
            ['id' => $id],
            ['%s', '%d'],
            ['%d']
        );
    }

    /**
     * @param mixed $raw
     * @return mixed
     */
    private function decodeJson($raw)
    {
        if (!is_string($raw) || $raw === '') {
            return null;
        }
        $decoded = json_decode($raw, true);
        return $decoded;
    }
}
