<?php

namespace Modules\MedBilling\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Modules\MedBilling\Entities\MedbillClaim;

/**
 * Reports (SIFA P4-T1) — plain aggregate queries rendered as tables:
 *   - billed vs collected (from the ERP transactions the module created)
 *   - claims by status
 *   - payer aging (outstanding payer_share by payer, bucketed by claim age)
 * Guarded by medbill.reports.view.
 */
class ReportController extends MedBillingBaseController
{
    public function index()
    {
        $this->guard('medbill.reports.view');

        return view('medbilling::reports.index', [
            'billedVsCollected' => $this->billedVsCollected(),
            'claimsByStatus'    => $this->claimsByStatus(),
            'payerAging'        => $this->payerAging(),
        ]);
    }

    /**
     * Billed vs collected for the ERP sells this module created (i.e. sells that
     * have at least one medbill_charge_item). Collected reads the ERP
     * transaction_payments so it reflects real payments.
     *
     * @return array{billed:float, collected:float, outstanding:float, invoices:int}
     */
    private function billedVsCollected(): array
    {
        $businessId = $this->businessId();

        $txnIds = DB::table('medbill_charge_items')
            ->where('business_id', $businessId)
            ->whereNotNull('transaction_id')
            ->distinct()
            ->pluck('transaction_id');

        if ($txnIds->isEmpty()) {
            return ['billed' => 0.0, 'collected' => 0.0, 'outstanding' => 0.0, 'invoices' => 0];
        }

        $billed = (float) DB::table('transactions')
            ->whereIn('id', $txnIds)
            ->where('business_id', $businessId)
            ->sum('final_total');

        $collected = 0.0;
        if (Schema::hasTable('transaction_payments')) {
            $collected = (float) DB::table('transaction_payments')
                ->whereIn('transaction_id', $txnIds)
                ->sum(DB::raw('IF(is_return = 0, amount, amount * -1)'));
        }

        return [
            'billed'      => round($billed, 2),
            'collected'   => round($collected, 2),
            'outstanding' => round($billed - $collected, 2),
            'invoices'    => $txnIds->count(),
        ];
    }

    /** Claim count + claimed/approved totals grouped by status. */
    private function claimsByStatus()
    {
        return MedbillClaim::forBusiness($this->businessId())
            ->select('status',
                DB::raw('COUNT(*) as cnt'),
                DB::raw('SUM(total_claimed) as claimed'),
                DB::raw('SUM(COALESCE(total_approved, 0)) as approved'),
                DB::raw('SUM(payer_share) as payer_share'))
            ->groupBy('status')
            ->get();
    }

    /**
     * Payer aging: outstanding payer_share per payer for claims not yet paid,
     * bucketed by claim age (0-30 / 31-60 / 61-90 / 90+ days).
     */
    private function payerAging()
    {
        $rows = MedbillClaim::forBusiness($this->businessId())
            ->leftJoin('medbill_payers', 'medbill_claims.payer_id', '=', 'medbill_payers.id')
            ->whereNotIn('medbill_claims.status', ['paid', 'draft'])
            ->select([
                'medbill_payers.name as payer_name',
                'medbill_claims.payer_share',
                'medbill_claims.created_at',
            ])
            ->get();

        $buckets = [];
        foreach ($rows as $r) {
            $name = $r->payer_name ?: '—';
            $buckets[$name] ??= ['b0_30' => 0.0, 'b31_60' => 0.0, 'b61_90' => 0.0, 'b90p' => 0.0, 'total' => 0.0];

            $ageDays = $r->created_at ? now()->diffInDays($r->created_at) : 0;
            $share = (float) $r->payer_share;

            if ($ageDays <= 30) {
                $buckets[$name]['b0_30'] += $share;
            } elseif ($ageDays <= 60) {
                $buckets[$name]['b31_60'] += $share;
            } elseif ($ageDays <= 90) {
                $buckets[$name]['b61_90'] += $share;
            } else {
                $buckets[$name]['b90p'] += $share;
            }
            $buckets[$name]['total'] += $share;
        }

        return $buckets;
    }
}
