<?php

declare(strict_types=1);

namespace PpPhp\Core;

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

/**
 * PpQuery — a tiny, safe, fluent query builder returned by pp()->table().
 *
 *   pp()->table('orders')
 *       ->where('status', 'open')
 *       ->where('total', '>', 100)
 *       ->orderBy('created', 'desc')
 *       ->limit(20)
 *       ->get();
 *
 * Identifiers are validated + quoted for the active driver; values are always
 * bound as placeholders; row-level security is merged into every read — the same
 * guards the generated list pages use. Terminal methods: get / first / value /
 * count / exists / pluck / paginate. toSql() returns [sql, bindings] for
 * debugging (and offline testing) without hitting the database.
 */
final class PpQuery
{
    /** sentinel for the 2-arg where() form */
    private const NONE = "\0__pp_none__\0";

    private const OPERATORS = ['=', '!=', '<>', '<', '<=', '>', '>=', 'like', 'not like', 'is', 'is not'];

    private string $table;

    /** @var array<int, string> */
    private array $selects = ['*'];

    /** @var array<int, string> compiled WHERE fragments (ANDed) */
    private array $wheres = [];

    /** @var array<int, mixed> WHERE bindings, in order */
    private array $bindings = [];

    /** @var array<int, mixed> HAVING bindings (appended after WHERE bindings) */
    private array $havingBindings = [];

    /** @var array<int, string> ORDER BY fragments */
    private array $orders = [];

    /** @var array<int, string> JOIN clauses */
    private array $joins = [];

    /** @var array<int, string> GROUP BY columns */
    private array $groups = [];

    /** @var array<int, string> HAVING fragments (ANDed) */
    private array $havings = [];

    private bool $distinct = false;

    private ?int $limit = null;
    private ?int $offset = null;

    public function __construct(string $table)
    {
        $this->table = $table;
    }

    // ── building ──────────────────────────────────────────────────────

    /** select('id','name') — defaults to *. Use selectRaw() for expressions. */
    public function select(string ...$cols): self
    {
        $this->selects = $cols !== [] ? array_map([self::class, 'qi'], $cols) : ['*'];
        return $this;
    }

    /** selectRaw('SUM(total) AS grand') — raw expression, no quoting (author-trusted). */
    public function selectRaw(string $expr): self
    {
        if ($this->selects === ['*']) {
            $this->selects = [];
        }
        $this->selects[] = $expr;
        return $this;
    }

    public function distinct(): self
    {
        $this->distinct = true;
        return $this;
    }

    /** join('order_items', 'orders.id', '=', 'order_items.order_id'). */
    public function join(string $table, string $first, string $op, string $second, string $type = 'INNER'): self
    {
        $op = in_array(trim($op), ['=', '!=', '<>', '<', '<=', '>', '>='], true) ? trim($op) : '=';
        $this->joins[] = strtoupper($type) . ' JOIN ' . self::qi($table)
            . ' ON ' . self::qi($first) . ' ' . $op . ' ' . self::qi($second);
        return $this;
    }

    public function leftJoin(string $table, string $first, string $op, string $second): self
    {
        return $this->join($table, $first, $op, $second, 'LEFT');
    }

    public function groupBy(string ...$cols): self
    {
        foreach ($cols as $c) {
            $this->groups[] = self::qi($c);
        }
        return $this;
    }

    /** having('COUNT(*) > ?', [1]) — raw (still bound). */
    public function having(string $sql, array $params = []): self
    {
        $this->havings[] = '(' . $sql . ')';
        foreach ($params as $p) {
            $this->havingBindings[] = $p;
        }
        return $this;
    }

    /** whereBetween('created', $from, $to). */
    public function whereBetween(string $col, mixed $from, mixed $to): self
    {
        $this->wheres[] = self::qi($col) . ' BETWEEN ? AND ?';
        $this->bindings[] = $from;
        $this->bindings[] = $to;
        return $this;
    }

    /** where('status','open')  OR  where('total','>',100). */
    public function where(string $col, mixed $opOrVal, mixed $val = self::NONE): self
    {
        if ($val === self::NONE) {
            $op = '=';
            $val = $opOrVal;
        } else {
            $op = strtolower(trim((string) $opOrVal));
            if (!in_array($op, self::OPERATORS, true)) {
                $op = '=';
            }
        }
        $this->wheres[] = self::qi($col) . ' ' . strtoupper($op) . ' ?';
        $this->bindings[] = $val;
        return $this;
    }

    /** whereIn('id', [1,2,3]). */
    public function whereIn(string $col, array $vals): self
    {
        if ($vals === []) {
            $this->wheres[] = '1 = 0';   // an empty IN matches nothing
            return $this;
        }
        $this->wheres[] = self::qi($col) . ' IN (' . implode(', ', array_fill(0, count($vals), '?')) . ')';
        foreach ($vals as $v) {
            $this->bindings[] = $v;
        }
        return $this;
    }

