<?php
namespace FleekDash\Database\Core;

defined('ABSPATH') || exit;

if (!\function_exists('fleekdash_log_debug')) {
    require_once FLEEKDASH_PLUGIN_DIR . 'includes/Core/debug-helpers.php';
}

/**
 * Base Migration Class
 *
 * All migrations must extend this class and implement up() and down() methods
 */
abstract class BaseMigration
{
    protected \wpdb $wpdb;

    protected string $prefix;

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

    /**
     * Run migration
     */
    abstract public function up(): void;

    /**
     * Rollback migration
     */
    abstract public function down(): void;

    /**
     * Get migration name from file
     */
    public function getName(): string
    {
        $reflection = new \ReflectionClass($this);

        return basename($reflection->getFileName(), '.php');
    }

    /**
     * Validate identifier (table/column/index name) to prevent SQL injection
     *
     * Only allows alphanumeric characters and underscores
     * Rejects SQL injection patterns (semicolons, comments, etc.)
     *
     * @param string $identifier Identifier to validate
     * @return bool True if valid, false otherwise
     */
    protected function validateIdentifier(string $identifier): bool
    {
        // Only allow alphanumeric characters and underscores
        // Must start with letter or underscore, not a number
        return preg_match('/^[a-z_][a-z0-9_]*$/i', $identifier) === 1;
    }

    /**
     * Escape identifier with backticks for safe SQL usage
     *
     * @param string $identifier Identifier to escape
     * @return string Escaped identifier
     * @throws \InvalidArgumentException If identifier is invalid
     */
    protected function escapeIdentifier(string $identifier): string
    {
        if (!$this->validateIdentifier($identifier)) {
            throw new \InvalidArgumentException(
                "Invalid identifier: '{$identifier}'. Only alphanumeric characters and underscores are allowed."
            );
        }

        // Use backticks to safely quote identifier
        return "`{$identifier}`";
    }

    /**
     * Create table helper
     */
    protected function createTable(string $name, callable $callback): void
    {
        $builder = new TableBuilder($name);
        $callback($builder);
        $sql = $builder->toSql();

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }

    /**
     * Drop table helper
     */
    protected function dropTable(string $name): void
    {
        if (!$this->validateIdentifier($name)) {
            throw new \InvalidArgumentException("Invalid table name: '{$name}'");
        }

        $table = $this->prefix . $name;
        $table_escaped = $this->escapeIdentifier($table);
        $this->wpdb->query("DROP TABLE IF EXISTS {$table_escaped}");
    }

    /**
     * Check if table exists
     */
    protected function tableExists(string $name): bool
    {
        $table = $this->prefix . $name;
        return $this->wpdb->get_var(
            $this->wpdb->prepare("SHOW TABLES LIKE %s", $table)
        ) === $table;
    }

