strprfcrm/database/migrations/2026_02_12_130001_create_spare_parts_view.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::statement("DROP VIEW IF EXISTS spare_parts_view");

        DB::statement("
            CREATE VIEW spare_parts_view AS
            SELECT
                sp.*,
                COALESCE(spo_agg.physical_stock_without_docs, 0) AS physical_stock_without_docs,
                COALESCE(spo_agg.physical_stock_with_docs, 0) AS physical_stock_with_docs,
                COALESCE(res_agg.reserved_without_docs, 0) AS reserved_without_docs,
                COALESCE(res_agg.reserved_with_docs, 0) AS reserved_with_docs,
                (COALESCE(spo_agg.physical_stock_without_docs, 0) - COALESCE(res_agg.reserved_without_docs, 0)) AS quantity_without_docs,
                (COALESCE(spo_agg.physical_stock_with_docs, 0) - COALESCE(res_agg.reserved_with_docs, 0)) AS quantity_with_docs,
                ((COALESCE(spo_agg.physical_stock_without_docs, 0) - COALESCE(res_agg.reserved_without_docs, 0))
                 + (COALESCE(spo_agg.physical_stock_with_docs, 0) - COALESCE(res_agg.reserved_with_docs, 0))) AS total_quantity
            FROM spare_parts sp
            LEFT JOIN (
                SELECT
                    spare_part_id,
                    SUM(CASE WHEN status = 'in_stock' AND with_documents = 0 THEN available_qty ELSE 0 END) AS physical_stock_without_docs,
                    SUM(CASE WHEN status = 'in_stock' AND with_documents = 1 THEN available_qty ELSE 0 END) AS physical_stock_with_docs
                FROM spare_part_orders
                WHERE deleted_at IS NULL
                GROUP BY spare_part_id
            ) spo_agg ON spo_agg.spare_part_id = sp.id
            LEFT JOIN (
                SELECT
                    spare_part_id,
                    SUM(CASE WHEN status = 'active' AND with_documents = 0 THEN reserved_qty ELSE 0 END) AS reserved_without_docs,
                    SUM(CASE WHEN status = 'active' AND with_documents = 1 THEN reserved_qty ELSE 0 END) AS reserved_with_docs
                FROM reservations
                GROUP BY spare_part_id
            ) res_agg ON res_agg.spare_part_id = sp.id
            WHERE sp.deleted_at IS NULL
        ");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement("DROP VIEW IF EXISTS spare_parts_view");
    }
};