<?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
    {
        // Step 1: Add new columns to m_store_raw_materials
        if (Schema::hasTable('m_store_raw_materials')) {
            Schema::table('m_store_raw_materials', function (Blueprint $table) {
                if (!Schema::hasColumn('m_store_raw_materials', 'lsm_flag')) {
                    $table->boolean('lsm_flag')->default(true)->after('tipe_dokumen');
                }
                if (!Schema::hasColumn('m_store_raw_materials', 'spb_flag')) {
                    $table->boolean('spb_flag')->default(true)->after('lsm_flag');
                }
                if (!Schema::hasColumn('m_store_raw_materials', 'inv_matrix_flag')) {
                    $table->boolean('inv_matrix_flag')->default(false)->after('spb_flag');
                }
                if (!Schema::hasColumn('m_store_raw_materials', 'period_id')) {
                    $table->unsignedBigInteger('period_id')->nullable()->after('inv_matrix_flag');
                }
            });

            // Add foreign key for period_id if not exists
            $foreignKeyExists = DB::select("
                SELECT constraint_name 
                FROM information_schema.table_constraints 
                WHERE table_name = 'm_store_raw_materials' 
                AND constraint_name = 'm_store_raw_materials_period_id_foreign'
                AND table_schema = current_schema()
            ");

            if (empty($foreignKeyExists)) {
                Schema::table('m_store_raw_materials', function (Blueprint $table) {
                    $table->foreign('period_id')->references('id')->on('m_periods')->onDelete('set null');
                });
            }
        }

        // Step 2: Migrate data from m_raw_materials to m_store_raw_materials
        // Copy flags and period_id from raw_materials to all existing store mappings
        if (Schema::hasTable('m_raw_materials') && Schema::hasTable('m_store_raw_materials')) {
            DB::statement("
                UPDATE m_store_raw_materials srm
                SET 
                    lsm_flag = CAST(rm.lsm_flag AS boolean),
                    spb_flag = CAST(rm.spb_flag AS boolean),
                    inv_matrix_flag = CAST(rm.inv_matrix_flag AS boolean),
                    period_id = rm.period_id
                FROM m_raw_materials rm
                WHERE srm.raw_material_id = rm.id
            ");
        }

        // Step 3: Remove columns from m_raw_materials
        if (Schema::hasTable('m_raw_materials')) {
            Schema::table('m_raw_materials', function (Blueprint $table) {
                // Drop foreign key first if exists
                $foreignKeyExists = DB::select("
                    SELECT constraint_name 
                    FROM information_schema.table_constraints 
                    WHERE table_name = 'm_raw_materials' 
                    AND constraint_name = 'm_raw_materials_period_id_foreign'
                    AND table_schema = current_schema()
                ");

                if (!empty($foreignKeyExists)) {
                    $table->dropForeign(['period_id']);
                }

                // Drop columns if they exist
                if (Schema::hasColumn('m_raw_materials', 'lsm_flag')) {
                    $table->dropColumn('lsm_flag');
                }
                if (Schema::hasColumn('m_raw_materials', 'spb_flag')) {
                    $table->dropColumn('spb_flag');
                }
                if (Schema::hasColumn('m_raw_materials', 'inv_matrix_flag')) {
                    $table->dropColumn('inv_matrix_flag');
                }
                if (Schema::hasColumn('m_raw_materials', 'period_id')) {
                    $table->dropColumn('period_id');
                }
            });
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Step 1: Add columns back to m_raw_materials
        if (Schema::hasTable('m_raw_materials')) {
            Schema::table('m_raw_materials', function (Blueprint $table) {
                if (!Schema::hasColumn('m_raw_materials', 'lsm_flag')) {
                    $table->boolean('lsm_flag')->default(true)->after('konversi_satuan_kirim');
                }
                if (!Schema::hasColumn('m_raw_materials', 'spb_flag')) {
                    $table->boolean('spb_flag')->default(true)->after('lsm_flag');
                }
                if (!Schema::hasColumn('m_raw_materials', 'inv_matrix_flag')) {
                    $table->boolean('inv_matrix_flag')->default(false)->after('tipe_barang');
                }
                if (!Schema::hasColumn('m_raw_materials', 'period_id')) {
                    $table->unsignedBigInteger('period_id')->nullable()->after('tipe_barang');
                    $table->foreign('period_id')->references('id')->on('m_periods')->onDelete('set null');
                }
            });
        }

        // Step 2: Migrate data back (take first store mapping as source)
        if (Schema::hasTable('m_raw_materials') && Schema::hasTable('m_store_raw_materials')) {
            DB::statement("
                UPDATE m_raw_materials rm
                SET 
                    lsm_flag = srm.lsm_flag,
                    spb_flag = srm.spb_flag,
                    inv_matrix_flag = srm.inv_matrix_flag,
                    period_id = srm.period_id
                FROM (
                    SELECT DISTINCT ON (raw_material_id) 
                        raw_material_id, lsm_flag, spb_flag, inv_matrix_flag, period_id
                    FROM m_store_raw_materials
                    ORDER BY raw_material_id, id
                ) srm
                WHERE rm.id = srm.raw_material_id
            ");
        }

        // Step 3: Remove columns from m_store_raw_materials
        if (Schema::hasTable('m_store_raw_materials')) {
            Schema::table('m_store_raw_materials', function (Blueprint $table) {
                // Drop foreign key first if exists
                $foreignKeyExists = DB::select("
                    SELECT constraint_name 
                    FROM information_schema.table_constraints 
                    WHERE table_name = 'm_store_raw_materials' 
                    AND constraint_name = 'm_store_raw_materials_period_id_foreign'
                    AND table_schema = current_schema()
                ");

                if (!empty($foreignKeyExists)) {
                    $table->dropForeign(['period_id']);
                }

                if (Schema::hasColumn('m_store_raw_materials', 'lsm_flag')) {
                    $table->dropColumn('lsm_flag');
                }
                if (Schema::hasColumn('m_store_raw_materials', 'spb_flag')) {
                    $table->dropColumn('spb_flag');
                }
                if (Schema::hasColumn('m_store_raw_materials', 'inv_matrix_flag')) {
                    $table->dropColumn('inv_matrix_flag');
                }
                if (Schema::hasColumn('m_store_raw_materials', 'period_id')) {
                    $table->dropColumn('period_id');
                }
            });
        }
    }
};
