<?php

namespace Booktics\Abstracts;

use Booktics\Contracts\Database_Interface;
use RuntimeException;
use wpdb;

/**
 * Database Manager for booktics database
 *
 * @package Booktics/Abstracts
 */
class Booktics_Database implements Database_Interface {

    /**
     * WordPress database global variable
     *
     * @var wpdb
     */
    private $db;

    /**
     * Set global wpdb instance
     */
    public function __construct() {
        global $wpdb;
        if ( ! $wpdb instanceof wpdb ) {
            throw new RuntimeException( 'WordPress database object not available' );
        }
        $this->db = $wpdb;
    }

    /**
     * Validate a SQL identifier (column name) against a strict allow-list.
     *
     * Only plain identifiers ([A-Za-z0-9_]) are permitted. Since column names
     * cannot be parameterized by wpdb::prepare(), any value used as a column
     * name must be validated here to prevent SQL injection when the array keys
     * originate from user-controlled request bodies.
     *
     * @param string $identifier
     *
     * @return string The validated identifier, wrapped in backticks.
     *
     * @throws RuntimeException When the identifier is not a plain column name.
     */
    private function sanitize_identifier( string $identifier ): string {
        if ( ! preg_match( '/^[a-zA-Z0-9_]+$/', $identifier ) ) {
            throw new RuntimeException(
                sprintf( esc_html__( 'Invalid database column identifier: %s', 'booktics' ), esc_html( $identifier ) )
            );
        }

        return "`{$identifier}`";
    }

    /**
     * Begin database transaction
     *
     * @return void
     */
    public function begin_transaction(): void {
        $this->db->query( 'START TRANSACTION' );
    }

    /**
     * Commit transactions to database
     *
     * @return void
     */
    public function commit(): void {
        $this->db->query( 'COMMIT' );
    }

    /**
     * Rollback uncommited transactions
     *
     * @return void
     */
    public function roll_back(): void {
        $this->db->query( 'ROLLBACK' );
    }

    /**
     * Delete records of specific table
     *
     * @param string $table
     * @param array $where
     *
     * @return void
     */
    public function delete( string $table, array $where ): void {
        $table      = booktics_get_table_name( $table );
        $conditions = array();
        $values     = array();

        foreach ( $where as $key => $value ) {
            $conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
            $values[]     = $value;
        }

        $condition_string = implode( ' AND ', $conditions );

        $sql = "DELETE FROM $table WHERE $condition_string";

        $result = $this->db->query(
            $this->db->prepare( $sql, ...$values )
        );

        if ( $result === false ) {
            throw new RuntimeException(
            // translators: %s is the database error message.
                sprintf( esc_html__( 'Failed to delete records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
            );
        }
    }

    /**
     * Batch insert records
     *
     * @param string $table
     * @param array $records
     *
     * @return void
     */
    public function insert_batch( string $table, array $records ): void {
        if ( empty( $records ) ) {
            return;
        }

        $table              = booktics_get_table_name( $table );
        $columns            = array_keys( $records[0] );
        $safe_columns       = array_map( array( $this, 'sanitize_identifier' ), $columns );
        $placeholders       = array_fill( 0, count( $columns ), '%s' );
        $placeholder_string = '(' . implode( ',', $placeholders ) . ')';

        $values              = array();
        $placeholders_groups = array();

        foreach ( $records as $record ) {
            $placeholders_groups[] = $placeholder_string;
            foreach ( $columns as $column ) {
                $values[] = $record[ $column ] ?? '';
            }
        }

        $column_string       = implode( ',', $safe_columns );
        $placeholders_string = implode( ',', $placeholders_groups );

        $sql = "INSERT INTO $table ($column_string) VALUES $placeholders_string";

        $result = $this->db->query(
            $this->db->prepare( $sql, ...$values )
        );

        if ( $result === false ) {
            // translators: %s is the database error message.
            sprintf( esc_html__( 'Failed to insert records: %s', 'booktics' ), esc_html( $this->db->last_error ) );
        }
    }

    /**
     * Select records from a table
     *
     * @param string $table
     * @param array $where Optional WHERE conditions
     * @param array $columns Columns to select
     *
     * @return array
     */
    public function select( string $table, array $where = array(), array $columns = array( '*' ) ) {
        $table     = booktics_get_table_name( $table );
        $columns_s = implode(
            ', ',
            array_map(
                function ( $column ) {
                    return '*' === $column ? '*' : $this->sanitize_identifier( $column );
                },
                $columns
            )
        );
        $sql       = "SELECT $columns_s FROM $table";

        $values = array();
        if ( ! empty( $where ) ) {
            $conditions = array();

            foreach ( $where as $key => $value ) {
                $conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
                $values[]     = $value;
            }

            $condition_string = implode( ' AND ', $conditions );
            $sql              .= " WHERE $condition_string";
        }

        $prepared = $this->db->prepare( $sql, ...$values );
        $results  = $this->db->get_results( $prepared );

        if ( $results === null ) {
            throw new RuntimeException(
            // translators: %s is the database error message.
                sprintf( esc_html__( 'Failed to select records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
            );
        }

        return $results;
    }

    /**
     * Initialize query builder instance
     *
     * @param string $table
     *
     * @return DB_Query_Builder
     */
    public function table( string $table ): DB_Query_Builder {
        return ( new DB_Query_Builder( $this->db ) )->table( $table );
    }

    /**
     * Update records in a table
     *
     * @param string $table
     * @param array $data
     * @param array $where
     *
     * @return void
     */
    public function update( string $table, array $data, array $where ) {
        $table            = booktics_get_table_name( $table );
        $set_values       = array();
        $where_conditions = array();
        $values           = array();

        foreach ( $data as $key => $value ) {
            $set_values[] = $this->sanitize_identifier( $key ) . ' = %s';
            $values[]     = $value;
        }

        foreach ( $where as $key => $value ) {
            $where_conditions[] = $this->sanitize_identifier( $key ) . ' = %s';
            $values[]           = $value;
        }

        $set_string   = implode( ', ', $set_values );
        $where_string = implode( ' AND ', $where_conditions );

        $sql = "UPDATE $table SET $set_string WHERE $where_string";

        $result = $this->db->query(
            $this->db->prepare( $sql, ...$values )
        );

        if ( $result === false ) {
            throw new RuntimeException(
            // translators: %s: Records Name
                sprintf( esc_html__( 'Failed to update records: %s', 'booktics' ), esc_html( $this->db->last_error ) )
            );
        }
    }
}