<?php

declare(strict_types=1);

namespace FreshetBackup;

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- a backup reads the tables themselves; there is nothing to cache.
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- exception messages are stored in the run status and escaped where they are printed (Admin::render(), Admin::notices()).

/**
 * The Database reads, through $wpdb. Identifiers go through %i, values through
 * %s / %d — nothing is concatenated into a query.
 */
final class WpdbDatabase implements Database
{
    private ?string $timeZone = null;

    public function __construct(private readonly \wpdb $wpdb)
    {
    }

    public function tables(): array
    {
        $wpdb = $this->wpdb;
        $rows = $wpdb->get_results('SHOW FULL TABLES', ARRAY_N);
        $tables = [];
        foreach ((array) $rows as $row) {
            if (isset($row[0], $row[1]) && strtoupper((string) $row[1]) === 'BASE TABLE') {
                $tables[] = (string) $row[0];
            }
        }
        $wpdb->flush();
        sort($tables, SORT_STRING);

        return $tables;
    }

    public function createStatement(string $table): string
    {
        $wpdb = $this->wpdb;
        $row = $wpdb->get_row($wpdb->prepare('SHOW CREATE TABLE %i', $table), ARRAY_N);
        if (!is_array($row) || !isset($row[1])) {
            throw new DumpError(sprintf(
                /* translators: %s: database table name */
                __('Could not read the structure of table %s, so no backup was made. Check that table with your host\'s database tool, then try again.', 'freshet-backup'),
                $table
            ));
        }

        return (string) $row[1];
    }

    public function columns(string $table): array
    {
        $wpdb = $this->wpdb;
        $rows = $wpdb->get_results($wpdb->prepare('SHOW COLUMNS FROM %i', $table), ARRAY_A);
        $columns = [];
        foreach ((array) $rows as $row) {
            $columns[] = [
                'name'      => (string) $row['Field'],
                'type'      => strtolower((string) $row['Type']),
                'generated' => self::isGenerated((string) ($row['Extra'] ?? '')),
            ];
        }

        return $columns;
    }

    /**
     * A computed column, which an INSERT must leave out: Extra reads "VIRTUAL
     * GENERATED" / "STORED GENERATED" (MariaDB 10.1: "VIRTUAL" / "PERSISTENT").
     * Not MySQL 8's "DEFAULT_GENERATED", which only means the column has an
     * expression default such as CURRENT_TIMESTAMP — its value is real data.
     */
    public static function isGenerated(string $extra): bool
    {
        return (bool) preg_match('/\b(VIRTUAL|STORED|PERSISTENT)\b/i', $extra);
    }

    public function primaryKey(string $table): array
    {
        $wpdb = $this->wpdb;
        $rows = $wpdb->get_results($wpdb->prepare('SHOW KEYS FROM %i WHERE Key_name = %s', $table, 'PRIMARY'), ARRAY_A);
        $keys = [];
        foreach ((array) $rows as $row) {
            $keys[(int) $row['Seq_in_index']] = (string) $row['Column_name'];
        }
        ksort($keys);

        return array_values($keys);
    }

    public function rows(string $table, array $columns, ?string $key, string|int|null $after, bool $numericKey, int $offset, int $limit): array
    {
        $wpdb = $this->wpdb;
        $list = implode(', ', array_fill(0, count($columns), '%i'));

        if ($key !== null && $after === null) {
            $sql = "SELECT $list FROM %i ORDER BY %i LIMIT %d";
            $args = [...$columns, $table, $key, $limit];
        } elseif ($key !== null) {
            $sql = "SELECT $list FROM %i WHERE %i > " . ($numericKey ? '%d' : '%s') . ' ORDER BY %i LIMIT %d';
            $args = [...$columns, $table, $key, $after, $key, $limit];
        } else {
            $sql = "SELECT $list FROM %i LIMIT %d, %d";
            $args = [...$columns, $table, $offset, $limit];
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql holds only %i placeholders built above; every value is bound by prepare().
        $rows = $wpdb->get_results($wpdb->prepare($sql, $args), ARRAY_N);
        if ($wpdb->last_error !== '') {
            throw new DumpError(sprintf(
                /* translators: 1: database table name, 2: the database's own error message */
                __('Reading table %1$s failed (%2$s), so no backup was made. Try again; if it keeps failing, check that table with your host\'s database tool.', 'freshet-backup'),
                $table,
                $wpdb->last_error
            ));
        }
        $wpdb->flush();

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

    public function averageRowLength(string $table): int
    {
        $wpdb = $this->wpdb;
        return (int) $wpdb->get_var($wpdb->prepare(
            'SELECT AVG_ROW_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
            $table
        ));
    }

    public function charset(): string
    {
        $wpdb = $this->wpdb;
        return $wpdb->charset ?: 'utf8mb4';
    }

    public function beginSnapshot(): void
    {
        $wpdb = $this->wpdb;
        $this->timeZone = (string) $wpdb->get_var('SELECT @@session.time_zone');
        $wpdb->query("SET time_zone = '+00:00'");
        $wpdb->query('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
        $wpdb->query('START TRANSACTION WITH CONSISTENT SNAPSHOT');
    }

    public function endSnapshot(): void
    {
        $wpdb = $this->wpdb;
        $wpdb->query('COMMIT');
        if ($this->timeZone !== null && $this->timeZone !== '') {
            $wpdb->query($wpdb->prepare('SET time_zone = %s', $this->timeZone));
        }
        $this->timeZone = null;
    }
}