<?php
/**
 * TitanCart ORM / Query Layer.
 *
 * Implements a placeholder-based query system on top of WordPress's $wpdb.
 * All DB interaction in TitanCart flows through this class.
 *
 * Placeholder reference:
 *   ?s  — string   (escaped + quoted)
 *   ?i  — integer  (intval cast, unquoted)
 *   ?p  — raw      (no escaping — trusted SQL fragments only)
 *   ?n  — NULL     (literal NULL)
 *   ?a  — array    → IN ('v1','v2','v3')
 *   ?u  — update   → `key`='val', `key2`=NULL
 *   ?e  — escape   (escaped, no surrounding quotes)
 *
 * @package TitanCart\DB
 */

namespace TitanCart\DB;

defined( 'ABSPATH' ) || exit;

class Database {

    /** @var \wpdb WordPress database object */
    private \wpdb $wpdb;

    /** @var string Table prefix for TitanCart tables */
    public string $prefix;

    /** @var array<string> Query log (enabled in WP_DEBUG mode) */
    private array $query_log = [];

    /**
     * @var bool Whether this request's connection session time zone has been
     * pinned to UTC yet (KI-24 Phase 3). Static so it runs once per request
     * regardless of how many times the Database service is resolved.
     */
    private static bool $session_tz_pinned = false;

    /**
     * @var array<string, array{created_at: bool, updated_at: bool}>
     * Per-table timestamp-column presence cache (KI-24 Phase 3, Option B).
     * Keyed by fully-prefixed table name; one SHOW COLUMNS per table per request.
     */
    private array $ts_columns_cache = [];

    public function __construct() {
        global $wpdb;
        $this->wpdb   = $wpdb;
        $this->prefix = $wpdb->prefix . TITANCART_TABLE_PREFIX;

        $this->pin_session_utc();
    }

    /**
     * Pin the MySQL connection's SESSION time zone to UTC, once per request.
     *
     * This is the KI-24 datetime contract realised at the connection layer:
     * with the session in UTC, every server-side "now" — `CURRENT_TIMESTAMP`
     * column defaults, `ON UPDATE CURRENT_TIMESTAMP`, and bare `NOW()` — is
     * evaluated in UTC, matching the PHP-side helpers (`titancart_now_utc()`, the
     * Option-B ORM auto-stamp). It removes the old per-writer split where
     * MySQL-evaluated stamps landed in the DB-server's local zone (Eastern on
     * the live installs) while PHP-written stamps were UTC.
     *
     * Set from inside the engine (not from server/global config), so the
     * behaviour travels with the code to any install — the portable-engine
     * stance. Safe for existing data: all TitanCart timestamp columns are
     * DATETIME (never TIMESTAMP), so stored values are not reinterpreted when
     * the session zone changes; only new server-side evaluations are affected.
     *
     * '+00:00' is used rather than the named zone 'UTC' because the fixed
     * offset does not require MySQL's named-timezone tables to be populated.
     */
    private function pin_session_utc(): void {
        if ( self::$session_tz_pinned ) {
            return;
        }
        self::$session_tz_pinned = true;
        $this->wpdb->query( "SET time_zone = '+00:00'" );
    }

    // ── Public table name helper ──────────────────────────────────────────────

    /**
     * Return a fully-prefixed table name.
     *
     * Example: $db->table('products') → 'wp_tc_products'
     *
     * @param  string $name Short table name (without tc_ prefix).
     * @return string
     */
    public function table( string $name ): string {
        return $this->prefix . ltrim( $name, '_' );
    }

    // ── Query execution ───────────────────────────────────────────────────────

