<?php
declare(strict_types=1);

use Celena\Core\Database\Blueprint;
use Celena\Core\Database\Connection;
use Celena\Core\Database\Schema;

/**
 * Расширение каталога: добавляются производители, атрибуты, промокоды,
 * способы доставки и оплаты, плюс новые поля у товара (status, featured,
 * manufacturer_id, barcode, rating_avg/rating_count, deleted_at для архива).
 *
 * Миграция идемпотентна: каждая колонка/таблица проверяется перед созданием.
 */
return function (Schema $schema, Connection $conn): void {
    $driver = $conn->driver()->name();

    $hasColumn = function (string $table, string $column) use ($conn, $driver): bool {
        $tbl = $conn->table($table);
        if ($driver === 'pgsql') {
            $row = $conn->run(
                "SELECT 1 FROM information_schema.columns WHERE table_name = ? AND column_name = ? LIMIT 1",
                [$tbl, $column]
            )->fetch();
        } else {
            $row = $conn->run(
                "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ? LIMIT 1",
                [$tbl, $column]
            )->fetch();
        }
        return $row !== false && $row !== null;
    };

    $tblP = $conn->table_q('shop_products');

    if (!$hasColumn('shop_products', 'status')) {
        $col = $conn->quoteId('status');
        // active | draft | archived
        $conn->exec("ALTER TABLE $tblP ADD COLUMN $col VARCHAR(20) NOT NULL DEFAULT 'active'");
    }
    if (!$hasColumn('shop_products', 'is_featured')) {
        $col = $conn->quoteId('is_featured');
        $boolType = $driver === 'pgsql' ? 'BOOLEAN' : 'TINYINT(1)';
        $default = $driver === 'pgsql' ? 'FALSE' : '0';
        $conn->exec("ALTER TABLE $tblP ADD COLUMN $col $boolType NOT NULL DEFAULT $default");
    }
    if (!$hasColumn('shop_products', 'manufacturer_id')) {
        $col = $conn->quoteId('manufacturer_id');
        $conn->exec("ALTER TABLE $tblP ADD COLUMN $col BIGINT NULL");
    }
    if (!$hasColumn('shop_products', 'barcode')) {
        $col = $conn->quoteId('barcode');
        $conn->exec("ALTER TABLE $tblP ADD COLUMN $col VARCHAR(64) NULL");
    }
    if (!$hasColumn('shop_products', 'rating_avg')) {
        $col = $conn->quoteId('rating_avg');
        $conn->exec("ALTER TABLE $tblP ADD COLUMN $col DECIMAL(3,2) NOT NULL DEFAULT 0");
    }
    if (!$hasColumn('shop_products', 'rating_count')) {
        $col = $conn->quoteId('rating_count');
        $conn->exec("ALTER TABLE $tblP ADD COLUMN $col INT NOT NULL DEFAULT 0");
    }

    if (!$schema->hasTable('shop_manufacturers')) {
        $schema->create('shop_manufacturers', function (Blueprint $t) {
            $t->id();
            $t->string('slug', 191);
            $t->string('title', 255);
            $t->string('logo', 500)->nullable();
            $t->text('description')->nullable();
            $t->string('country', 100)->nullable();
            $t->string('website', 255)->nullable();
            $t->integer('sort_order')->default(0);
            $t->boolean('is_active')->default(true);
            $t->datetime('created_at')->nullable();
            $t->datetime('updated_at')->nullable();
            $t->unique('slug');
        });
    }

    if (!$schema->hasTable('shop_attributes')) {
        $schema->create('shop_attributes', function (Blueprint $t) {
            $t->id();
            $t->string('slug', 100);
            $t->string('title', 200);
            // text | number | select | multiselect | boolean | color
            $t->string('type', 20)->default('text');
            // JSON-список вариантов для select/multiselect/color
            $t->json('options')->nullable();
            $t->string('unit', 20)->nullable();
            $t->boolean('is_filterable')->default(true);
            $t->boolean('is_required')->default(false);
            $t->integer('sort_order')->default(0);
            $t->datetime('created_at')->nullable();
            $t->datetime('updated_at')->nullable();
            $t->unique('slug');
        });
    }

    if (!$schema->hasTable('shop_attribute_values')) {
        $schema->create('shop_attribute_values', function (Blueprint $t) {
            $t->id();
            $t->bigInteger('product_id');
            $t->bigInteger('attribute_id');
            $t->text('value')->nullable();
            $t->index('product_id');
            $t->index('attribute_id');
        });
    }

    if (!$schema->hasTable('shop_promo_codes')) {
        $schema->create('shop_promo_codes', function (Blueprint $t) {
            $t->id();
            $t->string('code', 64);
            // percent | fixed | free_shipping
            $t->string('type', 20)->default('percent');
            $t->decimal('value', 12, 2)->default(0);
            $t->decimal('min_amount', 12, 2)->nullable();
            $t->integer('usage_limit')->nullable();
            $t->integer('usage_count')->default(0);
            $t->datetime('starts_at')->nullable();
            $t->datetime('ends_at')->nullable();
            $t->boolean('is_active')->default(true);
            $t->text('comment')->nullable();
            $t->datetime('created_at')->nullable();
            $t->datetime('updated_at')->nullable();
            $t->unique('code');
        });
    }

    if (!$schema->hasTable('shop_shipping_methods')) {
        $schema->create('shop_shipping_methods', function (Blueprint $t) {
            $t->id();
            $t->string('slug', 100);
            $t->string('title', 200);
            $t->text('description')->nullable();
            // flat | by_weight | free | pickup
            $t->string('mode', 20)->default('flat');
            $t->decimal('cost', 12, 2)->default(0);
            $t->decimal('free_from', 12, 2)->nullable();
            $t->boolean('is_active')->default(true);
            $t->integer('sort_order')->default(0);
            $t->datetime('created_at')->nullable();
            $t->datetime('updated_at')->nullable();
            $t->unique('slug');
        });
    }

    if (!$schema->hasTable('shop_payment_methods')) {
        $schema->create('shop_payment_methods', function (Blueprint $t) {
            $t->id();
            $t->string('slug', 100);
            $t->string('title', 200);
            $t->text('description')->nullable();
            // cash | card_offline | online_gateway | invoice
            $t->string('kind', 30)->default('cash');
            // JSON-конфиг (ключи API, sandbox-флаг и т.п.) — заполняется суб-плагином
            $t->json('config')->nullable();
            $t->boolean('is_active')->default(true);
            $t->integer('sort_order')->default(0);
            $t->datetime('created_at')->nullable();
            $t->datetime('updated_at')->nullable();
            $t->unique('slug');
        });
    }

    // Бэкфилл: переносим старое is_active → status, чтобы фильтр по табам работал.
    $tblP = $conn->table_q('shop_products');
    $colStatus = $conn->quoteId('status');
    $colActive = $conn->quoteId('is_active');
    $activeVal = $driver === 'pgsql' ? 'TRUE' : '1';
    try {
        $conn->exec("UPDATE $tblP SET $colStatus = 'active' WHERE $colActive = $activeVal AND ($colStatus IS NULL OR $colStatus = '')");
        $conn->exec("UPDATE $tblP SET $colStatus = 'draft'  WHERE $colActive <> $activeVal AND ($colStatus IS NULL OR $colStatus = '')");
    } catch (\Throwable) {
    }
};
