<?php

use Illuminate\Database\Migrations\Migration;

/**
 * Add bar_status and kitchen_status columns to the orders table
 * in every tenant schema that exists in the pgsql_country database.
 *
 * PostgreSQL supports ADD COLUMN IF NOT EXISTS (≥ 9.6) so this
 * migration is safe to run multiple times.
 */
return new class extends Migration
{
    public function up(): void
    {
        /** @var \Illuminate\Support\Collection $schemas */
        $schemas = \DB::table('vendor_users')
            ->whereNotNull('schema_name')
            ->distinct()
            ->pluck('schema_name');

        foreach ($schemas as $schema) {
            // Validate schema name to prevent SQL injection
            if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $schema)) {
                continue;
            }

            \DB::connection('pgsql_country')->statement(
                "ALTER TABLE IF EXISTS {$schema}.orders
                    ADD COLUMN IF NOT EXISTS bar_status VARCHAR(50),
                    ADD COLUMN IF NOT EXISTS kitchen_status VARCHAR(50)"
            );
        }
    }

    public function down(): void
    {
        $schemas = \DB::table('vendor_users')
            ->whereNotNull('schema_name')
            ->distinct()
            ->pluck('schema_name');

        foreach ($schemas as $schema) {
            if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $schema)) {
                continue;
            }

            \DB::connection('pgsql_country')->statement(
                "ALTER TABLE IF EXISTS {$schema}.orders
                    DROP COLUMN IF EXISTS bar_status,
                    DROP COLUMN IF EXISTS kitchen_status"
            );
        }
    }
};