    /** whereNull('deleted_at') / whereNotNull(...). */
    public function whereNull(string $col): self
    {
        $this->wheres[] = self::qi($col) . ' IS NULL';
        return $this;
    }

    public function whereNotNull(string $col): self
    {
        $this->wheres[] = self::qi($col) . ' IS NOT NULL';
        return $this;
    }

    /** Escape hatch: whereRaw('YEAR(created) = ?', [2026]). */
    public function whereRaw(string $sql, array $params = []): self
    {
        $this->wheres[] = '(' . $sql . ')';
        foreach ($params as $p) {
            $this->bindings[] = $p;
        }
        return $this;
    }

    public function orderBy(string $col, string $dir = 'asc'): self
    {
        $dir = strtolower($dir) === 'desc' ? 'DESC' : 'ASC';
        $this->orders[] = self::qi($col) . ' ' . $dir;
        return $this;
    }

    public function limit(int $n): self
    {
        $this->limit = max(0, $n);
        return $this;
    }

    public function offset(int $n): self
    {
        $this->offset = max(0, $n);
        return $this;
    }

    // ── terminal ──────────────────────────────────────────────────────

    /**
     * Build [sql, bindings] with row-level security merged. Public so authors
     * can debug (and so it's testable without a database).
     *
     * @return array{0: string, 1: array<int, mixed>}
     */
    public function toSql(): array
    {
        $sql = 'SELECT ' . ($this->distinct ? 'DISTINCT ' : '') . implode(', ', $this->selects)
            . ' FROM ' . self::qi($this->table);
        if ($this->joins !== []) {
            $sql .= ' ' . implode(' ', $this->joins);
        }
        $where = RowSecurity::mergeWhere($this->buildWhere(), $this->table);
        if ($where !== '') {
            $sql .= ' ' . $where;
        }
        if ($this->groups !== []) {
            $sql .= ' GROUP BY ' . implode(', ', $this->groups);
        }
        if ($this->havings !== []) {
            $sql .= ' HAVING ' . implode(' AND ', $this->havings);
        }
        if ($this->orders !== []) {
            $sql .= ' ORDER BY ' . implode(', ', $this->orders);
        }
        if ($this->limit !== null) {
            $sql .= ' LIMIT ' . $this->limit;
        }
        if ($this->offset !== null) {
            $sql .= ' OFFSET ' . $this->offset;
        }
        return [$sql, array_merge($this->bindings, $this->havingBindings)];
    }

    /** @return array<int, array<string, mixed>> */
    public function get(): array
    {
        [$sql, $params] = $this->toSql();
        $rows = DB::FetchAll($sql, $params);
        return is_array($rows) ? $rows : [];
    }

    /** @return array<string, mixed>|null */
    public function first(): ?array
    {
        $rows = (clone $this)->limit(1)->get();
        return $rows[0] ?? null;
    }

    /** One scalar — the given column of the first matching row. */
    public function value(string $col): mixed
    {
        $row = (clone $this)->select($col)->first();
        if ($row === null) {
            return null;
        }
        // return the single selected column regardless of its key casing
        return array_values($row)[0] ?? null;
    }

    public function count(): int
    {
        return (int) $this->aggregateRaw('COUNT(*)');
    }

    public function exists(): bool
    {
        return $this->count() > 0;
    }

    /** SUM / AVG / MIN / MAX of a column, over the current filter (+ joins). */
    public function sum(string $col): float
    {
        return (float) $this->aggregateRaw('SUM(' . self::qi($col) . ')');
    }

    public function avg(string $col): float
    {
        return (float) $this->aggregateRaw('AVG(' . self::qi($col) . ')');
    }

    public function min(string $col): mixed
    {
        return $this->aggregateRaw('MIN(' . self::qi($col) . ')');
    }

    public function max(string $col): mixed
    {
        return $this->aggregateRaw('MAX(' . self::qi($col) . ')');
    }

    /** Run a single-value aggregate over FROM + joins + where (ignores group/order/limit). */
    private function aggregateRaw(string $expr): mixed
    {
        $sql = 'SELECT ' . $expr . ' FROM ' . self::qi($this->table);
        if ($this->joins !== []) {
            $sql .= ' ' . implode(' ', $this->joins);
        }
        $where = RowSecurity::mergeWhere($this->buildWhere(), $this->table);
        if ($where !== '') {
            $sql .= ' ' . $where;
        }
        return DB::FetchOne($sql, $this->bindings);
    }

    /** @return array<int, mixed> */
    public function pluck(string $col): array
    {
        return array_map(static fn(array $r) => $r[$col] ?? null, (clone $this)->select($col)->get());
    }

    // ── write terminals (permission-gated, filter-required) ───────────

