<?php

namespace App\Http\Controllers;

use App\Models\BillGenerate;
use Carbon\Carbon;
use App\Models\Pop;
use App\Models\Client;
use App\Models\Income;
use App\Models\Employee;
use App\Models\Reseller;
use App\Models\IncomeHead;
use App\Models\Billpayment;
use App\Models\CompanyInformation;
use App\Models\CustomerAccount;
use App\Models\ResellerUser;
use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class PaymentReportController extends Controller
{
    public function paymentReport($today = null)
    {
        if ($today != null) {
            $start = today();
            $end = today();
        } else {
            $start = today()->firstOfMonth();
            $end = today()->endOfMonth();
        }

        $areas = DB::table('clientsinfo')
            ->leftJoin('clients', 'clients.id', '=', 'clientsinfo.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->select('area')
            ->distinct()
            ->where('resellers.reseller_type', 'own')
            ->get();

        return view('paymenthistory.index', [
            'page_title'        => 'View Payment History',
            'reseller'          => Reseller::resellerList()->where('reseller_type', 'own')->get(),
            'employees'         => Employee::where('status', 'active')->get(),
            'pops'              => Pop::all(),
            'start'             => $start,
            'end'               => $end,
            'otc'               => false,
            'payment_type'  => IncomeHead::all(),
            // 'discount_amount' => Billpayment::all(),
            'users' => User::where('email', '!=', 'admin@billingfix.xyz')->where('email', '!=', 'soiket@outlook.com')
                ->where('email', '!=', 'support@yetfix.com')->where('email', '!=', 'limitedyetfix@gmail.com')->get(),
            'areas' => $areas
        ]);
    }


    public function otcPaymentReport()
    {
        $start = today()->firstOfMonth();
        $end = today()->endOfMonth();

        $areas = DB::table('clientsinfo')
            ->leftJoin('clients', 'clients.id', '=', 'clientsinfo.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->select('area')->distinct()->where('resellers.reseller_type', 'own')->get();

        return view('paymenthistory.index', [
            'page_title'        => 'View OTC Payment History',
            'reseller'          => Reseller::resellerList()->where('reseller_type', 'own')->get(),
            'employees'         => Employee::all(),
            'pops'              => Pop::all(),
            'start'             => $start,
            'end'               => $end,
            'otc'               => true,
            'payment_type'  => IncomeHead::all(),
            // 'discount_amount' => Billpayment::all(),
            'users' => User::where('email', '!=', 'admin@billingfix.xyz')->where('email', '!=', 'soiket@outlook.com')
                ->where('email', '!=', 'support@yetfix.com')->where('email', '!=', 'limitedyetfix@gmail.com')->get(),
            'areas' => $areas
        ]);
    }


    public function getPopForManager(Request $request)
    {
        if (request()->ajax()) {

            // dd($request->all());
            if ($request->id) {

                $pop_list = Pop::where('reseller_id', $request->id)->get();

                // dd($pop_list);
                if ($pop_list->first()) {
                    $options = '<option value="">All Pop</option>';
                    foreach ($pop_list as $p) {
                        $options .= '<option value="' . $p->id . '">' . $p->popname . '</option>';
                    }
                } else {
                    $options = '<option value="">No Pop/Sub-Manager Found</option>';
                }

                echo $options;
            }
        }
    }

    public function paymentReportSearch(Request $request)
    {
        if ($request->ajax()) {
            $from = $request->from_date == '' ? today() : Carbon::parse($request->from_date);
            $to   = $request->to_date == '' ? today() : Carbon::parse($request->to_date);

            $from = Carbon::parse($from)->format('Y-m-d 00:00:00');
            $to   = Carbon::parse($to)->format('Y-m-d 23:59:59');

            // Optimize: Use select to limit columns and optimize eager loading
            $result = Billpayment::select([
                'bill_payments.id',
                'bill_payments.client_id',
                'bill_payments.created_at',
                'bill_payments.paid_amount',
                'bill_payments.discount_amount',
                'bill_payments.money_receipt_number',
                'bill_payments.description',
                'bill_payments.user_id',
                'bill_payments.collected_by',
                'bill_payments.income_id',
                'bill_payments.vat_amount',
                'bill_payments.reseller_vat_percentage',
                'bill_payments.reseller_commission_percentage',
                'bill_payments.reseller_commission_amount'
            ])
            ->with([
                'clients:id,userid,pop_id,package_id,expire_date',
                'clientsinfo:client_id,clients_name,contact_no,area',
                'clients.clientsinfo:client_id,clients_name,contact_no,area',
                'clients.packages:id,package_name',
                'clients.pop:id,popname,reseller_id',
                'clients.pop.reseller:id,name',
                'user:id,name',
                'employee:id,name',
                'income:id,incomeHead',
                'income.head:id,name'
            ]);

            if ($request->user_by != "") {
                $result->whereIn('user_id', $request->user_by);
            }


            if ($request->otc) {

                $result->whereHas('income', function ($q) {
                    $q->where('incomeHead', 2);
                });

                if ($request->pop_id != '') {
                    $result->whereHas('clients.pop', function ($query) use ($request) {
                        $query->where('id', $request->pop_id);
                    });
                } else {
                    if ($request->reseller_id != '') {
                        $result->whereHas('clients.pop', function ($query) use ($request) {
                            $query->where('reseller_id', $request->reseller_id);
                        });
                    }
                }
                if ($request->area != null) {
                    $result->whereHas('clients.clientsinfo', function ($query) use ($request) {
                        $query->where('area', $request->area);
                    });
                }

                $result->whereBetween('created_at', [$from, $to])
                    ->orderBy('id', 'desc');

                // Calculate totals for all matching records (before pagination)
                $totals = (clone $result)->selectRaw('
                    SUM(paid_amount) as total_paid_amount,
                    SUM(discount_amount) as total_discount_amount
                ')->first();

                // Add pagination for OTC report
                $perPage = $request->per_page ?? 50;
                $list = $result->paginate($perPage);

                return view('paymenthistory.otcReport', [
                    'list' =>  $list,
                    'totals' => $totals
                ]);
            } elseif ($request->type == '') {

                if ($request->otc == true) {
                    $result->whereHas('income', function ($q) {
                        $q->where('incomeHead', 2);
                    });
                }


                if ($request->pop_id != '') {
                    $result->whereHas('clients.pop', function ($query) use ($request) {
                        $query->where('id', $request->pop_id);
                    });
                } else {
                    if ($request->reseller_id != '') {
                        $result->whereHas('clients.pop.reseller', function ($query) use ($request) {
                            $query->where('id', $request->reseller_id);
                        });
                    }
                }

                if ($request->collected_by != '') {
                    $result->whereHas('employee', function ($query) use ($request) {
                        $query->whereIn('id', $request->collected_by);
                    });
                }


                if ($request->payment_type != '') {
                    $result->whereHas('income.head', function ($query) use ($request) {
                        $query->where('id', $request->payment_type);
                    });
                }

                // dd('area filter');
                if ($request->area != null) {
                    $result->whereHas('clients.clientsinfo', function ($query) use ($request) {
                        $query->where('area', $request->area);
                    });
                }

                // dd(Pop::list()->toArray());

                $pop = Pop::list()->pluck('id');

                $result->whereBetween('created_at', [$from, $to])
                    ->whereIn('client_id', function ($q) use ($pop, $request) {
                        $q->select('id')
                            ->from('clients')
                            ->when($request->with_close_id != 'on', function ($q) {
                                $q->whereNull('deleted_at');
                            })
                            ->whereIn('pop_id', $pop);
                    })
                    ->orderBy('id', 'desc');

                // Calculate totals for all matching records (before pagination)
                $totals = (clone $result)->selectRaw('
                    SUM(paid_amount) as total_paid_amount,
                    SUM(discount_amount) as total_discount_amount,
                    SUM(vat_amount) as total_vat_amount,
                    SUM(reseller_commission_amount) as total_reseller_commission_amount
                ')->first();

                // Add pagination - default 50 per page, configurable
                $perPage = $request->per_page ?? 50;
                $list = $result->paginate($perPage);

                return view('paymenthistory.result', [
                    'list'          =>  $list,
                    'hideColumn'    => false,
                    'totals'        =>  $totals
                ]);
            } elseif ($request->type == 'due') {


                $result = Client::with('pop', 'clientsinfo:client_id,clients_name', 'customerAccount:client_id,dueAmount')
                    ->whereHas('pop', function ($q) {
                        return $q->where('bill_generate', 'yes');
                    })
                    ->select('userid', 'id');


                if ($request->pop_id != '') {
                    $result->whereHas('pop', function ($query) use ($request) {
                        $query->where('id', $request->pop_id);
                    });
                } else {
                    if ($request->reseller_id != '') {
                        $result->whereHas('pop.reseller', function ($query) use ($request) {
                            $query->where('id', $request->reseller_id);
                        });
                    }
                }

                if ($request->collected_by != '') {
                    $result->whereHas('employee', function ($query) use ($request) {
                        $query->whereIn('id', $request->collected_by);
                    });
                }

                if ($request->area != null) {
                    $result->whereHas('clients.clientsinfo', function ($query) use ($request) {
                        $query->where('area', $request->area);
                    });
                }

                $result = $result->get()->toArray();


                $bill = BillGenerate::selectRaw("sum(bill_amount) as total_bill,client_id")
                    ->whereBetween('created_at', [$from, $to])
                    ->groupBy('client_id')->get()->toArray();
                $pay = Billpayment::selectRaw(" (sum(paid_amount) + sum(discount_amount)) as total_pay,client_id")
                    ->whereBetween('created_at', [$from, $to])
                    ->groupBy('client_id')->get()->toArray();


                // dd($bill);

                $d = [];






                foreach ($result as $r) {



                    $d[$r['id']]['userid'] = $r['userid'];
                    $d[$r['id']]['dueAmount'] = $r['customer_account']['dueAmount'] ?? 0;


                    foreach ($bill as $b) {

                        if ($b['client_id'] == $r['id']) {
                            $d[$b['client_id']]['total_bill'] = $b['total_bill'];
                        }
                    }

                    foreach ($pay as $p) {
                        if ($p['client_id'] == $r['id']) {
                            $d[$p['client_id']]['total_paid'] = $p['total_pay'];
                        }
                    }
                }


                return view('paymenthistory.duelist', [
                    'list'          =>  $d
                ]);
            } elseif ($request->type == 'discount_amount') {



                if ($request->type == 'discount_amount') {

                    $result->where('discount_amount', '>', 0);
                }

                if ($request->pop_id != '') {
                    $result->whereHas('clients.pop', function ($query) use ($request) {
                        $query->where('id', $request->pop_id);
                    });
                } else {
                    if ($request->reseller_id != '') {
                        $result->whereHas('clients.pop.reseller', function ($query) use ($request) {
                            $query->where('id', $request->reseller_id);
                        });
                    }
                }

                if ($request->collected_by != '') {
                    $result->whereHas('employee', function ($query) use ($request) {
                        $query->whereIn('id', $request->collected_by);
                    });
                }

                if ($request->area != null) {
                    $result->whereHas('clients.clientsinfo', function ($query) use ($request) {
                        $query->where('area', $request->area);
                    });
                }

                // $result = $result;
                $result->whereBetween('created_at', [$from, $to])
                    ->orderBy('id', 'desc');

                // Calculate totals for all matching records (before pagination)
                $totals = (clone $result)->selectRaw('
                    SUM(paid_amount) as total_paid_amount,
                    SUM(discount_amount) as total_discount_amount,
                    SUM(vat_amount) as total_vat_amount,
                    SUM(reseller_commission_amount) as total_reseller_commission_amount
                ')->first();

                // Add pagination
                $perPage = $request->per_page ?? 50;
                $list = $result->paginate($perPage);

                return view('paymenthistory.result', [
                    'list'          =>  $list,
                    'hideColumn'    => false,
                    'totals'        =>  $totals
                ]);
            } elseif ($request->type == 'paid_amount') {



                if ($request->type == 'paid_amount') {

                    $result->where('paid_amount', '>', 0);
                }

                if ($request->pop_id != '') {
                    $result->whereHas('clients.pop', function ($query) use ($request) {
                        $query->where('id', $request->pop_id);
                    });
                } else {
                    if ($request->reseller_id != '') {
                        $result->whereHas('clients.pop.reseller', function ($query) use ($request) {
                            $query->where('id', $request->reseller_id);
                        });
                    }
                }

                if ($request->collected_by != '') {
                    $result->whereHas('employee', function ($query) use ($request) {
                        $query->whereIn('id', $request->collected_by);
                    });
                }

                if ($request->area != null) {
                    $result->whereHas('clients.clientsinfo', function ($query) use ($request) {
                        $query->where('area', $request->area);
                    });
                }

                // $result = $result;
                $result->whereBetween('created_at', [$from, $to])
                    ->orderBy('id', 'desc');

                // Calculate totals for all matching records (before pagination)
                $totals = (clone $result)->selectRaw('
                    SUM(paid_amount) as total_paid_amount,
                    SUM(discount_amount) as total_discount_amount,
                    SUM(vat_amount) as total_vat_amount,
                    SUM(reseller_commission_amount) as total_reseller_commission_amount
                ')->first();

                // Add pagination
                $perPage = $request->per_page ?? 50;
                $list = $result->paginate($perPage);

                return view('paymenthistory.result', [
                    'list'          =>  $list,
                    'hideColumn'    => true,
                    'totals'        =>  $totals
                ]);
            } elseif ($request->type == 'advance_adjust') {

                $result = BillGenerate::with('clients', 'clientsinfo')
                    ->whereColumn('due_amount', '<', 'bill_amount')
                    ->whereBetween('created_at', [$from, $to])
                    ->orderBy('id', 'desc');

                // Add pagination
                $perPage = $request->per_page ?? 50;
                $list = $result->paginate($perPage);

                return view('paymenthistory.advanceAdjust', [
                    'list'          =>  $list,
                ]);
            }
        }
    }



    public function moneyReceiptSearch()
    {
        return view('paymenthistory.moneyreceipt', [
            'page_title'        => 'Money Receipt Search'
        ]);
    }


    public function moneyReceiptSearchReport(Request $request)
    {
        if ($request->ajax()) {
            // dd($request->all());
            if ($request->money_receipt_number == '') {
                return '<p class="text-center">Not found...!</p>';
            }
            $result = Billpayment::with('clients', 'clients.clientsinfo', 'clients.packages', 'clients.pop', 'clients.pop.reseller', 'user', 'clients.customerAccount', 'employee', 'income', 'income.head');

            // $result = DB::table('bill_payments')
            //     ->leftJoin('clients', 'clients.id', '=', 'bill_payments.client_id')
            //     ->leftJoin('clientsinfo', 'clientsinfo.client_id', '=', 'clients.id')
            //     ->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
            //     ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            //     ->leftJoin('resellers', 'pops.reseller_id', '=', 'resellers.id')
            //     ->leftJoin('users', 'users.id', '=', 'bill_payments.user_id')
            //     ->leftJoin('customer_accounts', 'customer_accounts.client_id', '=', 'clients.id')
            //     ->leftJoin('employees', 'employees.id', '=', 'bill_payments.collected_by')
            //     ->leftJoin('incomes', 'incomes.id', '=', 'bill_payments.income_id')
            //     ->select(
            //         'clients.*',
            //         'clientsinfo.*',
            //         'clients.created_at as created_at',
            //         'bill_payments.paid_amount',
            //         'bill_payments.discount_amount',
            //         'bill_payments.money_receipt_number',
            //         'bill_payments.collected_by',
            //         'bill_payments.user_id',
            //         'bill_payments.id as payment_id',
            //         'bill_payments.created_at as payment_created_at',
            //         'pops.reseller_id as reseller_id',
            //         'resellers.name as reseller_name',
            //         'pops.popname',
            //         'customer_accounts.dueAmount',
            //         'customer_accounts.totalPaid',
            //         'customer_accounts.totalDiscount',
            //         'customer_accounts.totalBillAmount',
            //         'clientsinfo.flat_no',
            //         'clientsinfo.building_name',
            //         'clientsinfo.road_no',
            //         'clientsinfo.block_sector',
            //         'employees.name as employee_name',
            //         'users.name as auth_user_name',
            //         'packages.package_name',
            //         'pops.popname',
            //         'clientsinfo.clients_name',
            //         'clientsinfo.contact_no',
            //         'clientsinfo.father_name',
            //         'clientsinfo.mother_name',
            //         'clientsinfo.area',
            //         'clientsinfo.email',
            //         'clientsinfo.national_id',
            //         'packages.package_rate',
            //         'incomes.name as incomeHead'
            //     )
            //     ->orderBy('bill_payments.id', 'desc');

            $result->where('money_receipt_number', 'LIKE', "%$request->money_receipt_number%");


            return view('paymenthistory.result', [
                'list'       =>  $result->get(),
                'hideColumn'    => false
            ]);
        }
    }

    public function billPaymentReport()
    {
        $start = today()->firstOfMonth();
        $end = today();
        $reseller = Reseller::resellerList()->get();

        return view('billPaymentHistory.index', [
            'page_title'        => 'View Bill and Payment History',
            'reseller'          => $reseller,
            'start'             => $start,
            'end'               => $end,
        ]);
    }

    public function billPaymentReportSearch(Request $request)
    {
        if ($request->ajax()) {

            $from = $request->from_date == '' ? today() : Carbon::parse($request->from_date)->format('Y-m-d 00:00:00');
            $to   = $request->to_date == '' ? today() : Carbon::parse($request->to_date)->format('Y-m-d 23:59:59');

            if ($request->with_zero_payment != 'on') {

                $list = Client::with('clientsinfo', 'pop', 'pop.reseller', 'generatedBill', 'paymentBill', 'customerAccount');

                if ($request->from_date != '' && $request->to_date != '') {
                    $list->whereHas('generatedBill', function ($query) use ($from, $to) {
                        $query->whereBetween('created_at', [$from, $to]);
                    });
                }

                if ($request->pop_id != null) {
                    $list->where('pop_id', $request->pop_id);
                } else {
                    if ($request->reseller_id != null) {
                        $list->whereHas('pop.reseller', function ($query) use ($request) {
                            $query->where('id', $request->reseller_id);
                        });
                    }
                }

                if ($request->with_close_id == 'on') {
                    $list->withTrashed()->orderBy('id', 'desc');
                } else {
                    $list->orderBy('id', 'desc');
                }

                $type = '';

                return view('billPaymentHistory.result', [
                    'list'          =>  $list->get(),
                    'type'          =>  $type
                ]);
            } else {



                $data = Client::with(['clientsinfo', 'pop', 'pop.reseller', 'generatedBill', 'paymentBill', 'customerAccount'])
                    ->whereDoesntHave('paymentBill', function ($query) use ($from, $to) {
                        $query->whereBetween('created_at', [$from, $to]);
                    })
                    ->whereHas('pop', function ($query) {
                        $query->where('bill_generate', 'yes');
                    });

                // dd($data->get());

                $type = 'Zero Payment';

                if ($request->reseller_id != null) {
                    if ($request->pop_id == null) {
                        $list = $data->whereHas('pop.reseller', function ($query) use ($request) {
                            $query->where('id', $request->reseller_id);
                        });
                    } else {
                        $list = $data->where('pop_id', $request->pop_id);
                    }
                } else {
                    $list = $data;
                }

                return view('billPaymentHistory.result', [
                    'list'          =>  $list->get(),
                    'type'          =>  $type
                ]);
            }
        }
    }

    public function paymentInvoicePrint()
    {
        return view('paymenthistory.paymentSearch', [
            'page_title'        => 'Payment Invoice Print'
        ]);
    }

    public function paymentInvoiceSearch(Request $request)
    {
        if ($request->ajax()) {
            // dd($request->all());

            $client = Client::where('userid', $request->username)->first();

            if ($client != null) {

                $result = Billpayment::with('clients', 'clients.clientsinfo', 'clients.packages', 'clients.pop', 'clients.pop.reseller', 'user', 'clients.customerAccount', 'employee', 'income', 'income.head');
                $result->where('client_id', $client->id);

                return view('paymenthistory.invoiceResult', [
                    'list'       =>  $result->get(),
                ]);
            } else {
                return '<p class="text-center" style="color:red">Not Result Found...!</p>';
            }
        }
    }

    public function invoicePrint($id)
    {
        $billpayment = Billpayment::with('clients', 'clientsinfo', 'employee', 'clients.customerAccount', 'income')->find($id);

        $data = [
            'billpayment'  => $billpayment,
            'company_info' => CompanyInformation::latest()->first(),
        ];
        // dd($data);
        return view('paymenthistory.payInvoice', $data);
    }
}
