<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Mtc\MercuryDataModels\ResourceView;
use Mtc\MercuryDataModels\Vehicle;
use Mtc\MercuryDataModels\VehicleAttributeValue;
use Mtc\MercuryDataModels\VehicleAutoTraderData;
use Mtc\MercuryDataModels\VehicleFeature;
use Mtc\MercuryDataModels\VehicleFinance;
use Mtc\MercuryDataModels\VehicleLabel;
use Mtc\MercuryDataModels\VehicleStandardEquipment;
use Mtc\MercuryDataModels\VehicleTechnicalData;

class RemoveOrphanedVehicleData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'vehicles:purge-orphaned-data';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Remove vehicle data for hard deleted vehicles';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $limit = 500;

        collect([
            VehicleAutoTraderData::class,
            VehicleFinance::class,
            VehicleLabel::class,
        ])->each(fn ($model_name) => $this->deleteOrphanedRows($model_name, $limit));

        // Handle ResourceView separately as it uses polymorphic relations
        $this->deleteOrphanedResourceViews($limit);

        collect([
            VehicleAttributeValue::class,
            VehicleStandardEquipment::class,
            VehicleTechnicalData::class,
            VehicleFeature::class,
        ])->each(fn ($model_name) => $this->deleteOrphanedRows($model_name, $limit, 'owner'));
    }

    private function deleteOrphanedRows(string $model_name, int $limit, string $relation = 'vehicle')
    {
        $model_name::query()
            ->when(
                $relation === 'owner',
                fn ($ownerQuery) => $ownerQuery->whereDoesntHaveMorph(
                    $relation,
                    [Vehicle::class],
                    fn ($subQuery) => $subQuery->withTrashed()
                ),
                fn ($ownerQuery) => $ownerQuery->whereDoesntHave($relation, fn ($q) => $q->withTrashed())
            )
            ->limit($limit)
            ->delete();
    }

    private function deleteOrphanedResourceViews(int $limit)
    {
        // Delete resource views for vehicles that no longer exist (including soft-deleted)
        ResourceView::query()
            ->where('viewable_type', 'vehicle')
            ->whereNotExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('vehicles')
                    ->whereColumn('vehicles.id', 'resource_views.viewable_id');
            })
            ->limit($limit)
            ->delete();
    }
}
