<?php

declare(strict_types=1);

namespace PpPhp\Core;

use PpPhp\Core\Auth\Permissions;
use PpPhp\Core\Auth\RowSecurity;

/**
 * PpApi — the server-side companion to pp.js (window.Runner).
 *
 * Authors reach it in every server event, Custom-Button PHP, and
 * `action_<name>()` function through the global `pp()` helper. It gives three
 * things, mirroring the client Runner:
 *
 *   1. Context   — pp()->user(), role(), can(), page(), param(), id().
 *   2. Data      — permission-aware find/insert/update/delete + raw reads,
 *                  built on the same DB primitives the generated pages use
 *                  (column coercion, identifier quoting, row-level security).
 *   3. A command bus — pp()->toast(), setControl(), hide(), reloadGrid()… queue
 *                  UI commands that pp.js applies live in the browser when the
 *                  request returns (via Runner.apply). So PHP can *drive* the
 *                  page — no hand-written client-after code.
 *
 * The command list is returned by api/event.php and api/button.php alongside
 * `result`, and drained with pullCommands().
 */
final class PpApi
{
    private static ?self $instance = null;

    /** @var array<int, array<string, mixed>> queued client commands */
    private array $commands = [];

    /** @var array<string, mixed> the request params (what Client Before sent) */
    private array $params = [];

    /** @var array{table: string, action: string, id: mixed} page context */
    private array $ctx = ['table' => '', 'action' => '', 'id' => null];

    /** @var array<int, mixed> keys of the rows selected on a List page */
    private array $keys = [];

    /** One instance per request. */
    public static function instance(): self
    {
        return self::$instance ??= new self();
    }

    /** Test/reset seam. */
    public static function reset(): void
    {
        self::$instance = null;
    }

    /**
     * Seed the request params + page context. Called by api/event.php and
     * api/button.php before the author's code runs.
     *
     * @param array<string, mixed> $params
     * @param array<string, mixed> $ctx
     */
    public function boot(array $params = [], array $ctx = []): self
    {
        if ($params !== []) {
            $this->params = $params;
        }
        $this->ctx['table']  = (string) ($ctx['table'] ?? $params['__table'] ?? $_REQUEST['table'] ?? $this->ctx['table']);
        $this->ctx['action'] = (string) ($ctx['action'] ?? $params['__action'] ?? $this->ctx['action']);
        $this->ctx['id']     = $ctx['id'] ?? $params['id'] ?? $params['__id'] ?? $_REQUEST['id'] ?? $this->ctx['id'];
        if (isset($ctx['keys']) || isset($params['__keys'])) {
            $this->keys = array_values((array) ($ctx['keys'] ?? $params['__keys'] ?? []));
        }
        return $this;
    }

    // ── Request context ───────────────────────────────────────────────

    /** @return array<string, mixed> */
    public function params(): array
    {
        return $this->params;
    }

    public function param(string $key, mixed $default = null): mixed
    {
        return $this->params[$key] ?? $default;
    }

    /** @return array{table: string, action: string, id: mixed} */
    public function page(): array
    {
        return $this->ctx;
    }

    public function id(): mixed
    {
        return $this->ctx['id'];
    }

    // ── Current user ──────────────────────────────────────────────────

    /** @return array<string, mixed> the current user's row (empty if none). */
    public function user(): array
    {
        $u = Security::CurrentUserData();
        return is_array($u) ? $u : [];
    }

    public function userId(): mixed
    {
        $u = $this->user();
        return $u['id'] ?? $u['user_id'] ?? null;
    }

    public function username(): string
    {
        return (string) Security::CurrentUsername();
    }

    public function role(): string
    {
        return (string) Security::CurrentUserGroup();
    }

    /** Permission check that returns a bool (does not redirect). */
    public function can(string $action, string $table): bool
    {
        return Permissions::can($action, $table);
    }

    // ── Data (permission-aware) ───────────────────────────────────────

    /**
     * One record by id (or another key), with row-level security applied.
     *
     * @return array<string, mixed>|null
     */
    public function find(string $table, mixed $id, string $key = 'id'): ?array
    {
        $t = DB::QuoteIdentifier($table);
        $where = RowSecurity::mergeWhere('WHERE ' . DB::QuoteIdentifier($key) . ' = ?', $table);
        $rows = DB::FetchAll("SELECT * FROM {$t} {$where} LIMIT 1", [$id]);
        return (is_array($rows) && $rows !== []) ? $rows[0] : null;
    }

