<?php

namespace App\Http\Controllers\SuperAdmin;

use App\Http\Controllers\Controller;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;
use Inertia\Response;

class SmsController extends Controller
{
    public function index(): Response
    {
        $rows = DB::table('schools')
            ->leftJoin('sms_wallets', 'sms_wallets.school_id', '=', 'schools.id')
            ->leftJoin('sms_messages', 'sms_messages.school_id', '=', 'schools.id')
            ->select([
                'schools.name_fa',
                'sms_wallets.total_charged',
                'sms_wallets.total_used',
                'sms_wallets.balance',
                DB::raw("SUM(CASE WHEN sms_messages.created_at >= '".now()->subDays(7)->toDateTimeString()."' THEN sms_messages.cost ELSE 0 END) as last_week_cost"),
            ])
            ->groupBy('schools.id', 'schools.name_fa', 'sms_wallets.total_charged', 'sms_wallets.total_used', 'sms_wallets.balance')
            ->orderBy('schools.name_fa')
            ->limit(100)
            ->get();

        $chargeRequests = DB::table('sms_charge_requests')
            ->join('schools', 'schools.id', '=', 'sms_charge_requests.school_id')
            ->leftJoin('users as ru', 'ru.id', '=', 'sms_charge_requests.requested_by')
            ->where('sms_charge_requests.status', 'pending')
            ->orderByDesc('sms_charge_requests.created_at')
            ->limit(50)
            ->get([
                'sms_charge_requests.id',
                'schools.name_fa as school_name',
                'schools.id as school_id',
                'sms_charge_requests.sms_count',
                'sms_charge_requests.price',
                'ru.first_name as req_first',
                'ru.last_name as req_last',
                'sms_charge_requests.created_at',
            ]);

        return Inertia::render('SuperAdmin/SmsUsage', [
            'metrics' => [
                'totalCharged' => (int) DB::table('sms_wallets')->sum('total_charged'),
                'sentThisMonth' => (int) DB::table('sms_messages')->where('status', 'sent')->where('created_at', '>=', now()->startOfMonth())->sum('cost'),
                'schoolsWithSms' => (int) DB::table('sms_wallets')->where('status', 'active')->count(),
            ],
            'chart' => $rows->pluck('last_week_cost')->map(fn ($value) => (int) $value)->values(),
            'schools' => $rows->map(fn ($row): array => [
                'name' => $row->name_fa,
                'totalCharged' => (int) ($row->total_charged ?? 0),
                'totalUsed' => (int) ($row->total_used ?? 0),
                'balance' => (int) ($row->balance ?? 0),
            ])->values(),
            'chargeRequests' => $chargeRequests->map(fn ($r): array => [
                'id' => (int) $r->id,
                'schoolName' => $r->school_name,
                'schoolId' => (int) $r->school_id,
                'smsCount' => (int) $r->sms_count,
                'price' => (int) $r->price,
                'requestedBy' => trim(($r->req_first ?? '').' '.($r->req_last ?? '')) ?: '—',
                'createdAt' => $this->jalaliDate($r->created_at, true),
            ])->values(),
        ]);
    }

    public function approveChargeRequest(Request $request, int $id): RedirectResponse
    {
        $req = DB::table('sms_charge_requests')->where('id', $id)->where('status', 'pending')->first();
        abort_unless($req, 404);

        DB::transaction(function () use ($request, $req, $id): void {
            $wallet = DB::table('sms_wallets')->where('school_id', $req->school_id)->first();

            if ($wallet) {
                $balanceBefore = (int) $wallet->balance;
                $balanceAfter = $balanceBefore + $req->sms_count;

                DB::table('sms_wallets')->where('school_id', $req->school_id)->update([
                    'balance' => $balanceAfter,
                    'total_charged' => DB::raw("total_charged + {$req->sms_count}"),
                    'updated_at' => now(),
                ]);

                DB::table('sms_wallet_transactions')->insert([
                    'school_id' => $req->school_id,
                    'type' => 'charge',
                    'amount' => $req->sms_count,
                    'balance_before' => $balanceBefore,
                    'balance_after' => $balanceAfter,
                    'description' => "شارژ بسته {$req->sms_count} پیامکی توسط Super Admin",
                    'created_by' => $request->user()?->id,
                    'created_at' => now(),
                ]);
            } else {
                DB::table('sms_wallets')->insert([
                    'school_id' => $req->school_id,
                    'balance' => $req->sms_count,
                    'total_charged' => $req->sms_count,
                    'total_used' => 0,
                    'status' => 'active',
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            DB::table('sms_charge_requests')->where('id', $id)->update([
                'status' => 'approved',
                'reviewed_by' => $request->user()?->id,
                'reviewed_at' => now(),
                'updated_at' => now(),
            ]);
        });

        return back()->with('success', 'درخواست شارژ تایید شد و اعتبار به کیف پول مدرسه اضافه شد.');
    }

    public function rejectChargeRequest(Request $request, int $id): RedirectResponse
    {
        $req = DB::table('sms_charge_requests')->where('id', $id)->where('status', 'pending')->first();
        abort_unless($req, 404);

        DB::table('sms_charge_requests')->where('id', $id)->update([
            'status' => 'rejected',
            'reviewed_by' => $request->user()?->id,
            'reviewed_at' => now(),
            'updated_at' => now(),
        ]);

        return back()->with('success', 'درخواست رد شد.');
    }
}
