<?php
/**
 * Migration: add coupon purpose columns for widget vs bundle usage.
 *
 * @package BoostBot
 * @since   1.4.1
 */

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Migration owns custom-table DDL. Table names pass through sanitize_identifier(); DDL cannot use prepare() placeholders for identifiers; caching is irrelevant for one-shot migrations.
defined( 'ABSPATH' ) || exit;

class BoostBot_Migration_Add_Coupon_Purpose_Columns {

    /**
     * Run the migration.
     *
     * @return void
     */
    public static function run() {
        $instance = new self();
        $instance->up();
    }

    /**
     * Add missing purpose columns and indexes.
     *
     * @return void
     */
    public function up() {
        global $wpdb;

        $table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_woocommerce_coupons' );

        if ( ! $this->table_exists( $table ) ) {
            return;
        }

        if ( ! $this->column_exists( $table, 'for_widget' ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN for_widget tinyint(1) NOT NULL DEFAULT 1 AFTER is_active" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        if ( ! $this->column_exists( $table, 'for_bundle' ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
            $wpdb->query( "ALTER TABLE {$table} ADD COLUMN for_bundle tinyint(1) NOT NULL DEFAULT 1 AFTER for_widget" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        if ( ! $this->index_exists( $table, 'for_widget' ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
            $wpdb->query( "ALTER TABLE {$table} ADD KEY for_widget (for_widget)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }

        if ( ! $this->index_exists( $table, 'for_bundle' ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
            $wpdb->query( "ALTER TABLE {$table} ADD KEY for_bundle (for_bundle)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        }
    }

    /**
     * Check whether the table exists.
     *
     * @param string $table Table name.
     * @return bool
     */
    private function table_exists( string $table ): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );

        return $table === $result;
    }

    /**
     * Check whether a column exists.
     *
     * @param string $table  Table name.
     * @param string $column Column name.
     * @return bool
     */
    private function column_exists( string $table, string $column ): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
        $results = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM {$table} LIKE %s", $column ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        return ! empty( $results );
    }

    /**
     * Check whether an index exists.
     *
     * @param string $table Table name.
     * @param string $index Index name.
     * @return bool
     */
    private function index_exists( string $table, string $index ): bool {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
        $results = $wpdb->get_results( $wpdb->prepare( "SHOW INDEX FROM {$table} WHERE Key_name = %s", $index ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        return ! empty( $results );
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}
