<?php

declare(strict_types=1);

use App\Business\Product\ProductSkuLineBackfillService;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        $this->addDocumentLineCompositeIndexes();
        $this->addInventoryTransactionWarehouseProductSkuIndex();
        $this->addInventoryBalanceWarehouseProductSkuIndex();

        if (! Schema::hasTable('dberp_product_skus')) {
            return;
        }

        app(ProductSkuLineBackfillService::class)->run(false, false);
    }

    public function down(): void
    {
        $this->dropInventoryBalanceWarehouseProductSkuIndex();
        $this->dropInventoryTransactionWarehouseProductSkuIndex();
        $this->dropDocumentLineCompositeIndexes();

        // 第 6 步数据回填不可逆：不在此回滚业务数据。
    }

    private function addDocumentLineCompositeIndexes(): void
    {
        $specs = [
            ['dberp_purchase_order_items', 'idx_step6_dpoi_prod_psku'],
            ['dberp_purchase_receipt_order_items', 'idx_step6_dproi_prod_psku'],
            ['dberp_sales_order_items', 'idx_step6_dsoi_prod_psku'],
            ['dberp_sales_outbound_order_items', 'idx_step6_dsouti_prod_psku'],
        ];

        foreach ($specs as [$tableName, $indexName]) {
            if (! Schema::hasTable($tableName)) {
                continue;
            }

            if (! Schema::hasColumn($tableName, 'product_id') || ! Schema::hasColumn($tableName, 'product_sku_id')) {
                continue;
            }

            Schema::table($tableName, function (Blueprint $blueprint) use ($tableName, $indexName): void {
                if (! $this->indexExists($tableName, $indexName)) {
                    $blueprint->index(['product_id', 'product_sku_id'], $indexName);
                }
            });
        }
    }

    private function dropDocumentLineCompositeIndexes(): void
    {
        $specs = [
            ['dberp_purchase_order_items', 'idx_step6_dpoi_prod_psku'],
            ['dberp_purchase_receipt_order_items', 'idx_step6_dproi_prod_psku'],
            ['dberp_sales_order_items', 'idx_step6_dsoi_prod_psku'],
            ['dberp_sales_outbound_order_items', 'idx_step6_dsouti_prod_psku'],
        ];

        foreach ($specs as [$tableName, $indexName]) {
            if (! Schema::hasTable($tableName)) {
                continue;
            }

            Schema::table($tableName, function (Blueprint $blueprint) use ($tableName, $indexName): void {
                if ($this->indexExists($tableName, $indexName)) {
                    $blueprint->dropIndex($indexName);
                }
            });
        }
    }

    private function addInventoryTransactionWarehouseProductSkuIndex(): void
    {
        $tableName = 'dberp_inventory_transactions';
        $indexName = 'idx_step6_dinvtx_wh_prod_psku';

        if (! Schema::hasTable($tableName)) {
            return;
        }

        if (! Schema::hasColumn($tableName, 'warehouse_id')
            || ! Schema::hasColumn($tableName, 'product_id')
            || ! Schema::hasColumn($tableName, 'product_sku_id')) {
            return;
        }

        Schema::table($tableName, function (Blueprint $blueprint) use ($tableName, $indexName): void {
            if (! $this->indexExists($tableName, $indexName)) {
                $blueprint->index(['warehouse_id', 'product_id', 'product_sku_id'], $indexName);
            }
        });
    }

    private function dropInventoryTransactionWarehouseProductSkuIndex(): void
    {
        $tableName = 'dberp_inventory_transactions';
        $indexName = 'idx_step6_dinvtx_wh_prod_psku';

        if (! Schema::hasTable($tableName)) {
            return;
        }

        Schema::table($tableName, function (Blueprint $blueprint) use ($tableName, $indexName): void {
            if ($this->indexExists($tableName, $indexName)) {
                $blueprint->dropIndex($indexName);
            }
        });
    }

    private function addInventoryBalanceWarehouseProductSkuIndex(): void
    {
        $tableName = 'dberp_inventory_balances';
        $indexName = 'idx_step6_dinvbal_wh_prod_psku';

        if (! Schema::hasTable($tableName)) {
            return;
        }

        if (! Schema::hasColumn($tableName, 'warehouse_id')
            || ! Schema::hasColumn($tableName, 'product_id')
            || ! Schema::hasColumn($tableName, 'product_sku_id')) {
            return;
        }

        Schema::table($tableName, function (Blueprint $blueprint) use ($tableName, $indexName): void {
            if (! $this->indexExists($tableName, $indexName)) {
                $blueprint->index(['warehouse_id', 'product_id', 'product_sku_id'], $indexName);
            }
        });
    }

    private function dropInventoryBalanceWarehouseProductSkuIndex(): void
    {
        $tableName = 'dberp_inventory_balances';
        $indexName = 'idx_step6_dinvbal_wh_prod_psku';

        if (! Schema::hasTable($tableName)) {
            return;
        }

        Schema::table($tableName, function (Blueprint $blueprint) use ($tableName, $indexName): void {
            if ($this->indexExists($tableName, $indexName)) {
                $blueprint->dropIndex($indexName);
            }
        });
    }

    private function indexExists(string $table, string $indexName): bool
    {
        $connection = Schema::getConnection();
        $driver = $connection->getDriverName();

        if ($driver === 'sqlite') {
            $rows = $connection->select('PRAGMA index_list(`'.$table.'`)');

            foreach ($rows as $row) {
                if (($row->name ?? null) === $indexName) {
                    return true;
                }
            }

            return false;
        }

        $database = $connection->getDatabaseName();

        $result = $connection->selectOne(
            'select count(*) as c from information_schema.statistics where table_schema = ? and table_name = ? and index_name = ?',
            [$database, $table, $indexName],
        );

        return isset($result->c) && (int) $result->c > 0;
    }
};
