<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\CustomerAccount;
use App\Models\Currency;
use App\Models\DocumentHeader;
use App\Models\DocumentLine;
use App\Models\CustomerJournal;
use App\Models\Receipt;
use App\Models\CompanySettings;
use App\Models\User;
use PDF;

class CustomerDailyTransactionController extends Controller
{
    // Display initial form
    public function index()
    {
        $customers = CustomerAccount::orderBy('customer_code')->get();
        $currencies = Currency::orderBy('code')->get();

        return view('reports.customer-daily-transaction', compact('customers', 'currencies'));
    }

    // Generate customer daily transaction report (supports all or single)
    public function generate(Request $request)
    {
        $request->validate([
            'customer_account' => 'required',
            'date_from' => 'required|date',
            'date_to' => 'required|date|after_or_equal:date_from',
            'currency' => 'required',
        ]);

        $customerId = $request->customer_account;
        $dateFrom = $request->date_from;
        $dateTo = $request->date_to;
        $currencyCode = $request->currency;
        $company = CompanySettings::first();
        $customers = CustomerAccount::orderBy('customer_code')->get();
        $currencies = Currency::orderBy('code')->get();

        // Handle "All Customers"
        if ($customerId == 'all') {
            $allTransactions = [];
            $customersList = CustomerAccount::orderBy('customer_code')->get();

            foreach ($customersList as $customer) {
                $transactionData = $this->getTransactionData($customer->id, $currencyCode, $dateFrom, $dateTo);

                $allTransactions[] = [
                    'customer'        => $customer,
                    'transactionData' => $transactionData
                ];
            }

            return view('reports.customer-daily-transaction', compact(
                'allTransactions',
                'customerId',
                'dateFrom',
                'dateTo',
                'currencyCode',
                'customers',
                'currencies',
                'company'
            ));
        } else {
            // Single customer
            $customer = CustomerAccount::findOrFail($customerId);
            $transactionData = $this->getTransactionData($customerId, $currencyCode, $dateFrom, $dateTo);

            return view('reports.customer-daily-transaction', compact(
                'transactionData',
                'customerId',
                'dateFrom',
                'dateTo',
                'currencyCode',
                'customers',
                'currencies',
                'company',
                'customer'
            ));
        }
    }

    // Download PDF (supports all or single)
    public function downloadPdf(Request $request)
    {
        $request->validate([
            'customer_account' => 'required',
            'date_from' => 'required|date',
            'date_to' => 'required|date|after_or_equal:date_from',
            'currency' => 'required',
        ]);

        $customerId = $request->customer_account;
        $dateFrom = $request->date_from;
        $dateTo = $request->date_to;
        $currencyCode = $request->currency;
        $currency = Currency::where('code', $currencyCode)->first();
        $company = CompanySettings::first();

        if ($customerId == 'all') {
            $allTransactions = [];
            $customersList = CustomerAccount::orderBy('customer_code')->get();

            foreach ($customersList as $customer) {
                $transactionData = $this->getTransactionData($customer->id, $currencyCode, $dateFrom, $dateTo);

                $allTransactions[] = [
                    'customer'        => $customer,
                    'transactionData' => $transactionData
                ];
            }

            $pdf = PDF::loadView('reports.customer-daily-transaction-pdf', compact(
                'allTransactions',
                'dateFrom',
                'dateTo',
                'currency',
                'company'
            ));

            return $pdf->download('customer-daily-transactions.pdf');
        } else {
            $customer = CustomerAccount::findOrFail($customerId);
            $transactionData = $this->getTransactionData($customerId, $currencyCode, $dateFrom, $dateTo);

            $allTransactions = [[
                'customer'        => $customer,
                'transactionData' => $transactionData
            ]];

            $pdf = PDF::loadView('reports.customer-daily-transaction-pdf', compact(
                'allTransactions',
                'dateFrom',
                'dateTo',
                'currency',
                'company'
            ));

            return $pdf->download('customer-daily-transactions-' . $customer->customer_code . '.pdf');
        }
    }

