<?php

namespace App\Console\Commands;

use App\Models\Device;
use App\Models\Photo;
use App\Services\PhotoPreview;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;

class PruneMissingPhotoFilesCommand extends Command
{
    protected $signature = 'coruna:prune-missing-photo-files
        {--execute : Actually delete DB rows (default is dry-run)}
        {--device= : Only this device_id or numeric id}';

    protected $description = 'Delete photos rows whose file is missing on the local disk';

    public function handle(PhotoPreview $previews): int
    {
        $execute = (bool) $this->option('execute');
        $q = Photo::query();
        $deviceFilter = trim((string) $this->option('device'));
        if ($deviceFilter !== '') {
            if (ctype_digit($deviceFilter)) {
                $q->where('photos.device_id', (int) $deviceFilter);
            } else {
                $q->join('devices', 'devices.id', '=', 'photos.device_id')
                    ->where('devices.device_id', $deviceFilter);
            }
        }

        $this->info(sprintf(
            '%s  drop photos rows with no disk file  batches of 500',
            $execute ? 'EXECUTE' : 'DRY-RUN',
        ));

        $disk = Storage::disk('local');
        $scanned = 0;
        $missing = 0;
        $deleted = 0;
        $touched = [];
        $lastId = 0;

        while (true) {
            $photos = (clone $q)
                ->select(['photos.id', 'photos.device_id', 'photos.path'])
                ->where('photos.id', '>', $lastId)
                ->orderBy('photos.id')
                ->limit(500)
                ->get();
            if ($photos->isEmpty()) {
                break;
            }

            $dropIds = [];
            foreach ($photos as $photo) {
                $scanned++;
                $path = trim((string) ($photo->path ?? ''));
                if ($path !== '' && $disk->exists($path)) {
                    continue;
                }
                $missing++;
                $dropIds[] = $photo->id;
                $touched[$photo->device_id] = true;
            }

            if ($execute && $dropIds !== []) {
                DB::table('mnemonic_findings')->whereIn('photo_id', $dropIds)->update(['photo_id' => null]);
                DB::table('photo_reads')->whereIn('photo_id', $dropIds)->delete();
                Photo::query()->whereIn('id', $dropIds)->delete();
                $deleted += count($dropIds);
            }

            $lastId = (int) $photos->last()->id;
            $this->info($execute
                ? sprintf('  scanned=%d missing=%d deleted=%d', $scanned, $missing, $deleted)
                : sprintf('  scanned=%d missing=%d', $scanned, $missing));
        }

        if ($missing === 0) {
            $this->info(sprintf('nothing to prune  scanned=%d', $scanned));

            return self::SUCCESS;
        }

        if (! $execute) {
            $this->info(sprintf(
                'dry-run scanned=%d missing=%d devices=%d; pass --execute to delete rows',
                $scanned,
                $missing,
                count($touched),
            ));

            return self::SUCCESS;
        }

        foreach (array_keys($touched) as $deviceId) {
            $device = Device::query()->find($deviceId);
            if ($device) {
                $previews->forgetForDevice((string) $device->device_id);
            }
        }

        $this->info(sprintf(
            'done  scanned=%d deleted_rows=%d devices=%d',
            $scanned,
            $deleted,
            count($touched),
        ));

        return self::SUCCESS;
    }
}