<?php
namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Tagihan;
use App\Models\TarifPembayaran;
use Illuminate\Support\Facades\DB;

class CleanDuplicateTagihan extends Command
{
    /**
     * The name and signature of the console command.
     */
    protected $signature = 'clean:duplicate-tagihan';

    /**
     * The console command description.
     */
    protected $description = 'Remove duplicate tagihan rows, keeping the earliest record per student/kelas/tahun_ajaran/bulan and fixing the tarif to match the master tariff of that academic year.';

    public function handle()
    {
        $this->info('Scanning for duplicate tagihan...');

        // Group by the uniqueness columns (siswa_id, kelas_id, tahun_ajaran_id, bulan)
        $duplicates = Tagihan::select(
                'siswa_id',
                'kelas_id',
                'tahun_ajaran_id',
                'bulan',
                DB::raw('MIN(id) as keep_id'),
                DB::raw('GROUP_CONCAT(id) as ids')
            )
            ->groupBy('siswa_id', 'kelas_id', 'tahun_ajaran_id', 'bulan')
            ->havingRaw('COUNT(*) > 1')
            ->get();

        $totalDeleted = 0;
        foreach ($duplicates as $dup) {
            $ids = explode(',', $dup->ids);
            // keep the earliest record (by created_at) – we already have MIN(id) which usually matches earliest insert order
            $keepId = $dup->keep_id;
            $deleteIds = array_filter($ids, fn($i) => $i != $keepId);

            // Load the kept record so we can fix its tariff if needed
            $keepTagihan = Tagihan::find($keepId);
            if (!$keepTagihan) {
                continue;
            }

            // Determine the correct tariff for this tagihan based on its jenis (SPP/DPP) and tahun_ajaran_id
            $jenis = $keepTagihan->tarifPembayaran ? $keepTagihan->tarifPembayaran->jenis : 'SPP';
            $correctTarif = TarifPembayaran::where('jenis', $jenis)
                ->where('tahun_ajaran_id', $keepTagihan->tahun_ajaran_id)
                ->where('status', 'aktif')
                ->first();

            if ($correctTarif && $keepTagihan->tarif_pembayaran_id !== $correctTarif->id) {
                $keepTagihan->tarif_pembayaran_id = $correctTarif->id;
                // Preserve existing snapshot fields and status – they are already correct on the earliest row
                $keepTagihan->save();
                $this->line("Updated tarif for tagihan ID {$keepId} to {$correctTarif->id}");
            }

            // Delete the duplicate rows
            Tagihan::whereIn('id', $deleteIds)->delete();
            $totalDeleted += count($deleteIds);
            $this->line('Deleted duplicates: ' . implode(', ', $deleteIds));
        }

        $this->info("Cleanup complete. Total duplicate rows removed: {$totalDeleted}");
        return 0;
    }
}
?>