    // Retrieve transaction data
    private function getTransactionData($customerId, $currencyCode, $dateFrom, $dateTo)
    {
        $transactionData = [];
        $balance = 0;

        // Documents
        $documents = DocumentHeader::where('customer_account_id', $customerId)
            ->where('currency_code', $currencyCode)
            ->whereIn('type', ['invoice', 'credit_note', 'debit_note'])
            ->whereBetween('document_date', [$dateFrom, $dateTo])
            ->orderBy('document_date')
            ->get();

        foreach ($documents as $document) {
            $dr = 0; $cr = 0;
            if (in_array($document->type, ['invoice', 'debit_note'])) {
                $dr = $document->inclusive_total;
            } elseif ($document->type === 'credit_note') {
                $cr = $document->inclusive_total;
            }
            $balance += ($dr - $cr);

            $transactionData[] = [
                'date'        => $document->document_date,
                'description' => ucfirst(str_replace('_', ' ', $document->type)),
                'reference'   => $document->document_number,
                'dr'          => $dr,
                'cr'          => $cr,
                'balance'     => $balance
            ];
        }

        // Journals
        $journals = CustomerJournal::where('customer_account_id', $customerId)
            ->where('currency_id', function($query) use ($currencyCode) {
                $query->select('id')
                    ->from('currencies')
                    ->where('code', $currencyCode);
            })
            ->whereBetween('date', [$dateFrom, $dateTo])
            ->orderBy('date')
            ->get();

        foreach ($journals as $journal) {
            $dr = 0; $cr = 0;
            if ($journal->tr_code === 'DR') {
                $dr = $journal->amount;
            } elseif ($journal->tr_code === 'CR') {
                $cr = $journal->amount;
            }
            $balance += ($dr - $cr);

            $transactionData[] = [
                'date'        => $journal->date,
                'description' => $journal->narration,
                'reference'   => 'JNL' . str_pad($journal->id, 3, '0', STR_PAD_LEFT),
                'dr'          => $dr,
                'cr'          => $cr,
                'balance'     => $balance
            ];
        }

        // Receipts
        $receipts = Receipt::where('customer_id', $customerId)
            ->whereBetween('receipt_date', [$dateFrom, $dateTo])
            ->orderBy('receipt_date')
            ->get();

        foreach ($receipts as $receipt) {
            $dr = 0; $cr = $receipt->total_amount;
            $balance += ($dr - $cr);

            $transactionData[] = [
                'date'        => $receipt->receipt_date,
                'description' => 'Receipt',
                'reference'   => $receipt->receipt_number,
                'dr'          => $dr,
                'cr'          => $cr,
                'balance'     => $balance
            ];
        }

        usort($transactionData, function($a, $b) {
            return strtotime($a['date']) - strtotime($b['date']);
        });

        return $transactionData;
    }
       public function usersales()
    {
        $users = User::orderBy('name')->get();
        $currencies = Currency::orderBy('code')->get();
        $customers = CustomerAccount::orderBy('customer_code')->get();

        return view('reports.usersales', compact('users', 'currencies', 'customers'));
    }

     public function inventorysales()
    {
        $users = User::orderBy('name')->get();
        $currencies = Currency::orderBy('code')->get();
        $customers = CustomerAccount::orderBy('customer_code')->get();

        return view('reports.inventorysales', compact('users', 'currencies', 'customers'));
    }

