<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Populate division_deliveries table with existing inventory data

        // First, populate with current inventory (deliveries that created the inventory)
        $query = "
            INSERT INTO division_deliveries (
                division_id,
                item_id,
                total_quantity_delivered,
                current_available_quantity,
                total_quantity_issued,
                average_unit_price,
                total_value,
                first_delivery_date,
                last_delivery_date,
                last_updated,
                created_at,
                updated_at
            )
            SELECT
                i.division_id,
                i.item_id,
                SUM(i.quantity_available) as total_quantity_delivered,
                SUM(i.quantity_available) as current_available_quantity,
                0 as total_quantity_issued,
                AVG(i.unit_price) as average_unit_price,
                SUM(i.quantity_available * i.unit_price) as total_value,
                MIN(i.created_at) as first_delivery_date,
                MAX(i.last_updated) as last_delivery_date,
                NOW() as last_updated,
                NOW() as created_at,
                NOW() as updated_at
            FROM inventory i
            WHERE i.division_id IS NOT NULL
            AND i.quantity_available > 0
            GROUP BY i.division_id, i.item_id
        ";

        DB::statement($query);

        // Now calculate total issued quantities from outgoing transactions
        $issuedQuery = "
            UPDATE division_deliveries dd
            INNER JOIN (
                SELECT
                    oi.division_id,
                    oi.item_id,
                    SUM(oi.quantity_released) as total_issued
                FROM outgoing_items oi
                INNER JOIN outgoing_transactions ot ON oi.outgoing_transaction_id = ot.id
                WHERE oi.division_id IS NOT NULL
                AND ot.status = 'successful'
                GROUP BY oi.division_id, oi.item_id
            ) issued ON dd.division_id = issued.division_id AND dd.item_id = issued.item_id
            SET
                dd.total_quantity_issued = issued.total_issued,
                dd.total_quantity_delivered = dd.current_available_quantity + issued.total_issued,
                dd.total_value = dd.total_quantity_delivered * dd.average_unit_price,
                dd.last_updated = NOW()
        ";

        DB::statement($issuedQuery);

        \Log::info('Division deliveries table populated with existing data');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Clear the division_deliveries table
        DB::table('division_deliveries')->truncate();
        \Log::info('Division deliveries table cleared');
    }
};