<?php

namespace ClassyPOS\Http\Controllers;

use Illuminate\Http\Request;
use ClassyPOS\sales\Invoice;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class dailyReportController extends Controller
{
  public function index(Request $request, $date, $shopID)
  {

    $shopID = intval($shopID);

    $invoices = Invoice::select('InvoiceID', 'ReturnedMoney', 'SubTotal', 'created_at', 'ShopID', 'IsRefunded', 'Discount')
      ->where('ShopID', '=', $shopID)
      ->whereBetween('created_at', [$date, date('Y-m-d', strtotime($date . '+1 day'))])
      ->get();

    $refunds = DB::select("SELECT InvoiceID, TotalPrice, created_at, RefundShopID, Discount, Qty
            FROM invoice_product_refund_mapping
            WHERE RefundShopID = $shopID
            AND created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY);");

    $cards = DB::select("SELECT cpt.TransactionAmount, inv.InvoiceID, inv.ShopID
            FROM customer_payment_transaction AS cpt, invoice AS inv
            WHERE inv.ShopID = $shopID
            AND inv.created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY)
            AND inv.InvoiceID = cpt.InvoiceID
            AND cpt.MethodID <> 0;");

    $banks = DB::select("SELECT bank_ledger.Deposit , bank_ledger.Withdraw FROM bank_ledger
            WHERE ShopID = $shopID
            AND created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY)
            AND bank_ledger.Type IN ('SelfDeposit', 'SelfWithdraw');");


    $expenses = DB::select("SELECT expense_category.CategoryName, expense.Amount, Notes FROM expense, expense_category
            WHERE expense.ShopID = $shopID
            AND expense.created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY) AND expense.CategoryID = expense_category.CategoryID;");


    $qtys = DB::select("SELECT ProductID, Qty, created_at FROM invoice_product_mapping
            WHERE SellingShopID = $shopID
            AND created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY);");


    $purchases = DB::select("SELECT vendor.VendorName, purchase_invoice.TotalPrice FROM purchase_invoice, vendor
            WHERE purchase_invoice.ShopID = $shopID
            AND purchase_invoice.created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY) AND vendor.VendorID = purchase_invoice.VendorID;");

    $vendorPayments = DB::select("SELECT vendor.VendorName, vendor_ledger.Debit FROM vendor_ledger, vendor
    WHERE vendor_ledger.ShopID = $shopID
    AND vendor_ledger.created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY) AND vendor.VendorID = vendor_ledger.VendorID;");

   $duePays = DB::select("
    SELECT customer_ledger.Debit, customer.FirstName, customer_ledger.Type, bank.BankName
    FROM customer
    INNER JOIN customer_ledger ON customer.CustomerID = customer_ledger.CustomerID
    LEFT JOIN bank_ledger ON bank_ledger.LedgerID = customer_ledger.BankLedgerID
    LEFT JOIN bank ON bank_ledger.BankID = bank.BankID
    WHERE customer.ShopID = ?
    AND customer_ledger.created_at BETWEEN ? AND DATE_ADD(?, INTERVAL 1 DAY)
    AND customer_ledger.Type IN ('Payment', 'BankPayment')
", [$shopID, $date, $date]);


    $vendorPayBank = DB::select("SELECT Withdraw FROM bank_ledger
    WHERE bank_ledger.ShopID = $shopID
    AND bank_ledger.created_at BETWEEN '$date' AND DATE_ADD('$date', INTERVAL 1 DAY) AND bank_ledger.Type = 'VendorBankWithdraw'
;");


    $shopName = DB::select("SELECT ShopName FROM shop WHERE shopID = $shopID");
    
    

$date = Carbon::parse($date); // Parse the input date
$nextDate = $date->copy()->addDay(); // Add 1 day to $date

$methods = DB::table('invoice')
    ->leftJoin('customer_payment_transaction', 'customer_payment_transaction.InvoiceID', '=', 'invoice.InvoiceID')
    ->leftJoin('payment_method', 'payment_method.ID', '=', 'customer_payment_transaction.MethodID')
    ->select(
        'payment_method.MethodName',
        'customer_payment_transaction.MethodID',
        DB::raw('SUM(customer_payment_transaction.TransactionAmount) as TotalSum')
    )
    ->where('invoice.ShopID', '=', $shopID)
    ->where('customer_payment_transaction.MethodID', '<>', 0)
    ->whereBetween('invoice.created_at', [$date->toDateString(), $nextDate->toDateString()])
    ->groupBy('customer_payment_transaction.MethodID', 'payment_method.MethodName') // Group by payment method
    ->get();
    
    
    $salary = DB::table('employee_ledger_new')->join('employee','employee.EmployeeID','employee_ledger_new.EmployeeID')
    ->where('type','<>','return')
    ->where('employee.ShopID', '=', $shopID)
    ->whereBetween('employee_ledger_new.created_at',[$date->toDateString(), $nextDate->toDateString()])
    ->select('employee_ledger_new.*','employee.Name')->get();




    $totalSale = 0;
    $totalDiscountCashSale = 0;
    $totalDiscountDueSale = 0;
    $totalDiscount = 0;
    $totalRefund = 0;
    $totalRefundCash = 0;
    $totalRefundDue = 0;
    $totalVendorPays = 0;
    $totalDue = 0;
    $totalCardPay = 0;
    $totalBankDeposite = 0;
    $totalBankWithdraw = 0;
    $totalExpense = 0;
    $totalDuePayCash = 0;
    $totalDuePayBank = 0;
    $totalQtys = 0;
    $totalItems = [];
    $totalRefundItems = 0;
    $DueCustomers = [];
    $totalPurchaseInvoices = count($purchases);
    $totalPurchase = 0;
    $totalvp = 0;
    
    $totalSalary = 0;


    $totalInvoices = count($invoices);


    foreach ($invoices as $invoice) {
      $totalSale += $invoice->SubTotal;
      if($invoice->ReturnedMoney >= 0){
        $totalDiscountCashSale += $invoice->Discount;
      }
      else{
        $totalDiscountDueSale += $invoice->Discount;
      }
      $totalDiscount = $totalDiscountCashSale + $totalDiscountDueSale;
      if ($invoice->ReturnedMoney < 0) {
        array_push($DueCustomers, [$invoice->InvoiceID, abs($invoice->ReturnedMoney)]);
        $totalDue += abs($invoice->ReturnedMoney);
      }
    }

    foreach ($DueCustomers as &$dc) {
      $v = DB::select("SELECT CustomerID FROM customer_invoice_mapping WHERE InvoiceID = ?", [$dc[0]]);

      if (!empty($v)) {
        $customer = DB::select("SELECT FirstName FROM customer WHERE CustomerID = ?", [$v[0]->CustomerID]);

        if (!empty($customer)) {
          $dc[] = $customer[0]->FirstName;
        }
      }
    }


    foreach ($refunds as $refund) {
      $res = DB::select("SELECT ReturnedMoney FROM invoice WHERE InvoiceID = ?",[$refund->InvoiceID]);

      if($res[0]->ReturnedMoney < 0){
        $totalRefundDue += ($refund->TotalPrice - $refund->Discount);
      } 

      if($res[0]->ReturnedMoney >= 0){
        $totalRefundCash += ($refund->TotalPrice - $refund->Discount);
      }

      $totalRefund = $totalRefundCash + $totalRefundDue;
      $totalRefundItems += $refund->Qty;
    }

    foreach ($vendorPayments as $vendorpay) {
      $totalVendorPays += $vendorpay->Debit;
    }

    foreach ($cards as $card) {
      $totalCardPay += $card->TransactionAmount;
    }

    foreach ($banks as $bank) {
      $totalBankDeposite += $bank->Deposit;
      $totalBankWithdraw += $bank->Withdraw;
    }

    foreach ($expenses as $expense) {
      $totalExpense += $expense->Amount;
    }

    foreach ($duePays as $duePay) {
      if ($duePay->Type === "Payment") {
        $totalDuePayCash += $duePay->Debit;
      }

      if ($duePay->Type === "BankPayment") {
        $totalDuePayBank += $duePay->Debit;
      }
    }

    foreach($vendorPayBank as $vp){
      $totalvp += $vp->Withdraw;
    }

    foreach ($qtys as $qty) {
      $totalQtys += $qty->Qty;

      if (!in_array($qty->ProductID, $totalItems)) {
        array_push($totalItems, $qty->ProductID);
      }
    }

    foreach ($purchases as $purchase) {
      $totalPurchase += $purchase->TotalPrice;
    }
    
    
    foreach($salary as $a){
        $totalSalary += $a->Amount;
    }
    
    

    $totalCat = count($totalItems);


    

    $totalSales = $totalSale - ( $totalDiscount + $totalRefund);
    // $totalSale = $totalSale - ( $totalDiscount + $totalRefund);

    $totalDuePay = $totalDuePayCash + $totalDuePayBank;

    $cashTender = $totalSale - $totalCardPay;
    $cashTenders = $cashTender-( $totalDiscount + $totalRefund);

    $cashVP = $totalVendorPays - $totalvp;

    $totalCashOut = ($totalDiscountCashSale + $totalRefundCash + $totalVendorPays + $totalDue  + $totalBankDeposite + $totalExpense + $totalSalary);

    // $cash = $totalSale - $totalCashOut;
    $cash = $cashTender - $totalCashOut;
    $totalDue = $totalDue - $totalRefundDue;

    $finalCash = $cash + $totalDuePayCash + $totalBankWithdraw;

    $report = [
      'Total Sale' => $totalSales,
      'Card Payment' => $totalCardPay,
      'Total Discount' => $totalDiscount,
      'Total Refund' => $totalRefund,
      'Total Vendor Payment' => $totalVendorPays,
      'Total Customer Due' => $totalDue,
      'Total Bank Deposite' => $totalBankDeposite,
      'Total Expense' => $totalExpense,
      'Total Cash Reduce from sale' => $totalCashOut,
      'Remainning Cash' => $cash,
      'Total Customer Due Payment' => $totalDuePay,
      'By bank' => $totalDuePayBank,
      'By cash' => $totalDuePayCash,
      'Total Bank Withdraw' => $totalBankWithdraw,
      'Final Cash' => $finalCash,
      'purchase' => $totalPurchase,
      'purchaseInvoice' => $totalPurchaseInvoices,
      'Total Vendor Payement Bank'=>$totalvp,
      'Total Vendor Payement Cash'=>$cashVP,
      'finalCash'=> $finalCash,
      'total Discount From Cash'=> $totalDiscountCashSale,
      'total Discount From Due'=> $totalDiscountDueSale,
      'total Refund From Cash'=> $totalRefundCash,
      'total Refund From Due'=> $totalRefundDue,
      'Total Expense Payement Cash'=>0,
      'Total Expense Payement Bank'=>0,
      'salaries' => $salary,
      'totalSalary' => $totalSalary
    ];
    
    
    $groupedSalaries = collect($report['salaries'])->groupBy('EmployeeID')->map(function ($items) {
    return (object)[
        'EmployeeID' => $items->first()->EmployeeID,
        'Name' => $items->first()->Name,
        'Amount' => $items->sum('Amount'),
    ];
})->values(); // Optional: reset numeric keys

$report['groupedSalaries'] = $groupedSalaries;



    $activities = [
      'Total Invoiced' => $totalInvoices,
      'Total Items Sold' => $totalCat,
      'Total Quantities Sold' => $totalQtys,
      'Total Refunded Items' => $totalRefundItems
    ];


    // return $activities;

    // return ['report' => $report, 'shopName' => $shopName[0]->ShopName, 'date' => $date, 'activities' => $activities, 'DueCustomers'=>$DueCustomers, 'CashTender'=>$cashTender, 'expenses'=>$expenses];

    // return $vendorPayments;

    return view('report.daily_report.dailyReportRevice', ['report' => $report, 'shopName' => $shopName[0]->ShopName, 'date' => $date, 'activities' => $activities, 'DueCustomers' => $DueCustomers, 'CashTender' => $cashTenders, 'expenses' => $expenses, 'purchases' => $purchases, 'dues' => $duePays, 'vendorPayments'=>$vendorPayments, 'methods'=>$methods]);
  }
}



<style>
    table {
        font-family: arial, sans-serif;
        border-collapse: collapse;
        width: 90%;
    }

    td,
    th {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }

    tr:nth-child(even) {
        background-color: #dddddd;
    }
</style>

<div>
    <div>
        <h1>Daily Report</h1>
        <h3>Shop: {{$shopName}}</h3>
        <h4>Report Date : {{ $date }}</h4>
        <h4>Print Date : {{ date('d/m/Y h:i A') }}</h4>
    </div>
    <div>
        <table>
            <tr>
                <th>
                    Today Sale
                </th>
                <th>
                    {{$report['Total Sale']}}
                </th>
            </tr>
            <tr>
                <th>
                    Cash Tender
                </th>
                <th>
                    {{$CashTender}}
                </th>
            </tr>
            <tr>
                <th>
                    Card Tender
                </th>
                <th>
                    {{$report['Card Payment']}}
                </th>
            </tr>
        @foreach($methods as $method)
            <tr>
                <th>
                    {{ $method->MethodName ?? 'Cash' }}
                </th>
                <th>
                    {{ number_format($method->TotalSum, 0) }}
                </th>
            </tr>
        @endforeach

            <tr>
                <th>Total Discout</th>
                <th>{{$report['Total Discount']}}</th>
            </tr>
            <tr>
                <th>Total Refund</th>
                <th>{{$report['Total Refund']}}</th>
            </tr>
            <!--@foreach($activities as $x=>$y)-->
            <!--<tr>-->
            <!--    <th>{{$x}}</th>-->
            <!--    <td>{{$y}}</td>-->
            <!--</tr>-->
            <!--@endforeach-->
            <tr>
                <th>Customer Total Due</th>
                <th>{{$report['Total Customer Due']}}</th>
            </tr>
            <tr>
                <th>Customer's Name</th>
                <th>Amount</th>
            </tr>
            @foreach($DueCustomers as $x)
            <tr>
                <td>{{$x[2]}}</td>
                <td>{{$x[1]}}</td>
            </tr>
            @endforeach
            <tr>
                <th>Customer Total Due Pay</th>
                <th>{{$report['Total Customer Due Payment']}}</th>
            </tr>
            <tr>
                <th>By Cash</th>
                <th>{{$report['By cash']}}</th>
            </tr>
            <tr>
                <th>By Banking</th>
                <th>{{$report['By bank']}}</th>
            </tr>
            <tr>
                <th>Customer's Name</th>
                <th>Amount</th>
            </tr>
            @foreach($dues as $x)
            <tr>
                <td>{{$x->FirstName}}</td>
                <td>{{round($x->Debit,0)}} {{$x->BankName}}</td>
            </tr>
            @endforeach
            <tr>
                <th>Total Purchase</th>
                <th>{{$report['purchaseInvoice']}}</th>
            </tr>
            <tr>
                <th>Total Purchase Invoice</th>
                <th>{{$report['purchase']}}</th>
            </tr>
            <tr>
                <th>Vendor's Name</th>
                <th>Amount</th>
            </tr>
            @foreach($purchases as $x)
            <tr>
                <td>{{$x->VendorName}}</td>
                <td>{{round($x->TotalPrice,0)}}</td>
            </tr>
            @endforeach
            <tr>
                <th>Total Vendor Payment</th>
                <th>{{$report['Total Vendor Payment']}}</th>
            </tr>
            <tr>
                <th>By Cash</th>
                <th>{{$report['Total Vendor Payement Cash']}}</th>
            </tr>
            <tr>
                <th>By Banking</th>
                <th>{{$report['Total Vendor Payement Bank']}}</th>
            </tr>
            <tr>
                <th>Vendor's Name</th>
                <th>Amount</th>
            </tr>
            @foreach($vendorPayments as $x)
            @if(round($x->Debit,0)>0)
            <tr>
                <td>{{$x->VendorName}}</td>
                <td>{{round($x->Debit,0)}}</td>
            </tr>
            @endif
            @endforeach
            <tr>
                <th>
                    Total Expenses
                </th>
                <th>
                    {{$report['Total Expense']}}
                </th>
            </tr>
            <tr>
                <th>By Cash</th>
                <th>{{$report['Total Expense Payement Cash']}}</th>
            </tr>
            <tr>
                <th>By Banking</th>
                <th>{{$report['Total Expense Payement Bank']}}</th>
            </tr>
            <tr>
                <th>Category</th>
                <th>Amount</th>
            </tr>
            @foreach($expenses as $expense)
            <tr>
                <td>{{ $expense->CategoryName }} --[ {{ $expense->Notes }} ]</td>
                <td>{{ round($expense->Amount,0) }}</td>
            </tr>
            @endforeach
            <tr>
            <tr>
                <th>
                    Total Employee Salary
                </th>
                <th>
                    {{$report['totalSalary']}}
                </th>
            </tr>
            <tr>
                <th>Name</th>
                <th>Amount</th>
            </tr>
          @foreach($report['groupedSalaries'] as $salary)
<tr>
    <td>{{ $salary->Name }}</td>
    <td>{{ round($salary->Amount, 0) }}</td>
</tr>
@endforeach

            <tr>
                <th>Banking Activities</th>
                <th>Amount</th>
            </tr>
            <tr>
                <th>Deposite</th>
                <td>{{$report['Total Bank Deposite']}}</td>
            </tr>
            <tr>
                <th>Withdraw</th>
                <td>{{$report['Total Bank Withdraw']}}</td>
            </tr>
            <tr>
                <th>Final Cash</th>
                <th>{{round($report['finalCash'],0)}}</th>
            </tr>
        </table>
    </div>

</div>