     public function sales()
    {
        $users = User::orderBy('name')->get();
        $currencies = Currency::orderBy('code')->get();
        $customers = CustomerAccount::orderBy('customer_code')->get();


        return view('reports.dailysales', compact('users', 'currencies', 'customers'));
    }
public function generateusersales(Request $request)
{
    $request->validate([
        'user_from' => 'required',
        'user_to' => 'required',
        'date_from' => 'required|date',
        'date_to' => 'required|date|after_or_equal:date_from',
        'currency' => 'required',
        'customer_from' => 'required',
        'customer_to' => 'required',
    ]);

    $userfrom = $request->user_from;
    $userto = $request->user_to;
    $customerfrom = $request->customer_from;
    $customerto = $request->customer_to;
    $dateFrom = $request->date_from;
    $dateTo = $request->date_to;
    $currencyCode = $request->currency;

    $company = CompanySettings::first();
    $users = User::orderBy('id')->get();
    $customers = CustomerAccount::orderBy('id')->get();
    $currencies = Currency::orderBy('code')->get();

    // Get users in range
    $usersInRange = ($userfrom === 'all' || $userto === 'all')
        ? User::orderBy('id')->get()
        : User::whereBetween('id', [$userfrom, $userto])->orderBy('id')->get();

    // Get customers in range
    $customersInRange = ($customerfrom === 'all' || $customerto === 'all')
        ? CustomerAccount::orderBy('id')->get()
        : CustomerAccount::whereBetween('id', [$customerfrom, $customerto])
            ->orderBy('id')
            ->get();

    $customerIds = $customersInRange->pluck('id'); // 🔹 for filtering

    $allTransactions = [];

    foreach ($usersInRange as $user) {
        // Fetch invoices, credit notes, and debit notes filtered by user + customer range
        $transactions = DocumentHeader::with(['lines', 'addedByUser', 'customer'])
            ->whereIn('customer_account_id', $customerIds)
            ->where('addedby', $user->id)
            ->where('currency_code', $currencyCode)
            ->whereBetween('document_date', [$dateFrom, $dateTo])
            ->whereIn('type', ['invoice', 'credit_note', 'debit_note'])
            ->orderBy('document_date')
            ->get();

        // Prepare transaction summary per user
        $transactionData = $transactions->map(function ($doc) {
            $totalUnits = $doc->lines->sum('quantity');
            $confirmedUnits = $doc->lines->sum('confirmed_quantity');

            return [
                'date'               => $doc->document_date,
                'reference'          => $doc->document_number,
                'document_id'        => $doc->id, // Add the document id
                'type'               => $doc->type,
                'total_amount'       => $doc->inclusive_total,
                'total_units'        => $totalUnits,
                'confirmed_units'    => $confirmedUnits,
                'added_by'           => $doc->addedByUser->name ?? 'N/A',
                'order_number'       => $doc->order_number,
                'customer_account_id'=> $doc->customer_account_id,
                'customer_name'      => $doc->customer->customer_account ?? 'N/A',
            ];
        });

        $allTransactions[] = [
            'user' => $user,
            'transactionData' => $transactionData,
        ];
    }

    return view('reports.usersales', compact(
        'allTransactions',
        'userfrom',
        'userto',
        'customerfrom',
        'customerto',
        'dateFrom',
        'dateTo',
        'currencyCode',
        'users',
        'customers',
        'currencies',
        'company'
    ));
}

public function generatedailysales(Request $request)
{
    $request->validate([
        'user_from' => 'required',
        'user_to' => 'required',
        'date_from' => 'required|date',
        'date_to' => 'required|date|after_or_equal:date_from',
        'currency' => 'required',
        'customer_from' => 'required',
        'customer_to' => 'required',
    ]);

    $userfrom = $request->user_from;
    $userto = $request->user_to;
    $customerfrom = $request->customer_from;
    $customerto = $request->customer_to;
    $dateFrom = $request->date_from;
    $dateTo = $request->date_to;
    $currencyCode = $request->currency;

    $company = CompanySettings::first();
    $users = User::orderBy('id')->get();
    $customers = CustomerAccount::orderBy('id')->get();
    $currencies = Currency::orderBy('code')->get();

    // Get users in range
    $usersInRange = ($userfrom === 'all' || $userto === 'all')
        ? User::orderBy('id')->get()
        : User::whereBetween('id', [$userfrom, $userto])->orderBy('id')->get();

    // Get customers in range
    $customersInRange = ($customerfrom === 'all' || $customerto === 'all')
        ? CustomerAccount::orderBy('id')->get()
        : CustomerAccount::whereBetween('id', [$customerfrom, $customerto])
            ->orderBy('id')
            ->get();

    $usersIds = $usersInRange->pluck('id'); // 🔹 for filtering

    $allTransactions = [];

    foreach ($customersInRange as $customer) {
        // Fetch invoices, credit notes, and debit notes filtered by customer + user range
        $transactions = DocumentHeader::with(['lines', 'item', 'addedByUser', 'customer'])
            ->whereIn('addedby', $usersIds)
            ->where('customer_account_id', $customer->id)
            ->where('currency_code', $currencyCode)
            ->whereBetween('document_date', [$dateFrom, $dateTo])
            ->whereIn('type', ['invoice', 'credit_note', 'debit_note'])
            ->orderBy('document_date')
            ->get();

        // Prepare transaction summary per user
        $transactionData = $transactions->map(function ($doc) {
            $totalUnits = $doc->lines->sum('quantity');
            $confirmedUnits = $doc->lines->sum('confirmed_quantity');
            $price = $doc->lines->first()?->price ?? 0;
            $cost = $doc->lines->avg(fn($line) => $line->item->cost ?? 0);
            $total= $doc->inclusive_total;
            $discount = $doc->discount_total;
             // ✅ Calculate gross profit per document including discount
            $gross_profit = ($total - $discount) - ($cost * $confirmedUnits);
            $gross_profit_percent = $total != 0 ? ($gross_profit / $total) * 100 : 0;

            // ✅ Optional: average cost for this document (if you still want it)
            $averageCost = $doc->lines->avg(fn($line) => $line->item->cost ?? 0);

            return [
                'date'               => $doc->document_date,
                'reference'          => $doc->document_number,
                'document_id'        => $doc->id, // Add the document id
                'type'               => $doc->type,
                'total_amount'       => $doc->inclusive_total,
                'total_units'        => $totalUnits,
                'confirmed_units'    => $confirmedUnits,
                'added_by'           => $doc->addedByUser->name ?? 'N/A',
                'order_number'       => $doc->order_number,
                'customer_account_id'=> $doc->customer_account_id,
                'customer_name'      => $doc->customer->customer_account ?? 'N/A',
                'discount'           => $doc->discount_total,
                'price'              => $doc->lines->first()?->price ?? 0,
                'cost'               => $averageCost,
                'gross_profit'       => $gross_profit,
                'gross_profit_percent' => $gross_profit_percent,

            ];
        });

        $allTransactions[] = [
            'customer' => $customer,
            'transactionData' => $transactionData,
        ];
    }

    return view('reports.dailysales', compact(
        'allTransactions',
        'userfrom',
        'userto',
        'customerfrom',
        'customerto',
        'dateFrom',
        'dateTo',
        'currencyCode',
        'users',
        'customers',
        'currencies',
        'company'
    ));
}


public function generateinventorysales(Request $request)
{
    $request->validate([
        'user_from' => 'required',
        'user_to' => 'required',
        'date_from' => 'required|date',
        'date_to' => 'required|date|after_or_equal:date_from',
        'currency' => 'required',
        'customer_from' => 'required',
        'customer_to' => 'required',
    ]);

    $userfrom = $request->user_from;
    $userto = $request->user_to;
    $customerfrom = $request->customer_from;
    $customerto = $request->customer_to;
    $dateFrom = $request->date_from;
    $dateTo = $request->date_to;
    $currencyCode = $request->currency;

    $company = CompanySettings::first();
    $users = User::orderBy('id')->get();
    $customers = CustomerAccount::orderBy('id')->get();
    $currencies = Currency::orderBy('code')->get();

    // Get users in range
    $usersInRange = ($userfrom === 'all' || $userto === 'all')
        ? User::orderBy('id')->get()
        : User::whereBetween('id', [$userfrom, $userto])->orderBy('id')->get();

    // Get customers in range
    $customersInRange = ($customerfrom === 'all' || $customerto === 'all')
        ? CustomerAccount::orderBy('id')->get()
        : CustomerAccount::whereBetween('id', [$customerfrom, $customerto])
            ->orderBy('id')
            ->get();

    $usersIds = $usersInRange->pluck('id'); // 🔹 for filtering
    $customerIds = $customersInRange->pluck('id');

    $allTransactions = [];

    // Fetch document lines directly, with necessary relationships
    $linesQuery = DocumentLine::with(['item', 'DocumentHeader.addedByUser', 'DocumentHeader.customer'])
        ->whereHas('DocumentHeader', function($q) use ($usersIds, $customerIds, $dateFrom, $dateTo, $currencyCode) {
            $q->whereIn('addedby', $usersIds)
              ->whereIn('customer_account_id', $customerIds)
              ->where('currency_code', $currencyCode)
              ->whereBetween('document_date', [$dateFrom, $dateTo])
              ->whereIn('type', ['invoice', 'credit_note', 'debit_note']);
        });

    $lines = $linesQuery->get();
// ✅ Group by item name or code instead of item_id
$linesGroupedByItem = $lines->groupBy(fn($line) => optional($line->item)->code ?? 'N/A');

foreach ($linesGroupedByItem as $itemName => $itemLines) {
        $transactionData = $itemLines->map(function ($line) {
            $doc = $line->documentHeader;
            $item = optional($line->item)->code ?? 'N/A';
            $itemDescription = optional($line->item)->description ?? 'N/A';
            $total = $doc->inclusive_total;
            $discount = $doc->discount_total;
            $cost = $line->item->cost ?? 0;
            $gross_profit = $total - ($discount + ($cost * $line->quantity));
            // Gross profit percentage
            $gross_profit_percent = $total != 0 ? ($gross_profit / $total) * 100 : 0;

            return [
                'date'               => $doc->document_date,
                'reference'          => $doc->document_number,
                'document_id'        => $doc->id,
                'type'               => $doc->type,
                'total_amount'       => $doc->inclusive_total,
                'total_units'        => $line->quantity,
                'confirmed_units'    => $line->confirmed_quantity,
                'added_by'           => $doc->addedByUser->name ?? 'N/A',
                'order_number'       => $doc->order_number,
                'customer_account_id'=> $doc->customer_account_id,
                'customer_name'      => $doc->customer->customer_account ?? 'N/A',
                'item_id'            => $line->item_id,
                'item_name'          => $item,
                'item_description'   => $itemDescription,
                'discount'           => $line->discount ?? 0,
                'price'              => $line->price,
                'cost'               => $cost,
                'gross_profit'       => $gross_profit,
                'gross_profit_percent' => $gross_profit_percent,
            ];
        });
        $allTransactions[] = [
            'item_name' => $itemName,
            'transactionData' => $transactionData,
        ];
    }
    return view('reports.inventorysales', compact(
        'allTransactions',
        'userfrom',
        'userto',
        'customerfrom',
        'customerto',
        'dateFrom',
        'dateTo',
        'currencyCode',
        'users',
        'customers',
        'currencies',
        'company'
    ));
}

}