    /**
     * A fluent, safe query builder for a table (row-level security applied):
     *   pp()->table('orders')->where('status','open')->orderBy('created','desc')->get();
     */
    public function table(string $table): PpQuery
    {
        return new PpQuery($table);
    }

    // ── Current record & selected rows (button_object parity) ─────────

    /**
     * The record the current page/button is acting on (from the page context
     * table + id). PHPRunner's $button->getCurrentRecord().
     *
     * @return array<string, mixed>|null
     */
    public function record(?string $key = null): ?array
    {
        $t = $this->ctx['table'];
        $id = $this->id();
        if ($t === '' || $id === null || $id === '') {
            return null;
        }
        return $this->find($t, $id, $key ?? 'id');
    }

    /** Keys of the rows ticked on a List page. */
    public function selectedKeys(): array
    {
        return $this->keys;
    }

    /**
     * Full records for the rows selected on a List page (row-level security
     * applied). PHPRunner iterates these with getNextSelectedRecord().
     *
     * @return array<int, array<string, mixed>>
     */
    public function selectedRecords(string $key = 'id'): array
    {
        $t = $this->ctx['table'];
        if ($t === '' || $this->keys === []) {
            return [];
        }
        return $this->table($t)->whereIn($key, $this->keys)->get();
    }

    /**
     * Run a callback for each selected record — the clean equivalent of
     * PHPRunner's `while ($data = $button->getNextSelectedRecord())`.
     */
    public function eachSelected(callable $fn, string $key = 'id'): self
    {
        foreach ($this->selectedRecords($key) as $rec) {
            $fn($rec);
        }
        return $this;
    }

    /**
     * Raw multi-row read.
     *
     * @param array<int, mixed> $params
     * @return array<int, array<string, mixed>>
     */
    public function rows(string $sql, array $params = []): array
    {
        $r = DB::FetchAll($sql, $params);
        return is_array($r) ? $r : [];
    }

    /**
     * Raw single-row read.
     *
     * @param array<int, mixed> $params
     * @return array<string, mixed>|null
     */
    public function row(string $sql, array $params = []): ?array
    {
        $r = DB::FetchAll($sql, $params);
        return (is_array($r) && $r !== []) ? $r[0] : null;
    }

    /** Raw single-value read (first column of the first row). */
    public function value(string $sql, array $params = []): mixed
    {
        return DB::FetchOne($sql, $params);
    }

    /**
     * Insert a record (needs 'add' rights). Returns the new id, or false.
     *
     * @param array<string, mixed> $data
     */
    public function insert(string $table, array $data): int|false
    {
        if (!$this->can('add', $table)) {
            $this->toast('error', 'You are not allowed to add ' . $table . '.');
            return false;
        }
        return DB::Insert($table, $data);
    }

    /**
     * Update a record (needs 'edit' rights). Returns affected rows, or false.
     *
     * @param array<string, mixed> $data
     */
    public function update(string $table, mixed $id, array $data, string $key = 'id'): int|false
    {
        if (!$this->can('edit', $table)) {
            $this->toast('error', 'You are not allowed to edit ' . $table . '.');
            return false;
        }
        return DB::Update($table, $data, [$key => $id]);
    }

    /** Delete a record (needs 'delete' rights). Returns affected rows, or false. */
    public function delete(string $table, mixed $id, string $key = 'id'): int|false
    {
        if (!$this->can('delete', $table)) {
            $this->toast('error', 'You are not allowed to delete ' . $table . '.');
            return false;
        }
        return DB::Delete($table, [$key => $id]);
    }

