<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Wallet;
use App\Models\Payment;
use Carbon\Carbon;
use App\Models\Client;
use DB;



class FinanceController extends Controller
{
    public function calculateTotalPages($total_size, $pageSize) {
        return ceil($total_size / $pageSize);
    }

    public function index($page_number)
    {
        $page_size = 30;
        $total_payments = DB::table('payments')->count();
        $total_pages = $this->calculateTotalPages($total_payments, $page_size);
        $offset = ($page_number - 1) * $page_size;

        $payments = Payment::orderBy('created_at', 'desc')
            ->skip($offset)
            ->take($page_size)
            ->get();
        
        //the payment has a wallet_id, so we can use the wallet_id to get the wallet details and the wallet has a client_id, so we can use the client_id to get the client details

        // return response()->json($this->getDetails($payments[0])->original);
        //apply getDetails method to each payment
        foreach ($payments as $payment) {
            // $details = $this->getDetails($payment)->original;
            // var_dump($details); 
            // $payment->client_name = $details['client']['account_username'];

            $payment->client_name = $this->getDetails($payment)->original['client']['account_username'];
        }

        // return response()->json(['payments' => $payments]);

        return view('finance', ['page_number' => $page_number,
            'total_pages' => $total_pages,'payments' => $payments]);
    }

    public function getDetails($payment)
    {
        // Get the wallet details using the wallet_id from the payment
        // $wallet = Wallet::find($payment->wallet_id);

        // if (!$wallet) {
        //     return response()->json(['message' => 'Wallet not found'], 404);
        // }

        // Get the client details using the client_id from the wallet
        $client = Client::find($payment->client_id);

        // if (!$client) {
        //     return response()->json(['message' => 'Client not found'], 404);
        // }

        // Return the wallet and client details
        return response()->json(['client' => $client]);
    }
   
    public function searchFinance(Request $request)
    {
        $search = $request->input('search');
        $start_payment_date = $request->input('start_payment_date');
        $end_payment_date = $request->input('end_payment_date');
        // Use the $search variable to filter your data

        // return response()->json(['search' => $search, 'payment_date' => $payment_date]);

        // $payments = Payment::where('amount', 'like', '%' . $search . '%')
        //     ->orWhere('payment_method', 'like', '%' . $search . '%')
        //     ->get();

        // join the clients table to get the client details
        // dd($payment_date);

        if($start_payment_date && $end_payment_date){
            
            $start_date = $start_payment_date . ' 00:00:00';
            $end_date = $end_payment_date . ' 23:59:59';


            $query = "SELECT payments.*, clients.* 
                    FROM payments 
                    JOIN clients ON payments.client_id = clients.id 
                    WHERE 1=1";

            if ($start_payment_date && $end_payment_date) {
                $query .= " AND payments.payment_date BETWEEN '$start_date' AND '$end_date'";
            }

            // return response()->json(['query' => $query]);

            if ($search) {
                $query .= " AND (clients.account_username LIKE '%$search%' 
                                OR payments.amount LIKE '%$search%')";
            }

            $payments = DB::select(DB::raw($query));

            
        }else{
            $payments = Payment::join('clients', 'payments.client_id', '=', 'clients.id')
                ->where('clients.account_username', 'like', '%' . $search . '%')
                ->orWhere('payments.amount', 'like', '%' . $search . '%')
                ->get();
        }
        // $start_date = $payment_date . ' 00:00:00';
        // $end_date = $payment_date . ' 23:59:59';
        // $payments = Payment::join('clients', 'payments.client_id', '=', 'clients.id')
        //     ->where('payments.payment_date', '>=', $start_date)->where('payments.payment_date', '<=', $end_date)
        //     ->where('clients.account_username', 'like', '%' . $search . '%')
        //     ->where('payments.amount', 'like', '%' . $search . '%')
        //     ->get();

        return response()->json(['payments' => $payments]);
    }

    public function financialSummary(){
        return view('financialsummary');
    }

    public function financialSummaryPost(){
        $start_date = \Request::get('start_date');
        $end_date = \Request::get('end_date');

        $summaryQuery = "SELECT SUM(amount) as total_amount, COUNT(id) as total_payments
                        FROM payments
                        WHERE payment_date BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'";

        $summary = DB::select(DB::raw($summaryQuery));

        $paymentsQuery = "SELECT payments.*, clients.account_username, clients.email
                            FROM payments
                            JOIN clients ON payments.client_id = clients.id
                            WHERE payment_date BETWEEN '$start_date 00:00:00' AND '$end_date 23:59:59'";

        $payments = DB::select(DB::raw($paymentsQuery));

        return response()->json(['summary' => $summary, 'payments' => $payments]);
    }

}