    /**
     * UPDATE the matching rows with $data. Needs 'edit' rights; the current
     * filter (where + row-level security) scopes exactly which rows change.
     * Returns the number of affected rows.
     *
     * @param array<string, mixed> $data
     * @throws \RuntimeException if not permitted, or if there is no filter
     */
    public function update(array $data): int
    {
        if ($data === []) {
            return 0;
        }
        $this->guardWrite('edit');
        $where = $this->guardedWhere('update');

        $set = [];
        $params = [];
        foreach ($data as $col => $val) {
            $set[] = self::qi($col) . ' = ?';
            $params[] = $val;
        }
        foreach ($this->bindings as $b) {   // WHERE bindings come after the SET values
            $params[] = $b;
        }
        $sql = 'UPDATE ' . self::qi($this->table) . ' SET ' . implode(', ', $set) . ' ' . $where;
        $res = DB::Query($sql, $params);
        return $res === false ? 0 : $res->rowCount();
    }

    /**
     * DELETE the matching rows. Needs 'delete' rights; the current filter
     * (where + row-level security) scopes exactly which rows go. Returns the
     * number of deleted rows.
     *
     * @throws \RuntimeException if not permitted, or if there is no filter
     */
    public function delete(): int
    {
        $this->guardWrite('delete');
        $where = $this->guardedWhere('delete');
        $sql = 'DELETE FROM ' . self::qi($this->table) . ' ' . $where;
        $res = DB::Query($sql, $this->bindings);
        return $res === false ? 0 : $res->rowCount();
    }

    /**
     * Atomically add to a numeric column on the matching rows (e.g. a view/like
     * counter). Optionally set other columns at the same time. Needs 'edit'
     * rights and a filter. Returns affected rows.
     *
     *   pp()->table('posts')->where('id', $id)->increment('views');
     *   pp()->table('wallets')->where('user_id', $uid)->increment('balance', 50, ['updated' => now()]);
     *
     * @param array<string, mixed> $extra  other columns to set in the same UPDATE
     */
    public function increment(string $col, int|float $amount = 1, array $extra = []): int
    {
        return $this->stepColumn($col, '+', $amount, $extra);
    }

    /** Atomically subtract from a numeric column. See increment(). */
    public function decrement(string $col, int|float $amount = 1, array $extra = []): int
    {
        return $this->stepColumn($col, '-', $amount, $extra);
    }

    /** @param array<string, mixed> $extra */
    private function stepColumn(string $col, string $sign, int|float $amount, array $extra): int
    {
        $this->guardWrite('edit');
        $where = $this->guardedWhere($sign === '+' ? 'increment' : 'decrement');

        $qc = self::qi($col);
        $set = [$qc . ' = ' . $qc . ' ' . $sign . ' ?'];
        $params = [$amount];
        foreach ($extra as $c => $v) {
            $set[] = self::qi($c) . ' = ?';
            $params[] = $v;
        }
        foreach ($this->bindings as $b) {   // WHERE bindings come last
            $params[] = $b;
        }
        $sql = 'UPDATE ' . self::qi($this->table) . ' SET ' . implode(', ', $set) . ' ' . $where;
        $res = DB::Query($sql, $params);
        return $res === false ? 0 : $res->rowCount();
    }

    /** Throw unless the current user may perform $action on this table. */
    private function guardWrite(string $action): void
    {
        if (class_exists(Permissions::class) && !Permissions::can($action, $this->table)) {
            throw new \RuntimeException("You are not allowed to {$action} {$this->table}.");
        }
    }

    /**
     * The merged WHERE (filter + row security) — but refuse an unfiltered write
     * so a stray ->update()/->delete() can never touch the whole table.
     */
    private function guardedWhere(string $verb): string
    {
        $where = trim(RowSecurity::mergeWhere($this->buildWhere(), $this->table));
        if ($where === '') {
            throw new \RuntimeException(
                "Refusing to {$verb} every row of {$this->table} — add a ->where(...) filter first."
            );
        }
        return $where;
    }

    /**
     * Page the results.
     *
     * @return array{data: array<int, array<string,mixed>>, total: int, page: int, perPage: int, pages: int}
     */
    public function paginate(int $perPage, int $page = 1): array
    {
        $perPage = max(1, $perPage);
        $page = max(1, $page);
        $total = $this->count();
        $data = (clone $this)->limit($perPage)->offset(($page - 1) * $perPage)->get();
        return [
            'data'    => $data,
            'total'   => $total,
            'page'    => $page,
            'perPage' => $perPage,
            'pages'   => (int) ceil($total / $perPage),
        ];
    }

    // ── internals ─────────────────────────────────────────────────────

    private function buildWhere(): string
    {
        return $this->wheres === [] ? '' : 'WHERE ' . implode(' AND ', $this->wheres);
    }

    /**
     * Validate + quote an identifier ("col" or "table.col"). Rejects anything
     * that isn't a plain identifier so it can never be an injection vector.
     */
    private static function qi(string $id): string
    {
        $id = trim($id);
        if ($id === '*') {
            return '*';
        }
        if (!preg_match('/^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?$/', $id)) {
            throw new \InvalidArgumentException("Invalid identifier: {$id}");
        }
        return implode('.', array_map(
            static fn(string $part): string => DB::QuoteIdentifier($part),
            explode('.', $id)
        ));
    }
}
