<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

/**
 * One-off / manual migration of attendance records from the historical
 * `activity_log` table into the `attendance` table for a given date or
 * date range.
 *
 * Only records that match the existing attendance logging logic are copied:
 *   - subject_type matches the User entity (same as ami:flush-events)
 *   - description maps to a known attendance description_code
 *
 * Original activity_log rows are never modified or deleted. Rows that already
 * exist in `attendance` (same subject_id + description + log_name + created_at)
 * are skipped, and duplicates inside the source batch are collapsed into one.
 */
class MigrateAttendanceCommand extends Command
{
    protected $signature = 'attendance:migrate
                            {start : Start date (Y-m-d). A single date migrates just that day.}
                            {end? : End date (Y-m-d, optional)}';

    protected $description = 'Migrate attendance records from activity_log into the attendance table for a date or date range';

    private const SUBJECT_TYPE = 'Modules\Platform\User\Entities\User';

    private const DESCRIPTION_CODES = [
        'LOGGED_IN'   => 1,
        'Reachable'   => 2,
        'Unreachable' => 3,
        'LOGGED_OUT'  => 4,
        'unpause'     => 5,
        'pause'       => 6,
    ];

    public function handle(): int
    {
        try {
            $start = Carbon::parse($this->argument('start'))->startOfDay();
            $end = $this->argument('end')
                ? Carbon::parse($this->argument('end'))->endOfDay()
                : $start->copy()->endOfDay();
        } catch (\Throwable $e) {
            $this->error('Invalid date format. Use Y-m-d (e.g. 2026-08-17).');
            return 1;
        }

        if ($start->greaterThan($end)) {
            $this->error('Start date must be before or equal to the end date.');
            return 1;
        }

        $from = $start->format('Y-m-d H:i:s');
        $to = $end->format('Y-m-d H:i:s');

        $this->info("Migrating attendance records from activity_log for {$from} → {$to}");

        // -----------------------------------------------------------------------
        // 1. Keys of attendance rows already present in the period (duplicate guard).
        // -----------------------------------------------------------------------
        $existing = DB::table('attendance')
            ->whereBetween('created_at', [$from, $to])
            ->get(['subject_id', 'description', 'log_name', 'created_at'])
            ->map(fn ($row) => $this->rowKey($row->subject_id, $row->description, $row->log_name, $row->created_at))
            ->flip();

        // -----------------------------------------------------------------------
        // 2. Collect candidates from activity_log, deduplicating against both
        //    existing attendance rows and rows already staged in this batch.
        // -----------------------------------------------------------------------
        $batch = [];
        $skipped = 0;

        DB::table('activity_log')
            ->where('subject_type', self::SUBJECT_TYPE)
            ->whereNotNull('subject_id')
            ->whereNotNull('log_name')
            ->whereBetween('created_at', [$from, $to])
            ->whereIn('description', array_keys(self::DESCRIPTION_CODES))
            ->orderBy('id')
            ->chunkById(1000, function ($rows) use (&$batch, &$skipped, $existing) {
                foreach ($rows as $row) {
                    $code = self::DESCRIPTION_CODES[$row->description] ?? null;

                    if ($code === null) {
                        $skipped++;
                        continue;
                    }

                    $key = $this->rowKey($row->subject_id, $row->description, $row->log_name, $row->created_at);

                    if (isset($existing[$key]) || isset($batch[$key])) {
                        $skipped++;
                        continue;
                    }

                    $batch[$key] = [
                        'log_name'         => $row->log_name,
                        'description'      => $row->description,
                        'description_code' => $code,
                        'subject_id'       => $row->subject_id,
                        'created_at'       => $row->created_at,
                        'updated_at'       => $row->updated_at,
                    ];
                }
            });

        // -----------------------------------------------------------------------
        // 3. Insert in chunks; on chunk failure retry row-by-row so failures are
        //    counted precisely without aborting the whole migration.
        // -----------------------------------------------------------------------
        $migrated = 0;
        $failed = 0;
        $failures = [];

        foreach (array_chunk($batch, 500) as $chunk) {
            try {
                DB::transaction(function () use ($chunk) {
                    DB::table('attendance')->insert($chunk);
                });
                $migrated += count($chunk);
            } catch (\Throwable $e) {
                foreach ($chunk as $row) {
                    try {
                        DB::table('attendance')->insert($row);
                        $migrated++;
                    } catch (\Throwable $e2) {
                        $failed++;
                        $failures[] = "subject_id={$row['subject_id']} | {$row['description']} | {$row['log_name']} | {$row['created_at']} ({$e2->getMessage()})";
                    }
                }
            }
        }

        // -----------------------------------------------------------------------
        // 4. Summary.
        // -----------------------------------------------------------------------
        $this->newLine();
        $this->table(
            ['Metric', 'Count'],
            [
                ['Candidate records', count($batch) + $skipped],
                ['Migrated', $migrated],
                ['Skipped (duplicate)', $skipped],
                ['Failed', $failed],
            ]
        );

        if (!empty($failures)) {
            $this->newLine();
            $this->error('Failed records:');
            foreach (array_slice($failures, 0, 20) as $failure) {
                $this->line('  - ' . $failure);
            }
            if (count($failures) > 20) {
                $this->line('  ... and ' . (count($failures) - 20) . ' more');
            }
        }

        $this->newLine();
        $this->info('activity_log was not modified.');

        return $failed > 0 ? 1 : 0;
    }

    private function rowKey($subjectId, $description, $logName, $createdAt): string
    {
        return $subjectId . '|' . $description . '|' . $logName . '|' . $createdAt;
    }
}