<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // 1. 새 테이블 생성
        Schema::create('construction_site_photo_rows', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('construction_site_photo_id')->comment('사진대지 ID');
            $table->unsignedInteger('sort_order')->default(0)->comment('정렬 순서 (0부터)');

            // 작업전 사진
            $table->string('before_photo_path', 500)->nullable()->comment('작업전 사진 GCS 경로');
            $table->string('before_photo_gcs_uri', 500)->nullable()->comment('작업전 사진 GCS URI');
            $table->unsignedInteger('before_photo_size')->nullable()->comment('작업전 사진 파일크기(bytes)');

            // 작업중 사진
            $table->string('during_photo_path', 500)->nullable()->comment('작업중 사진 GCS 경로');
            $table->string('during_photo_gcs_uri', 500)->nullable()->comment('작업중 사진 GCS URI');
            $table->unsignedInteger('during_photo_size')->nullable()->comment('작업중 사진 파일크기(bytes)');

            // 작업후 사진
            $table->string('after_photo_path', 500)->nullable()->comment('작업후 사진 GCS 경로');
            $table->string('after_photo_gcs_uri', 500)->nullable()->comment('작업후 사진 GCS URI');
            $table->unsignedInteger('after_photo_size')->nullable()->comment('작업후 사진 파일크기(bytes)');

            $table->timestamps();

            $table->foreign('construction_site_photo_id')
                ->references('id')
                ->on('construction_site_photos')
                ->cascadeOnDelete();

            $table->index('construction_site_photo_id', 'idx_cspr_photo_id');
        });

        // 2. 기존 데이터를 새 테이블로 마이그레이션
        DB::statement("
            INSERT INTO construction_site_photo_rows
                (construction_site_photo_id, sort_order,
                 before_photo_path, before_photo_gcs_uri, before_photo_size,
                 during_photo_path, during_photo_gcs_uri, during_photo_size,
                 after_photo_path, after_photo_gcs_uri, after_photo_size,
                 created_at, updated_at)
            SELECT
                id, 0,
                before_photo_path, before_photo_gcs_uri, before_photo_size,
                during_photo_path, during_photo_gcs_uri, during_photo_size,
                after_photo_path, after_photo_gcs_uri, after_photo_size,
                created_at, updated_at
            FROM construction_site_photos
            WHERE deleted_at IS NULL
              AND (before_photo_path IS NOT NULL
                   OR during_photo_path IS NOT NULL
                   OR after_photo_path IS NOT NULL)
        ");

        // 3. 부모 테이블에서 사진 컬럼 삭제
        Schema::table('construction_site_photos', function (Blueprint $table) {
            $table->dropColumn([
                'before_photo_path', 'before_photo_gcs_uri', 'before_photo_size',
                'during_photo_path', 'during_photo_gcs_uri', 'during_photo_size',
                'after_photo_path', 'after_photo_gcs_uri', 'after_photo_size',
            ]);
        });
    }

    public function down(): void
    {
        // 부모 테이블에 사진 컬럼 복원
        Schema::table('construction_site_photos', function (Blueprint $table) {
            $table->string('before_photo_path', 500)->nullable()->after('description');
            $table->string('before_photo_gcs_uri', 500)->nullable()->after('before_photo_path');
            $table->unsignedInteger('before_photo_size')->nullable()->after('before_photo_gcs_uri');

            $table->string('during_photo_path', 500)->nullable()->after('before_photo_size');
            $table->string('during_photo_gcs_uri', 500)->nullable()->after('during_photo_path');
            $table->unsignedInteger('during_photo_size')->nullable()->after('during_photo_gcs_uri');

            $table->string('after_photo_path', 500)->nullable()->after('during_photo_size');
            $table->string('after_photo_gcs_uri', 500)->nullable()->after('after_photo_path');
            $table->unsignedInteger('after_photo_size')->nullable()->after('after_photo_gcs_uri');
        });

        // 데이터 복원 (sort_order=0인 첫 행만)
        DB::statement("
            UPDATE construction_site_photos p
            INNER JOIN construction_site_photo_rows r ON r.construction_site_photo_id = p.id AND r.sort_order = 0
            SET p.before_photo_path = r.before_photo_path,
                p.before_photo_gcs_uri = r.before_photo_gcs_uri,
                p.before_photo_size = r.before_photo_size,
                p.during_photo_path = r.during_photo_path,
                p.during_photo_gcs_uri = r.during_photo_gcs_uri,
                p.during_photo_size = r.during_photo_size,
                p.after_photo_path = r.after_photo_path,
                p.after_photo_gcs_uri = r.after_photo_gcs_uri,
                p.after_photo_size = r.after_photo_size
        ");

        Schema::dropIfExists('construction_site_photo_rows');
    }
};