    /**
     * Add column to existing table
     *
     * NOTE: information_schema.columns.table_name stores the FULL table name
     * including WordPress prefix (e.g., wp_fleekdash_site_growth_events)
     *
     * ✅ SECURITY: Validates identifiers and uses backticks to prevent SQL injection
     */
    protected function addColumn(string $table, string $column, string $definition): void
    {
        // Validate identifiers to prevent SQL injection
        if (!$this->validateIdentifier($table)) {
            throw new \InvalidArgumentException("Invalid table name: '{$table}'");
        }
        if (!$this->validateIdentifier($column)) {
            throw new \InvalidArgumentException("Invalid column name: '{$column}'");
        }

        $table_with_prefix = $this->prefix . $table;

        // Check if column exists
        // CRITICAL: Use prefixed table name - information_schema stores FULL table names
        // ✅ SECURITY: $wpdb->prepare() escapes values (table_name, column_name) as strings
        // This is safe because we're comparing against information_schema, not using in ALTER TABLE
        $exists = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE()
             AND table_name = %s
             AND column_name = %s",
            $table_with_prefix,
            $column
        ));

        if (!$exists) {
            // ✅ SECURITY: Use backticks for identifiers in ALTER TABLE
            $table_escaped = $this->escapeIdentifier($table_with_prefix);
            $column_escaped = $this->escapeIdentifier($column);
            $this->wpdb->query(
                "ALTER TABLE {$table_escaped} ADD COLUMN {$column_escaped} {$definition}"
            );
        }
    }

    /**
     * Drop column from table
     *
     * NOTE: information_schema.columns.table_name stores the FULL table name
     * including WordPress prefix (e.g., wp_fleekdash_site_growth_events)
     *
     * ✅ SECURITY: Validates identifiers and uses backticks to prevent SQL injection
     */
    protected function dropColumn(string $table, string $column): void
    {
        // Validate identifiers to prevent SQL injection
        if (!$this->validateIdentifier($table)) {
            throw new \InvalidArgumentException("Invalid table name: '{$table}'");
        }
        if (!$this->validateIdentifier($column)) {
            throw new \InvalidArgumentException("Invalid column name: '{$column}'");
        }

        $table_with_prefix = $this->prefix . $table;

        // Check if column exists
        // CRITICAL: Use prefixed table name - information_schema stores FULL table names
        // ✅ SECURITY: $wpdb->prepare() escapes values (table_name, column_name) as strings
        // This is safe because we're comparing against information_schema, not using in ALTER TABLE
        $exists = $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE()
             AND table_name = %s
             AND column_name = %s",
            $table_with_prefix,
            $column
        ));

        if ($exists) {
            // ✅ SECURITY: Use backticks for identifiers in ALTER TABLE
            $table_escaped = $this->escapeIdentifier($table_with_prefix);
            $column_escaped = $this->escapeIdentifier($column);
            $this->wpdb->query(
                "ALTER TABLE {$table_escaped} DROP COLUMN {$column_escaped}"
            );
        }
    }

    /**
     * Add index to table
     *
     * ✅ SECURITY: Validates identifiers and uses backticks to prevent SQL injection
     */
    protected function addIndex(string $table, string $column, ?string $name = null): void
    {
        // Validate identifiers to prevent SQL injection
        if (!$this->validateIdentifier($table)) {
            throw new \InvalidArgumentException("Invalid table name: '{$table}'");
        }
        if (!$this->validateIdentifier($column)) {
            throw new \InvalidArgumentException("Invalid column name: '{$column}'");
        }

        $table = $this->prefix . $table;
        $name = $name ?: "idx_{$column}";

        // Validate generated index name
        if (!$this->validateIdentifier($name)) {
            throw new \InvalidArgumentException("Invalid index name: '{$name}'");
        }

        // ✅ SECURITY: Use backticks for identifiers in ALTER TABLE
        $table_escaped = $this->escapeIdentifier($table);
        $name_escaped = $this->escapeIdentifier($name);
        $column_escaped = $this->escapeIdentifier($column);
        $this->wpdb->query(
            "ALTER TABLE {$table_escaped} ADD INDEX {$name_escaped} ({$column_escaped})"
        );
    }

    /**
     * Drop index from table
     *
     * ✅ SECURITY: Validates identifiers and uses backticks to prevent SQL injection
     */
    protected function dropIndex(string $table, string $name): void
    {
        // Validate identifiers to prevent SQL injection
        if (!$this->validateIdentifier($table)) {
            throw new \InvalidArgumentException("Invalid table name: '{$table}'");
        }
        if (!$this->validateIdentifier($name)) {
            throw new \InvalidArgumentException("Invalid index name: '{$name}'");
        }

        $table = $this->prefix . $table;

        // ✅ SECURITY: Use backticks for identifiers in ALTER TABLE
        $table_escaped = $this->escapeIdentifier($table);
        $name_escaped = $this->escapeIdentifier($name);
        $this->wpdb->query(
            "ALTER TABLE {$table_escaped} DROP INDEX {$name_escaped}"
        );
    }

    /**
     * Rename table
     *
     * ✅ SECURITY: Validates identifiers and uses backticks to prevent SQL injection
     */
    protected function renameTable(string $from, string $to): void
    {
        // Validate identifiers to prevent SQL injection
        if (!$this->validateIdentifier($from)) {
            throw new \InvalidArgumentException("Invalid source table name: '{$from}'");
        }
        if (!$this->validateIdentifier($to)) {
            throw new \InvalidArgumentException("Invalid target table name: '{$to}'");
        }

        $from = $this->prefix . $from;
        $to = $this->prefix . $to;

        // ✅ SECURITY: Use backticks for identifiers in RENAME TABLE
        $from_escaped = $this->escapeIdentifier($from);
        $to_escaped = $this->escapeIdentifier($to);
        $this->wpdb->query(
            "RENAME TABLE {$from_escaped} TO {$to_escaped}"
        );
    }
}