    /**
     * Insert a row, or update it if one already exists on the conflict columns —
     * a driver-aware UPSERT. Needs both 'add' and 'edit' rights.
     *
     *   pp()->upsert('settings', ['user_id' => $uid, 'theme' => 'dark'], ['user_id']);
     *
     * @param array<string, mixed>       $data        the full row (insert values)
     * @param array<int, string>         $conflict    unique column(s) that detect an existing row
     * @param array<int, string>|null    $updateCols  which columns to update on conflict (default: all but the conflict cols)
     * @return int  rows affected (>=1 on success, 0 on failure)
     */
    public function upsert(string $table, array $data, array $conflict = ['id'], ?array $updateCols = null): int
    {
        if ($data === []) {
            return 0;
        }
        if (!$this->can('add', $table) || !$this->can('edit', $table)) {
            $this->toast('error', 'You are not allowed to upsert ' . $table . '.');
            return 0;
        }

        $driver  = DB::CurrentDriver();
        $cols    = array_keys($data);
        $qcols   = array_map([DB::class, 'QuoteIdentifier'], $cols);
        $ph      = implode(', ', array_fill(0, count($cols), '?'));
        $vals    = array_values($data);
        $upCols  = $updateCols ?? array_values(array_diff($cols, $conflict));
        $qi      = static fn(string $c): string => DB::QuoteIdentifier($c);
        $insHead = 'INSERT INTO ' . $qi($table) . ' (' . implode(', ', $qcols) . ') VALUES (' . $ph . ')';

        // ── MySQL / MariaDB ──
        if ($driver === 'mysql') {
            if ($upCols === []) {
                $res = DB::Query('INSERT IGNORE INTO ' . $qi($table) . ' (' . implode(', ', $qcols) . ') VALUES (' . $ph . ')', $vals);
                return $res === false ? 0 : $res->rowCount();
            }
            $set = implode(', ', array_map(static fn(string $c): string => $qi($c) . ' = VALUES(' . $qi($c) . ')', $upCols));
            $res = DB::Query($insHead . ' ON DUPLICATE KEY UPDATE ' . $set, $vals);
            return $res === false ? 0 : max(1, $res->rowCount());
        }

        // ── PostgreSQL / SQLite ──
        if ($driver === 'pgsql' || $driver === 'sqlite') {
            $confList = implode(', ', array_map($qi, $conflict));
            if ($upCols === []) {
                $res = DB::Query($insHead . ' ON CONFLICT (' . $confList . ') DO NOTHING', $vals);
                return $res === false ? 0 : $res->rowCount();
            }
            $set = implode(', ', array_map(static fn(string $c): string => $qi($c) . ' = excluded.' . $qi($c), $upCols));
            $res = DB::Query($insHead . ' ON CONFLICT (' . $confList . ') DO UPDATE SET ' . $set, $vals);
            return $res === false ? 0 : max(1, $res->rowCount());
        }

        // ── Portable fallback (SQL Server, etc.): update-by-conflict, else insert ──
        $where     = implode(' AND ', array_map(static fn(string $c): string => $qi($c) . ' = ?', $conflict));
        $whereVals = array_map(static fn(string $c) => $data[$c] ?? null, $conflict);
        if ($upCols !== []) {
            $set     = implode(', ', array_map(static fn(string $c): string => $qi($c) . ' = ?', $upCols));
            $setVals = array_map(static fn(string $c) => $data[$c], $upCols);
            $u = DB::Query('UPDATE ' . $qi($table) . ' SET ' . $set . ' WHERE ' . $where, array_merge($setVals, $whereVals));
            if ($u !== false && $u->rowCount() > 0) {
                return $u->rowCount();
            }
        } elseif (DB::FetchOne('SELECT 1 FROM ' . $qi($table) . ' WHERE ' . $where, $whereVals)) {
            return 0;   // already there, nothing to update
        }
        return DB::Insert($table, $data) === false ? 0 : 1;
    }

    /** Build an app URL from the deployment base. */
    public function url(string $path = ''): string
    {
        $base = defined('APP_BASE') ? APP_BASE : '';
        return $base . '/' . ltrim($path, '/');
    }

    // ── Transactions ──────────────────────────────────────────────────