    /**
     * Execute a query and return the raw wpdb result.
     *
     * @param  string $query Raw SQL with ?-placeholders.
     * @param  mixed  ...$args Values for placeholder substitution.
     * @return bool|int wpdb->query() result.
     */
    public function query( string $query, mixed ...$args ): bool|int {
        $sql = $this->prepare( $query, $args );
        $this->log( $sql );
        return $this->wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is bound/escaped by Database::prepare() above (the TitanCart ORM's ?-placeholder binder; this class is the sole SQL execution seam — see docs/WPORG-REVIEWER-NOTES.md).
    }

    /**
     * Begin a MySQL transaction.
     *
     * Uses `START TRANSACTION` (not `BEGIN`) for full MySQL compatibility.
     * Tables involved must be InnoDB — MyISAM tables silently ignore the wrap.
     *
     * @return bool True on success, false if wpdb rejected the statement.
     */
    public function begin(): bool {
        $this->log( 'START TRANSACTION' );
        return (bool) $this->wpdb->query( 'START TRANSACTION' );
    }

    /**
     * Commit the current transaction.
     *
     * @return bool True on success.
     */
    public function commit(): bool {
        $this->log( 'COMMIT' );
        return (bool) $this->wpdb->query( 'COMMIT' );
    }

    /**
     * Roll back the current transaction.
     *
     * @return bool True on success.
     */
    public function rollback(): bool {
        $this->log( 'ROLLBACK' );
        return (bool) $this->wpdb->query( 'ROLLBACK' );
    }

    /**
     * Return multiple rows as an indexed array of associative arrays.
     *
     * @param  string $query
     * @param  mixed  ...$args
     * @return array<int, array<string, mixed>>
     */
    public function getArray( string $query, mixed ...$args ): array {
        $sql    = $this->prepare( $query, $args );
        $this->log( $sql );
        $result = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql bound/escaped by Database::prepare() above (ORM execution seam; see docs/WPORG-REVIEWER-NOTES.md).
        return is_array( $result ) ? $result : [];
    }

    /**
     * Return rows as a [key => value] hash map.
     * The first column becomes the key, the second the value.
     *
     * @param  string $query
     * @param  mixed  ...$args
     * @return array<string, mixed>
     */
    public function getHash( string $query, mixed ...$args ): array {
        $rows = $this->getArray( $query, ...$args );
        $out  = [];
        foreach ( $rows as $row ) {
            $values = array_values( $row );
            if ( count( $values ) >= 2 ) {
                $out[ $values[0] ] = $values[1];
            }
        }
        return $out;
    }

    /**
     * Return a single row as an associative array, or null.
     *
     * @param  string $query
     * @param  mixed  ...$args
     * @return array<string, mixed>|null
     */
    public function getRow( string $query, mixed ...$args ): ?array {
        $sql    = $this->prepare( $query, $args );
        $this->log( $sql );
        $result = $this->wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql bound/escaped by Database::prepare() above (ORM execution seam; see docs/WPORG-REVIEWER-NOTES.md).
        return is_array( $result ) ? $result : null;
    }

    /**
     * Return a single scalar value, or null.
     *
     * @param  string $query
     * @param  mixed  ...$args
     * @return mixed
     */
    public function getField( string $query, mixed ...$args ): mixed {
        $sql = $this->prepare( $query, $args );
        $this->log( $sql );
        return $this->wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql bound/escaped by Database::prepare() above (ORM execution seam; see docs/WPORG-REVIEWER-NOTES.md).
    }

    /**
     * Return a flat array of values from the first column.
     *
     * @param  string $query
     * @param  mixed  ...$args
     * @return array<int, mixed>
     */
    public function getColumn( string $query, mixed ...$args ): array {
        $sql    = $this->prepare( $query, $args );
        $this->log( $sql );
        $result = $this->wpdb->get_col( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql bound/escaped by Database::prepare() above (ORM execution seam; see docs/WPORG-REVIEWER-NOTES.md).
        return is_array( $result ) ? $result : [];
    }

    /**
     * Return rows grouped by the first column.
     * Result: [ key => [ row, row, ... ] ]
     *
     * @param  string $query
     * @param  mixed  ...$args
     * @return array<string, array<int, array<string, mixed>>>
     */
    public function getMultiHash( string $query, mixed ...$args ): array {
        $rows = $this->getArray( $query, ...$args );
        $out  = [];
        foreach ( $rows as $row ) {
            $key = reset( $row );
            $out[ $key ][] = $row;
        }
        return $out;
    }

    // ── Convenience write methods ─────────────────────────────────────────────

    /**
     * Insert a row into a table.
     *
     * @param  string               $table Short table name (without prefix).
     * @param  array<string, mixed> $data  Column => value pairs.
     * @return int Inserted row ID, or 0 on failure.
     */
    public function insert( string $table, array $data ): int {
        $full_table = $this->table( $table );
        $data       = $this->apply_auto_timestamps( $full_table, $data, true );
        $result     = $this->wpdb->insert( $full_table, $data );
        return $result !== false ? (int) $this->wpdb->insert_id : 0;
    }

    /**
     * Update rows in a table.
     *
     * @param  string               $table  Short table name.
     * @param  array<string, mixed> $data   Columns to update.
     * @param  array<string, mixed> $where  WHERE conditions (AND-joined).
     * @return int|false Rows affected, or false on failure.
     */
    public function update( string $table, array $data, array $where ): int|false {
        $full_table = $this->table( $table );
        $data       = $this->apply_auto_timestamps( $full_table, $data, false );
        return $this->wpdb->update( $full_table, $data, $where );
    }

    /**
     * Delete rows from a table.
     *
     * @param  string               $table Short table name.
     * @param  array<string, mixed> $where WHERE conditions (AND-joined).
     * @return int|false Rows deleted, or false on failure.
     */
    public function delete( string $table, array $where ): int|false {
        return $this->wpdb->delete( $this->table( $table ), $where );
    }

    /**
     * Return the ID of the last inserted row.
     *
     * @return int
     */
    public function lastInsertId(): int {
        return (int) $this->wpdb->insert_id;
    }

    /**
     * Return the last error from wpdb.
     *
     * @return string
     */
    public function lastError(): string {
        return $this->wpdb->last_error;
    }

    // ── KI-24 Phase 3 (Option B): ORM auto-stamp ──────────────────────────────

    /**
     * Auto-fill created_at / updated_at with the current UTC time when the
     * target table HAS the column AND the caller did NOT supply it.
     *
     * This is the single chokepoint that realises the KI-24 datetime contract
     * (store UTC; display in the store tz) for every write that flows through
     * the ORM — replacing reliance on the MySQL column DEFAULT CURRENT_TIMESTAMP
     * / ON UPDATE CURRENT_TIMESTAMP, which evaluate in the DB session timezone
     * (Eastern on both live installs) and so produced ~4h-skewed stamps.
     *
     * Explicit caller values always win (array_key_exists, so an explicit NULL
     * is also respected) — import/migration flows that preserve original
     * timestamps pass them explicitly and are left untouched. On UPDATE, only
     * updated_at is auto-filled; created_at is never altered.
     *
     * Safe to run BEFORE migration 56 drops the column defaults: the explicit
     * UTC value simply overrides the default. After the defaults are dropped,
     * this becomes the sole source of the stamp.
     *
     * @param  string               $full_table Fully-prefixed table name.
     * @param  array<string, mixed> $data       Column => value pairs.
     * @param  bool                 $is_insert  True for INSERT (stamps both),
     *                                          false for UPDATE (updated_at only).
     * @return array<string, mixed> The data array, possibly with timestamps added.
     */
    private function apply_auto_timestamps( string $full_table, array $data, bool $is_insert ): array {
        $cols = $this->timestamp_columns( $full_table );

        if ( $is_insert && $cols['created_at'] && ! array_key_exists( 'created_at', $data ) ) {
            $data['created_at'] = $this->now_utc();
        }
        if ( $cols['updated_at'] && ! array_key_exists( 'updated_at', $data ) ) {
            $data['updated_at'] = $this->now_utc();
        }

        return $data;
    }

    /**
     * Determine (and cache per-table) whether a table has created_at / updated_at
     * columns. One `SHOW COLUMNS` per table per request.
     *
     * @param  string $full_table Fully-prefixed table name.
     * @return array{created_at: bool, updated_at: bool}
     */
    private function timestamp_columns( string $full_table ): array {
        if ( isset( $this->ts_columns_cache[ $full_table ] ) ) {
            return $this->ts_columns_cache[ $full_table ];
        }

        // The identifier is built from the trusted tc_ table prefix (never user
        // input), so backtick-quoted interpolation is safe. A non-existent table
        // returns an empty set → no stamping → the subsequent write fails as it
        // would have anyway.
        $names = $this->wpdb->get_col( "SHOW COLUMNS FROM `{$full_table}`" );
        $names = is_array( $names ) ? $names : [];

        $result = [
            'created_at' => in_array( 'created_at', $names, true ),
            'updated_at' => in_array( 'updated_at', $names, true ),
        ];

        return $this->ts_columns_cache[ $full_table ] = $result;
    }

    /**
     * Current time as a UTC 'Y-m-d H:i:s' string. Mirrors titancart_now_utc(); falls
     * back to gmdate() (also UTC) if the global helper is not yet loaded, so the
     * UTC contract holds even during early bootstrap or in isolation.
     *
     * @return string
     */
    private function now_utc(): string {
        return \function_exists( 'titancart_now_utc' )
            ? \titancart_now_utc()
            : \gmdate( 'Y-m-d H:i:s' );
    }

    // ── Placeholder processor ─────────────────────────────────────────────────

    /**
     * Replace ?-placeholders in a query string with escaped values.
     *
     * @param  string       $query Raw SQL with placeholders.
     * @param  array<mixed> $args  Values to substitute.
     * @return string       Final SQL string.
     * @throws \InvalidArgumentException On mismatched placeholder/arg count.
     */
    public function prepare( string $query, array $args ): string {
        if ( empty( $args ) ) {
            return $query;
        }

        $index  = 0;
        $output = '';
        $len    = strlen( $query );

        for ( $i = 0; $i < $len; $i++ ) {
            $char = $query[ $i ];

            if ( $char !== '?' || ( $i + 1 >= $len ) ) {
                $output .= $char;
                continue;
            }

            $type = $query[ $i + 1 ];
            $i++;  // Consume the type character.

            if ( ! array_key_exists( $index, $args ) ) {
                throw new \InvalidArgumentException(
                    esc_html( "TitanCart DB: Not enough arguments for query. Expected arg at index {$index}." )
                );
            }

            $value   = $args[ $index++ ];
            $output .= $this->escape( $type, $value );
        }

        return $output;
    }

    /**
     * Escape a single value according to the placeholder type.
     *
     * @param  string $type  One of: s, i, p, n, a, u, e.
     * @param  mixed  $value The value to escape.
     * @return string        Escaped SQL fragment.
     */
    private function escape( string $type, mixed $value ): string {
        return match ( $type ) {

            // String — escape and quote.
            's' => $value === null
                ? 'NULL'
                : "'" . $this->wpdb->_real_escape( (string) $value ) . "'",

            // Integer — cast, no quotes.
            'i' => (string) (int) $value,

            // Raw — no escaping whatsoever (trusted SQL fragments only!).
            'p' => (string) $value,

            // NULL literal.
            'n' => 'NULL',

            // Array → IN list.
            'a' => $this->escapeArray( (array) $value ),

            // Update SET clause.
            'u' => $this->escapeUpdate( (array) $value ),

            // Escape only (no surrounding quotes) — for LIKE patterns, etc.
            'e' => $this->wpdb->_real_escape( (string) $value ),

            default => throw new \InvalidArgumentException(
                esc_html( "TitanCart DB: Unknown placeholder type '?{$type}'." )
            ),
        };
    }

    /**
     * Build a quoted comma-separated list for IN clauses.
     *
     * @param  array<mixed> $values
     * @return string
     */
    private function escapeArray( array $values ): string {
        if ( empty( $values ) ) {
            return "('')"; // Prevents syntax errors on empty IN.
        }

        $escaped = array_map(
            fn( $v ) => $v === null
                ? 'NULL'
                : "'" . $this->wpdb->_real_escape( (string) $v ) . "'",
            $values
        );

        return implode( ', ', $escaped );
    }

    /**
     * Build a SET clause for UPDATE statements.
     * Keys become backtick-quoted column names; values are escaped strings
     * (or NULL where the value is null).
     *
     * @param  array<string, mixed> $data
     * @return string
     */
    private function escapeUpdate( array $data ): string {
        $parts = [];
        foreach ( $data as $col => $val ) {
            $col = '`' . str_replace( '`', '``', $col ) . '`';
            if ( $val === null ) {
                $parts[] = "{$col}=NULL";
            } else {
                $parts[] = "{$col}='" . $this->wpdb->_real_escape( (string) $val ) . "'";
            }
        }
        return implode( ', ', $parts );
    }

    // ── Extra helpers ─────────────────────────────────────────────────────────

    /**
     * Return the raw WordPress table prefix (NOT the TitanCart tc_ prefix).
     * Use this when joining against native WP tables such as {prefix}users.
     *
     * Example: $db->wpdb_prefix() . 'users'  →  'wp_users'
     *
     * @return string
     */
    public function wpdb_prefix(): string {
        return $this->wpdb->prefix;
    }

    /**
     * Escape a string for use in a LIKE clause (escapes %, _, \).
     * Wrap the result in % wildcards before passing to ?s placeholder.
     *
     * @param  string $value
     * @return string
     */
    public function escape_like( string $value ): string {
        return $this->wpdb->esc_like( $value );
    }

    // ── Debug helpers ─────────────────────────────────────────────────────────

    /**
     * Log a query string when WP_DEBUG is enabled.
     *
     * @param string $sql
     */
    private function log( string $sql ): void {
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
            $this->query_log[] = $sql;
        }
    }

    /**
     * Return all logged queries (debug mode only).
     *
     * @return array<string>
     */
    public function getQueryLog(): array {
        return $this->query_log;
    }
}
