<?php

namespace App\Console\Commands;

use App\Models\User;
use App\Models\Bid;
use App\Models\Sell;
use App\Models\Ownership;
use App\Models\Investment;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class ResetForICTP extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'ictp:reset
        {--chunk=500 : Users per batch (default 500)}
        {--dry-run : Count and preview without making changes}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Reset all user trade activity and assign ICTP balance for users registered on or after 2025-12-14';

    /**
     * Execute the console command.
     *
     * Per-user end state after reset:
     *  - balance       = max(0, kycBonus - approvedWithdrawals)
     *  - loyalty_bonus  = re-derived from transaction log (source of truth)
     *  - ictp_balance   = 50.00 (house trading capital, KYC-verified users only)
     *  - rebates        = 0 (all trade-derived referrer rebates wiped)
     *  - ai_bonus       = 0
     *  - store_bonus    = 0
     *  - ictp_processed = timestamp (prevents re-processing)
     *
     * What gets deleted per user:
     *  - All sells (any type/status)
     *  - All ownerships
     *  - All bids (any type/status)
     *  - All investments marked as settled (audit history preserved, not deleted)
     */
    public function handle(): void
    {
        $cutoff = Carbon::parse('2025-12-14')->startOfDay();

        // =====================================================================
        // BUSINESS PARAMETERS
        // Adjust these values if the client changes the ICTP terms.
        // kycBonus: amount entitled to KYC-verified users in main balance.
        //           Currently 0.00 -- meaning all users get balance = 0 after
        //           reset regardless of KYC status. If this changes to e.g.
        //           10.00, the withdrawal deduction logic below becomes active.
        // ictpBalance: house trading capital assigned to each user.
        // =====================================================================
        $kycBonus     = 0.00;
        $ictpBalance  = 50.00;
        $chunkSize    = (int) $this->option('chunk');
        $dryRun       = $this->option('dry-run');

        // ── COUNT ────────────────────────────────────────────────────────────
        $total = User::where('created_at', '>=', $cutoff)
            ->whereNull('ictp_processed')
            ->count();

        if ($total === 0) {
            $this->info('No unprocessed users found. Nothing to do.');
            Log::info('[ICTP Reset] No unprocessed users found.');
            return;
        }

        $this->info("Found {$total} users to process in chunks of {$chunkSize}.");

        if ($dryRun) {
            $this->warn('DRY RUN -- no changes will be made.');
            $this->previewCounts($cutoff);
            return;
        }

        // ── SAFETY CONFIRMATION ──────────────────────────────────────────────
        if (!$this->confirm("This will reset trade data for {$total} users. Continue?")) {
            $this->info('Aborted.');
            return;
        }

        // ── PROCESS IN CHUNKS ────────────────────────────────────────────────
        $processed  = 0;
        $failed     = 0;
        $startTime  = microtime(true);

        Log::info("[ICTP Reset] Starting. {$total} users, chunk size {$chunkSize}.");

        // chunkById uses WHERE id > lastId (cursor pagination).
        // No offset recalculation, no infinite loops, scales to millions.
        User::where('created_at', '>=', $cutoff)
            ->whereNull('ictp_processed')
            ->select('id', 'kyc_status')
            ->chunkById($chunkSize, function ($users) use (
                &$processed, &$failed, $total, $kycBonus, $ictpBalance, $startTime
            ) {
                $ids = $users->pluck('id')->toArray();

                if (empty($ids)) {
                    return;
                }

                try {
                    DB::transaction(function () use ($ids, $kycBonus, $ictpBalance) {
                        $this->deleteTradeData($ids);
                        $this->settleInvestments($ids);
                        $this->resetUserBalances($ids, $kycBonus, $ictpBalance);
                    });

                    $processed += count($ids);
                } catch (\Throwable $e) {
                    $failed += count($ids);
                    Log::error("[ICTP Reset] Chunk failed for user IDs {$ids[0]}-{$ids[count($ids)-1]}: {$e->getMessage()}");
                    $this->error("Chunk failed (IDs {$ids[0]}-{$ids[count($ids)-1]}): {$e->getMessage()}");
                }

                $elapsed = round(microtime(true) - $startTime, 1);
                $this->line("  Processed {$processed}/{$total} | Failed {$failed} | {$elapsed}s elapsed");
            });

        // ── SUMMARY ──────────────────────────────────────────────────────────
        $elapsed = round(microtime(true) - $startTime, 2);

        $summary = "[ICTP Reset] Complete. Processed: {$processed}, Failed: {$failed}, Time: {$elapsed}s";
        Log::info($summary);
        $this->info('');
        $this->info($summary);

        if ($failed > 0) {
            $this->warn("Re-run the command to retry failed users (they remain unprocessed).");
        }
    }

    /**
     * Bulk delete all trade data for a batch of user IDs.
     * Order matters: sells reference ownerships via ownership_id.
     *
     * @param array $ids Integer array of user IDs
     */
    private function deleteTradeData(array $ids): void
    {
        // Sells first (FK reference to ownerships)
        Sell::whereIn('user_id', $ids)->delete();

        // Ownerships second
        Ownership::whereIn('user_id', $ids)->delete();

        // Bids last (all types: manual, ai, etc.)
        Bid::whereIn('user_id', $ids)->delete();
    }

    /**
     * Bulk settle all investments/AI bots for a batch of user IDs.
     * Zeroes balances and marks as settled so the scheduler ignores them.
     * Records are NOT deleted -- audit history is preserved.
     *
     * @param array $ids Integer array of user IDs
     */
    private function settleInvestments(array $ids): void
    {
        Investment::whereIn('user_id', $ids)->update([
            'balance'    => 0,
            'pfit'       => 0,
            'f_pfit'     => 0,
            'moved'      => 0,
            'status'     => 3,   // completed
            'is_settled' => 1,   // scheduler skips these
            'is_paused'  => 0,
            'funded'     => 0,
        ]);
    }

    /**
     * Bulk reset user balances using a single UPDATE with LEFT JOINs.
     *
     * Instead of N individual queries per user to calculate withdrawals and
     * loyalty bonuses, this runs two aggregate subqueries and applies the
     * results in one UPDATE statement per chunk.
     *
     * Original approach: 8 queries x 500 users = 4,000 queries per chunk.
     * This approach: 1 query per chunk.
     *
     * IDs are guaranteed integers from User::pluck('id') -- safe for
     * direct interpolation. Parameterized IN() with variable-length lists
     * is not supported natively by PDO without building placeholder strings,
     * which adds complexity with no security benefit when the source is a
     * primary key column.
     *
     * @param array $ids        Integer array of user IDs
     * @param float $kycBonus   Balance entitled to KYC-verified users
     * @param float $ictpBalance ICTP house trading capital to assign
     */
    private function resetUserBalances(array $ids, float $kycBonus, float $ictpBalance): void
    {
        // Build safe ID list (cast to int as defense-in-depth)
        $idList = implode(',', array_map('intval', $ids));

        DB::statement("
            UPDATE users u

            LEFT JOIN (
                SELECT user_id, SUM(amount + charge) AS total_withdrawn
                FROM withdrawals
                WHERE type = 'main'
                  AND status = 2
                  AND user_id IN ({$idList})
                GROUP BY user_id
            ) w ON u.id = w.user_id

            LEFT JOIN (
                SELECT user_id, SUM(amount) AS total_loyalty
                FROM transactions
                WHERE type = 'loyalty_bonus'
                  AND status = 1
                  AND user_id IN ({$idList})
                GROUP BY user_id
            ) t ON u.id = t.user_id

            SET
                u.balance        = GREATEST(0,
                                     CASE WHEN u.kyc_status = 1 THEN {$kycBonus} ELSE 0.00 END
                                     - COALESCE(w.total_withdrawn, 0)
                                   ),
                u.loyalty_bonus  = COALESCE(t.total_loyalty, 0),
                u.ictp_balance   = CASE WHEN u.kyc_status = 1 THEN {$ictpBalance} ELSE 0 END,
                u.rebates        = 0,
                u.ai_bonus       = 0,
                u.store_bonus    = 0,
                u.ictp_processed = NOW()

            WHERE u.id IN ({$idList})
        ");
    }

    /**
     * Dry-run preview: show how much data would be affected.
     *
     * @param Carbon $cutoff Registration date cutoff
     */
    private function previewCounts(Carbon $cutoff): void
    {
        $userIds = User::where('created_at', '>=', $cutoff)
            ->whereNull('ictp_processed')
            ->pluck('id');

        if ($userIds->isEmpty()) {
            return;
        }

        // Sample first 10,000 IDs for count estimates (avoid full table scans on massive datasets)
        $sampleIds = $userIds->take(10000)->toArray();
        $idList    = implode(',', array_map('intval', $sampleIds));
        $isSample  = $userIds->count() > 10000;
        $label     = $isSample ? '(sampled from first 10,000 users)' : '';

        $sells       = DB::selectOne("SELECT COUNT(*) AS cnt FROM sells WHERE user_id IN ({$idList})")->cnt;
        $ownerships  = DB::selectOne("SELECT COUNT(*) AS cnt FROM ownerships WHERE user_id IN ({$idList})")->cnt;
        $bids        = DB::selectOne("SELECT COUNT(*) AS cnt FROM bids WHERE user_id IN ({$idList})")->cnt;
        $investments = DB::selectOne("SELECT COUNT(*) AS cnt FROM investments WHERE user_id IN ({$idList})")->cnt;

        $this->info('');
        $this->info("Data that would be affected {$label}:");
        $this->info("  Sells:       {$sells} (delete)");
        $this->info("  Ownerships:  {$ownerships} (delete)");
        $this->info("  Bids:        {$bids} (delete)");
        $this->info("  Investments: {$investments} (settle, not delete)");
    }
}
