<?php

namespace App\Services;

use App\Models\Invoice;
use App\Models\Owing;
use App\Models\Payment;
use App\Models\Package;
use App\Models\CourseTemplate;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Carbon\Carbon;

class BillingService
{
    public function generateInitialInvoices($pId)
    {
        $pkg = Package::find($pId);
        $start = Carbon::parse($class->startdate)->startOfMonth();
        $duration = $template->duration;

        for ($i = 0; $i < $duration; $i++) {
            $invoiceMonth = $start->copy()->addMonths($i);
            Invoice::create([
                'title' => "Monthly Fee - {$invoiceMonth->format('F Y')}",
                'amount' => $template->monthly,
                'ref_no' => Str::uuid(),
                'method' => 'system',
                'packageid' => $packageid,
                'description' => "Auto-generated for {$invoiceMonth->format('F')}",
                'year' => $invoiceMonth->year,
                'month' => $invoiceMonth->month,
            ]);
        }
    }

    public function generateMonthlyInvoices()
    {
        $today = now();
        $pkgs = Package::where('price' <> 0)->get();

        foreach ($pkgs as $pkg) {
             $exists = Invoice::where('packageid', $pkg->id)
                ->where('month', $today->month)
                ->where('year', $today->year)
                ->exists();

            if (!$exists) {
                Invoice::create([
                    'title' => "Monthly Fee - {$today->format('F Y')}",
                    'amount' => $pkg->monthly,
                    'ref_no' => Str::uuid(),
                    'method' => 'system',
                    'packageid' => $pkg->id,
                    'description' => "Auto-generated on {$today->toDateString()}",
                    'year' => $today->year,
                    'month' => $today->month,
                ]);
            }
        }
    }

    public function flagOwingStudents()
    {
        $today = now();
        $month = $today->month;
        $year = $today->year;

        $students = DB::table('student_records')
            ->join('studentpackages', 'student_records.regnum', '=', 'studentpackages.regnum')
            ->join('packages', 'packages.id', '=', 'studentpackages.packageid')
            ->select('student_records.regnum', 'packages.id as packageid', 'packages.monthly')
            ->get();

        foreach ($students as $student) {
            $invoice = Invoice::where('packageid', $student->packid)
                ->where('month', $month)
                ->where('year', $year)
                ->first();

            $paid = Payment::where('regnum', $student->regnum)
                ->where('month', $month)
                ->where('year', $year)
                ->sum('amount');

            if ($invoice && $paid < $invoice->amount) {
                Owing::updateOrInsert([
                    'regnum' => $student->regnum,
                    'packageid' => $student->packageid,
                    'month' => $month,
                    'year' => $year,
                ]);
            }
        }
    }

    public function processPayment($regnum, $packageid, $amount)
    {
        $today = now();
        $invoice = Invoice::where('packageid', $packageid)
            ->where('month', $today->month)
            ->where('year', $today->year)
            ->first();

        if (!$invoice) return;

        $monthly = $invoice->amount;

        if ($amount >= $monthly) {
            Payment::create([
                'regnum' => $regnum,
                'packageid' => $packageid,
                'amount' => $monthly,
                'month' => $today->month,
                'year' => $today->year,
                'ref_no' => Str::uuid(),
            ]);

            $surplus = $amount - $monthly;
            if ($surplus > 0) {
                DB::table('studentbalances')->updateOrInsert(
                    ['regnum' => $regnum],
                    ['balance' => DB::raw("balance + {$surplus}")]
                );
            }

            Owing::where('regnum', $regnum)
                ->where('packageid', $packageid)
                ->where('month', $today->month)
                ->delete();

        } elseif ($amount > 0) {
            Payment::create([
                'regnum' => $regnum,
                'packageid' => $packageid,
                'amount' => $amount,
                'month' => $today->month,
                'year' => $today->year,
                'ref_no' => Str::uuid(),
            ]);

            Owing::updateOrInsert([
                'regnum' => $regnum,
                'packageid' => $packageid,
                'month' => $today->month,
                'year' => $today->year,
            ]);
        }
    }

    public function disableOwingStudents()
    {
        $owings = Owing::all();
        foreach ($owings as $ow) {
            DB::table('users')
                ->where('regnum', $ow->regnum)
                ->update(['status' => 'disabled']);
        }
    }
}
