<?php
namespace Bookaro\Utils;

defined( 'ABSPATH' ) || exit;

use wpdb;

/**
 * Generic database helpers shared across repositories.
 */
class DBUtils
{
    /**
     * Add a column to a table if it does not already exist.
     */
    public static function add_column_if_missing(wpdb $wpdb, string $table, string $column, string $definition): void
    {
        if ( ! self::table_exists( $wpdb, $table ) ) {
            return;
        }

        if (!self::column_exists($wpdb, $table, $column)) {
            $table_safe = esc_sql( $table );
            $definition_safe = esc_sql( $definition );
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE {$table_safe} ADD {$definition_safe}");
        }
    }

    /**
     * Add an index to a table if it does not already exist.
     */
    public static function add_index_if_missing(wpdb $wpdb, string $table, string $index_name, string $definition): void
    {
        if ( ! self::table_exists( $wpdb, $table ) ) {
            return;
        }

        if ( self::index_exists( $wpdb, $table, $index_name ) ) {
            return;
        }

        $table_safe = esc_sql( $table );
        $index_safe = esc_sql( $index_name );
        $definition_safe = esc_sql( $definition );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query("ALTER TABLE {$table_safe} ADD INDEX {$index_safe} ({$definition_safe})");
    }

    /**
     * Determine if a table exists.
     */
    private static function table_exists( wpdb $wpdb, string $table ): bool
    {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (bool) $wpdb->get_var(
            $wpdb->prepare(
                'SHOW TABLES LIKE %s',
                $table
            )
        );
    }

    /**
     * Determine if a column exists on a table.
     */
    public static function column_exists(wpdb $wpdb, string $table, string $column): bool
    {
        $table_safe = esc_sql( $table );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (bool) $wpdb->get_var(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SHOW COLUMNS FROM `{$table_safe}` LIKE %s",
                $column
            )
        );
    }

    /**
     * Determine if an index exists on a table.
     */
    private static function index_exists( wpdb $wpdb, string $table, string $index_name ): bool
    {
        $table_safe = esc_sql( $table );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return (bool) $wpdb->get_var(
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SHOW INDEX FROM `{$table_safe}` WHERE Key_name = %s",
                $index_name
            )
        );
    }
}
