<?php

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

class UpdateLeadsForContactPrivacyAndTracking extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('leads', function (Blueprint $table) {
            if (!Schema::hasColumn('leads', 'booth_area_sqft')) {
                $table->decimal('booth_area_sqft', 10, 2)->nullable()->after('booth_area_sqm');
            }

            if (!Schema::hasColumn('leads', 'description')) {
                $table->text('description')->nullable()->after('phone_number');
            }
        });

        if (Schema::getConnection()->getDriverName() === 'mysql') {
            $statuses = ['New', 'Costing', 'Quoted', 'Negotiation', 'Won/Ongoing', 'Lost', 'Garbage', 'Dead', 'Out of Budget'];

            DB::table('leads')->select('id', 'lead_status')->orderBy('id')->chunkById(100, function ($leads) use ($statuses) {
                foreach ($leads as $lead) {
                    $cleanStatus = trim(strip_tags((string) $lead->lead_status));

                    DB::table('leads')
                        ->where('id', $lead->id)
                        ->update(['lead_status' => in_array($cleanStatus, $statuses, true) ? $cleanStatus : 'New']);
                }
            });

            DB::statement("ALTER TABLE leads MODIFY fair_name VARCHAR(255) NULL");
            DB::statement("ALTER TABLE leads MODIFY city VARCHAR(120) NULL");
            DB::statement("ALTER TABLE leads MODIFY country VARCHAR(120) NULL");
            DB::statement("ALTER TABLE leads MODIFY fair_start_date DATE NULL");
            DB::statement("ALTER TABLE leads MODIFY fair_end_date DATE NULL");
            DB::statement("ALTER TABLE leads MODIFY booth_area_sqm DECIMAL(10, 2) NULL");
            DB::statement("ALTER TABLE leads MODIFY lead_budget DECIMAL(12, 2) NULL");
            DB::statement("ALTER TABLE leads MODIFY company_name VARCHAR(255) NULL");
            DB::statement("ALTER TABLE leads MODIFY lead_status ENUM('New', 'Costing', 'Quoted', 'Negotiation', 'Won/Ongoing', 'Lost', 'Garbage', 'Dead', 'Out of Budget') DEFAULT 'New'");
        }

        Schema::create('lead_views', function (Blueprint $table) {
            $table->id();
            $table->foreignId('lead_id')->constrained('leads')->cascadeOnDelete();
            $table->foreignId('user_id')->constrained('users')->cascadeOnDelete();
            $table->unsignedInteger('view_count')->default(0);
            $table->timestamp('last_viewed_at')->nullable();
            $table->timestamps();

            $table->unique(['lead_id', 'user_id']);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('lead_views');

        Schema::table('leads', function (Blueprint $table) {
            if (Schema::hasColumn('leads', 'booth_area_sqft')) {
                $table->dropColumn('booth_area_sqft');
            }

            if (Schema::hasColumn('leads', 'description')) {
                $table->dropColumn('description');
            }
        });

        if (Schema::getConnection()->getDriverName() === 'mysql') {
            DB::statement("ALTER TABLE leads MODIFY lead_status ENUM('New', 'Costing', 'Quoted', 'Negotiation', 'Won/Ongoing', 'Lost') DEFAULT 'New'");
        }
    }
}
