<?php

namespace App\Http\Controllers;

use App\Models\Premium_payment;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;

class PaymentReportController extends Controller
{
    public function showUserPaymentsForm()
    {
        $users = User::orderBy('name')->get();
        $payments = []; // empty by default

        return view('reports.user_payments_report', compact('users', 'payments'));
    }

    public function generateUserPaymentsReport(Request $request)
    {
        // Inputs with defaults
        $date1 = $request->date1;
        $date2 = $request->date2;
        $time1 = $request->time1 ?? '00:00';
        $time2 = $request->time2 ?? '23:59';

        // Combine to full datetime range
        $start = $date1 . ' ' . $time1 . ':00';
        $end = $date2 . ' ' . $time2 . ':59';

        $userId = $request->user_id;

        // ✅ Query joins: include payment_modes join!
        $query = DB::table('premium_payments')
            ->join('users', 'users.id', '=', 'premium_payments.user_id')
            ->join('members', 'members.id', '=', 'premium_payments.member_id')
            ->leftJoin('payment_modes', 'payment_modes.id', '=', 'premium_payments.payment_method') // ✅ Correct join
            ->whereBetween('premium_payments.payment_date', [$start, $end]);

        if ($userId !== 'all') {
            $query->where('premium_payments.user_id', $userId);
        }

        $payments = $query
            ->select(
                'premium_payments.*',
                'users.name as user_name',
                'members.name as member_name',
                'members.national_id as scheme',
                'members.surname as member_surname',
                'payment_modes.name as payment_mode_name' // ✅ Now pulls mode name!
            )
            ->orderBy('premium_payments.payment_date', 'desc')
            ->get();

        $total = $payments->sum('amount_paid');
        $users = User::orderBy('name')->get();

        return view('reports.user_payments_report', compact(
            'payments',
            'total',
            'date1',
            'date2',
            'time1',
            'time2',
            'users',
            'userId'
        ));
    }

    
    public function paymentStats(Request $request)
    {
        // ✅ Get all users for the dropdown
        $users = User::all();

        // ✅ Get the selected user (if any), default to logged-in user
        $selectedUser = $request->user_id ?? Auth::id();

        // ✅ Get the selected date range (default: today)
        $startDate = $request->date1 ?? Carbon::today()->toDateString();
        $endDate = $request->date2 ?? Carbon::today()->toDateString();

        // ✅ Get payments based on the user and date range
        $paymentsQuery = Premium_payment::where('user_id', $selectedUser)
            ->whereBetween('payment_date', [$startDate, $endDate]);

        $payments = $paymentsQuery->get();

        // ✅ Calculate statistics dynamically
        $totalPayments = $payments->count();
        $totalAmount = $payments->sum('amount_paid');

        // ✅ Cash Breakdown
        $cashCollected = $payments->where('payment_method', 1)->where('cash', 1)->sum('amount_paid');
        $cashNotCollected = $payments->where('payment_method', 1)->where('cash', 0)->sum('amount_paid');
        $totalCashTransactions = $payments->where('payment_method', 1)->where('cash', 1)->count();

        // ✅ MoMo Breakdown
        $momoCollected = $payments->where('payment_method', 2)->sum('amount_paid');
        $totalMomoTransactions = $payments->where('payment_method', 2)->count();

        // ✅ EFT Breakdown
        $eftCollected = $payments->where('payment_method', 3)->sum('amount_paid');
        $totalEftTransactions = $payments->where('payment_method', 3)->count();

        // ✅ Pending Payments
        $pendingPayments = $payments->where('status', 0)->sum('amount_paid');

        // ✅ Get Payments List from the Database
        $paymentList = DB::select("
        SELECT 
            premium_payments.id, 
            members.national_id, 
            members.surname, 
            members.name, 
            (premium_payments.amount_paid / premium_payments.months) as pay_amount, 
            months_covereds.months, 
            covers.name as cover_name, 
            payment_modes.name as mop, 
            premium_payments.created_at 
        FROM members 
        INNER JOIN premium_payments ON premium_payments.member_id = members.id 
        INNER JOIN months_covereds ON months_covereds.payment_id = premium_payments.id 
        INNER JOIN premia ON premia.member_id = members.id 
        INNER JOIN covers ON covers.id = premia.cover_id 
        INNER JOIN payment_modes ON payment_modes.id = premium_payments.payment_method 
        WHERE premium_payments.user_id = ? 
        AND (premium_payments.payment_date BETWEEN ? AND ?) 
        ORDER BY premium_payments.id ASC
    ", [$selectedUser, $startDate, $endDate]);

        return view('admin.payment-stats', compact(
            'users',
            'selectedUser',
            'startDate',
            'endDate',
            'totalPayments',
            'totalAmount',
            'cashCollected',
            'cashNotCollected',
            'momoCollected',
            'eftCollected',
            'totalCashTransactions',
            'totalMomoTransactions',
            'totalEftTransactions',
            'pendingPayments',
            'paymentList'
        ));
    }

}