    /**
     * Run a callback inside a database transaction — commit on success, roll
     * back on any exception (which is re-thrown). Returns the callback's value.
     * The callback receives $this.
     *
     *   pp()->transaction(function ($pp) {
     *       $id = $pp->insert('orders', [...]);
     *       $pp->insert('order_items', ['order_id' => $id, ...]);
     *   });
     */
    public function transaction(callable $fn): mixed
    {
        $pdo = DB::Pdo();
        $owns = !$pdo->inTransaction();
        if ($owns) {
            $pdo->beginTransaction();
        }
        try {
            $out = $fn($this);
            if ($owns) {
                $pdo->commit();
            }
            return $out;
        } catch (\Throwable $e) {
            if ($owns && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $e;
        }
    }

    public function begin(): self
    {
        if (!DB::Pdo()->inTransaction()) {
            DB::Pdo()->beginTransaction();
        }
        return $this;
    }

    public function commit(): self
    {
        if (DB::Pdo()->inTransaction()) {
            DB::Pdo()->commit();
        }
        return $this;
    }

    public function rollback(): self
    {
        if (DB::Pdo()->inTransaction()) {
            DB::Pdo()->rollBack();
        }
        return $this;
    }

    // ── Savepoints (partial rollback inside a transaction) ────────────

    /** Create a savepoint (must be inside a transaction). */
    public function savepoint(string $name): self
    {
        $this->requireTx('savepoint');
        $sql = $this->savepointSql('save', $this->safeName($name));
        if ($sql !== '') {
            DB::Pdo()->exec($sql);
        }
        return $this;
    }

    /** Roll back to a savepoint — undoes work since it, but keeps the transaction open. */
    public function rollbackTo(string $name): self
    {
        $this->requireTx('rollbackTo');
        DB::Pdo()->exec($this->savepointSql('rollbackto', $this->safeName($name)));
        return $this;
    }

    /** Release (forget) a savepoint. */
    public function releaseSavepoint(string $name): self
    {
        $this->requireTx('releaseSavepoint');
        $sql = $this->savepointSql('release', $this->safeName($name));
        if ($sql !== '') {
            DB::Pdo()->exec($sql);
        }
        return $this;
    }

    /**
     * Run $fn inside a savepoint: release it on success, or roll back to it on
     * failure and CONTINUE the outer transaction (returns false). Ideal for a
     * "skip the bad row, keep the good ones" loop.
     *
     *   pp()->transaction(function ($pp) {
     *       foreach ($rows as $r) {
     *           $pp->trySavepoint('row', fn($pp) => $pp->insert('items', $r));
     *       }
     *   });
     *
     * @return bool  true if $fn completed, false if it threw (and was rolled back)
     */
    public function trySavepoint(string $name, callable $fn): bool
    {
        $this->requireTx('trySavepoint');
        $safe = $this->safeName($name);
        $this->savepoint($safe);
        try {
            $fn($this);
            $this->releaseSavepoint($safe);
            return true;
        } catch (\Throwable $e) {
            $this->rollbackTo($safe);
            return false;
        }
    }

    private function requireTx(string $what): void
    {
        if (!DB::Pdo()->inTransaction()) {
            throw new \RuntimeException($what . '() requires an active transaction — wrap it in pp()->transaction().');
        }
    }

    private function safeName(string $name): string
    {
        $n = preg_replace('/[^A-Za-z0-9_]/', '', $name) ?? '';
        return $n === '' ? 'sp' : $n;
    }

    /** Driver-aware savepoint SQL (SQL Server uses SAVE/ROLLBACK TRANSACTION). */
    private function savepointSql(string $op, string $name): string
    {
        $driver = DB::CurrentDriver();
        if ($driver === 'sqlsrv' || $driver === 'dblib') {
            return match ($op) {
                'save'       => 'SAVE TRANSACTION ' . $name,
                'rollbackto' => 'ROLLBACK TRANSACTION ' . $name,
                default      => '',   // SQL Server has no RELEASE
            };
        }
        return match ($op) {
            'save'       => 'SAVEPOINT ' . $name,
            'rollbackto' => 'ROLLBACK TO SAVEPOINT ' . $name,
            'release'    => 'RELEASE SAVEPOINT ' . $name,
            default      => '',
        };
    }

    // ── Client command bus (server → live client) ─────────────────────
    // Each of these queues a directive that pp.js's Runner.apply() runs in the
    // browser when the request returns. All chainable.

    /** @param array<string, mixed> $data */
    private function push(string $cmd, array $data = []): self
    {
        $this->commands[] = array_merge(['cmd' => $cmd], $data);
        return $this;
    }

    /** Toast a message (type: success|error|warning|info). */
    public function toast(string $type, string $message): self
    {
        return $this->push('toast', ['type' => $type, 'message' => $message]);
    }

    public function alert(string $title, string $text = '', string $icon = 'info'): self
    {
        return $this->push('alert', ['title' => $title, 'text' => $text, 'icon' => $icon]);
    }

    /** Set a field's value in the browser. */
    public function setControl(string $field, mixed $value): self
    {
        return $this->push('setControl', ['field' => $field, 'value' => $value]);
    }

    /** @param array<string, mixed> $map */
    public function setData(array $map): self
    {
        return $this->push('setData', ['data' => $map]);
    }

    public function hide(string $field): self
    {
        return $this->push('hide', ['field' => $field]);
    }

    public function show(string $field): self
    {
        return $this->push('show', ['field' => $field]);
    }

    /** Inline message under a field (tone: error|success|info). */
    public function message(string $field, string $message, string $tone = 'error'): self
    {
        return $this->push('message', ['field' => $field, 'message' => $message, 'tone' => $tone]);
    }

    public function clearMessage(string $field): self
    {
        return $this->push('clearMessage', ['field' => $field]);
    }

    public function focus(string $field): self
    {
        return $this->push('focus', ['field' => $field]);
    }

    public function addClass(string $field, string $class): self
    {
        return $this->push('addClass', ['field' => $field, 'class' => $class]);
    }

    public function addStyle(string $field, string $css): self
    {
        return $this->push('addStyle', ['field' => $field, 'css' => $css]);
    }

    /** Reload the list grid (after a server change). */
    public function reloadGrid(): self
    {
        return $this->push('reloadGrid');
    }

    /** Reload the whole page. */
    public function reload(): self
    {
        return $this->push('reload');
    }

    public function redirect(string $url): self
    {
        return $this->push('redirect', ['url' => $url]);
    }

    /**
     * Open a pp.dialog.form() in the browser with this config.
     *
     * @param array<string, mixed> $config
     */
    public function openForm(array $config): self
    {
        return $this->push('openForm', ['config' => $config]);
    }

    /** Set a proxy value (also for the current render, if pp_set_proxy exists). */
    public function setProxy(string $key, mixed $value): self
    {
        if (function_exists('pp_set_proxy')) {
            pp_set_proxy($key, $value);
        }
        return $this->push('setProxy', ['key' => $key, 'value' => $value]);
    }

    /** Open a URL (optionally in a new tab). */
    public function openUrl(string $url, bool $newTab = false): self
    {
        return $this->push('openUrl', ['url' => $url, 'newTab' => $newTab]);
    }

    /** Trigger a file download in the browser. */
    public function download(string $url): self
    {
        return $this->push('download', ['url' => $url]);
    }

    /** Scroll a field into view. */
    public function scrollTo(string $field): self
    {
        return $this->push('scrollTo', ['field' => $field]);
    }

    /** Briefly flash a field to draw the eye. */
    public function highlight(string $field): self
    {
        return $this->push('highlight', ['field' => $field]);
    }

    /** Close any open SweetAlert/dialog. */
    public function closeDialog(): self
    {
        return $this->push('closeDialog');
    }

    /** console.log() in the browser — handy for debugging an event. */
    public function log(mixed $value): self
    {
        return $this->push('log', ['value' => $value]);
    }

    /**
     * Dispatch a custom DOM event on the page — the extensible escape hatch.
     * Author JS listens with document.addEventListener('pp:<name>', e => e.detail).
     *
     * @param mixed $detail
     */
    public function emit(string $name, mixed $detail = null): self
    {
        return $this->push('emit', ['name' => $name, 'detail' => $detail]);
    }

    /**
     * Call a global JavaScript function by name with arguments — drive any
     * client code the author defined.
     *
     * @param array<int, mixed> $args
     */
    public function call(string $fn, array $args = []): self
    {
        return $this->push('call', ['fn' => $fn, 'args' => $args]);
    }

    // ── Command queue access ──────────────────────────────────────────

    /** @return array<int, array<string, mixed>> */
    public function commands(): array
    {
        return $this->commands;
    }

    /**
     * Return the queued commands and clear them.
     *
     * @return array<int, array<string, mixed>>
     */
    public function pullCommands(): array
    {
        $c = $this->commands;
        $this->commands = [];
        return $c;
    }

    public function hasCommands(): bool
    {
        return $this->commands !== [];
    }
}
