<?php
/**
 * Database wrapper around $wpdb with CRUD helpers.
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB

class ZINV_Database {

    private string $table;

    public function __construct( string $table_name ) {
        global $wpdb;
        $this->table = $wpdb->prefix . $table_name;
    }

    public function get_table_name(): string {
        return $this->table;
    }

    public function insert( array $data ): int {
        global $wpdb;

        $formats = $this->get_formats( $data );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $wpdb->insert( $this->table, $data, $formats );

        return (int) $wpdb->insert_id;
    }

    public function update( array $data, array $where ): bool {
        global $wpdb;

        $data_formats   = $this->get_formats( $data );
        $where_formats  = $this->get_formats( $where );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->update( $this->table, $data, $where, $data_formats, $where_formats );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return false !== $result;
    }

    public function delete( array $where ): bool {
        global $wpdb;

        $formats = $this->get_formats( $where );
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->delete( $this->table, $where, $formats );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return false !== $result;
    }

    public function get_last_error(): string {
        global $wpdb;
        return $wpdb->last_error ?? '';
    }

    /*
     * Query-wrapper note: callers always pass a literal SQL string with %s/%d
     * placeholders plus matching $args. PHPCS cannot see through the variable
     * first arg to prepare(), so the InterpolatedNotPrepared sniff is suppressed
     * on the prepare() and the get_*() lines. The contract is enforced by callers.
     */

    public function get_row( string $query, ...$args ): ?array {
        global $wpdb;

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ( ! empty( $args ) ) {
            $prepared = $wpdb->prepare( $query, ...$args );
        } else {
            $prepared = $query;
        }

        $row = $wpdb->get_row( $prepared, ARRAY_A ) ?: null;
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $row;
    }

    public function get_results( string $query, ...$args ): array {
        global $wpdb;

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ( ! empty( $args ) ) {
            $prepared = $wpdb->prepare( $query, ...$args );
        } else {
            $prepared = $query;
        }

        $results = $wpdb->get_results( $prepared, ARRAY_A ) ?: [];
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $results;
    }

    public function get_var( string $query, ...$args ): ?string {
        global $wpdb;

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ( ! empty( $args ) ) {
            $prepared = $wpdb->prepare( $query, ...$args );
        } else {
            $prepared = $query;
        }

        $result = $wpdb->get_var( $prepared );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return null !== $result ? $result : null;
    }

    /**
     * Paginated SELECT helper.
     *
     * IMPORTANT: $select, $from, $where, $orderby are interpolated directly. Callers MUST:
     *  - whitelist $orderby against a known column set (see ALLOWED_SORT_FIELDS in CRUD classes)
     *  - pass only trusted/internal strings for $select and $from
     *  - place all user input in $where_args, with %s/%d placeholders in $where
     *
     * @param int $max_per_page  Optional hard cap. Defaults to 100; export endpoints can raise.
     */
    public function get_paginated(
        string $select,
        string $from,
        int $page = 1,
        int $per_page = 20,
        string $where = '',
        string $orderby = 'id',
        string $order = 'DESC',
        array $where_args = [],
        int $max_per_page = 100
    ): array {
        global $wpdb;

        $max_per_page = max( 1, $max_per_page );
        $per_page     = min( max( 1, $per_page ), $max_per_page );
        $page         = max( 1, $page );
        $offset       = ( $page - 1 ) * $per_page;

        $allowed_order = [ 'ASC', 'DESC' ];
        $order         = in_array( strtoupper( $order ), $allowed_order, true ) ? strtoupper( $order ) : 'DESC';

        // Defensive: sanitize and validate SELECT and FROM structure to prevent SQL clause injection.
        if ( ! preg_match( '/^[A-Za-z0-9_\.,`\s\*\(\)]+$/', $select ) ) {
            $select = '*';
        }

        if ( ! preg_match( '/^[A-Za-z0-9_`\s\.\=]+$/', $from ) && ! preg_match( '/^[A-Za-z0-9_`\s\.\=\(\)\bJOIN\b\bLEFT\b\bRIGHT\b\bINNER\b\bON\b]+$/i', $from ) ) {
            $from = $this->table;
        }

        // Defensive: reject anything that isn't a simple [alias.]column identifier.
        if ( ! preg_match( '/^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?$/', $orderby ) ) {
            $orderby = 'id';
        }

        $where_clause = ! empty( $where ) ? "WHERE {$where}" : '';

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        // Count total.
        $count_sql = "SELECT COUNT(*) FROM {$from} {$where_clause}";
        if ( ! empty( $where_args ) ) {
            $total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, ...$where_args ) );
        } else {
            $total = (int) $wpdb->get_var( $count_sql );
        }

        $total_pages = $per_page > 0 ? (int) ceil( $total / $per_page ) : 0;

        // Fetch items — LIMIT/OFFSET via %d placeholders, ORDER BY pre-validated above.
        $items_sql = "SELECT {$select} FROM {$from} {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
        $items_args = array_merge( $where_args, [ $per_page, $offset ] );
        $items = $wpdb->get_results( $wpdb->prepare( $items_sql, ...$items_args ), ARRAY_A ) ?: [];
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return [
            'items'       => $items,
            'total'       => $total,
            'total_pages' => $total_pages,
            'page'        => $page,
            'per_page'    => $per_page,
        ];
    }

    private function get_formats( array $data ): array {
        $formats = [];
        foreach ( $data as $value ) {
            if ( is_int( $value ) ) {
                $formats[] = '%d';
            } else {
                $formats[] = '%s';
            }
        }
        return $formats;
    }
}
