<?php

namespace App\Imports;

use DateTime;
use Exception;
use Throwable;
use Carbon\Carbon;
use App\Models\Nas;
use App\Models\Pop;
use App\Models\Client;
use App\Models\Packages;
use App\Models\SubPackage;
use App\Models\Clientsinfo;
use GuzzleHttp\Promise\Create;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use App\Models\MikcrotikClientList;
use App\Models\ReselleBalanceLogReport;
use Maatwebsite\Excel\Concerns\Importable;
use App\Models\SubResellerBalanceLogReport;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Facade\Ignition\Support\Packagist\Package;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class ClientsImport implements ToCollection
{
    use Importable;
    public function collection(Collection $rows)
    {
        // This import is used to clean up duplicate rows in `reselle_balance_log_reports`
        // Keep only the FIRST record per user (client) within a given time range,
        // delete the repeated ones, and calculate the total `amount` of the deleted rows.
        // The set of rows to consider is limited to the IDs coming from the Excel file.

        // Time window: 06-Jan-2026 12:06:38am to 06-Jan-2026 12:08:16am
        // (same as: $fromTime / $toTime using strtotime)
        $start = Carbon::createFromFormat('d-M-Y h:i:sa', '06-Jan-2026 12:06:38am');
        $end   = Carbon::createFromFormat('d-M-Y h:i:sa', '06-Jan-2026 12:08:16am');

        // Collect the ReselleBalanceLogReport IDs from Excel (assume first column = log id, skip header row)
        $idsFromExcel = [];
        $rowIndex = 0;
        foreach ($rows as $row) {
            if ($rowIndex === 0) {
                // header row
                $rowIndex++;
                continue;
            }

            if (isset($row[0]) && $row[0] !== null && $row[0] !== '') {
                $idsFromExcel[] = (int) $row[0];
            }
            $rowIndex++;
        }

        // If no IDs provided, nothing to do
        if (empty($idsFromExcel)) {
            return;
        }

        $totalDeletedAmount = 0;

        DB::transaction(function () use ($start, $end, $idsFromExcel, &$totalDeletedAmount) {
            // Find client_ids that have more than one record in the given time range
            // AND whose rows' IDs are in the uploaded Excel list
            $duplicateClientIds = ReselleBalanceLogReport::select('client_id')
                ->whereBetween('created_at', [$start, $end])
                ->whereIn('id', $idsFromExcel)
                ->groupBy('client_id')
                ->havingRaw('COUNT(*) > 1')
                ->pluck('client_id');

            foreach ($duplicateClientIds as $clientId) {
                // Get all logs for this client in the range, ordered so the FIRST is the one we keep
                $logs = ReselleBalanceLogReport::where('client_id', $clientId)
                    ->whereBetween('created_at', [$start, $end])
                    ->whereIn('id', $idsFromExcel)
                    ->orderBy('created_at') // first by time
                    ->orderBy('id')         // then by id as tie‑breaker
                    ->get();

                if ($logs->count() <= 1) {
                    continue; // nothing to deduplicate
                }

                // Remove the first item from the collection – this is the one we KEEP
                $logs->shift();

                // The remaining logs are duplicates we want to DELETE
                $idsToDelete = $logs->pluck('id');

                // Sum of `amount` for all duplicates of this client in the range
                $sumAmount = $logs->sum('amount');
                $totalDeletedAmount += $sumAmount;

                if ($idsToDelete->isNotEmpty()) {
                    ReselleBalanceLogReport::whereIn('id', $idsToDelete)->delete();
                }
            }
        });

        // Show the total deleted amount when the import finishes
        // (you can replace this with logging or a flash message if needed)
        dd('Total deleted amount:', $totalDeletedAmount);
    }
}
