<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Mail;
use App\Models\BulkInvoice;
use App\Models\MeterReading;
use App\Models\Company;
use App\Models\Bill;
use Illuminate\Support\Facades\Auth;
use App\Http\Requests\ProcessBillsRequest;
use Carbon\Carbon;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;
use Illuminate\View\View;
use App\Models\BillLine;
use App\Models\Customer;
use Barryvdh\DomPDF\Facade\Pdf as PDF;
use App\Jobs\SendBulkInvoiceEmail;

class UtilityBillController extends Controller
{

    public function index(Request $request)
    {
        // Get the latest billing period
        $latestPeriod = DB::table('bill')
            ->select('BillingPeriod')
            ->whereNotNull('BillingPeriod')
            ->orderBy('BillingPeriod', 'desc')
            ->value('BillingPeriod');
            
        // Get selected period from request or default to latest period
        $selectedPeriod = $request->input('period', $latestPeriod);
        
        // Build the base query
        $query = DB::table('bill as b')
            ->join('customer as c', 'b.CustomerID', '=', 'c.CustomerID')
            ->leftJoin('prounits as u', 'c.UnitID', '=', 'u.UnitID')
            ->leftJoin('property as p', 'u.PropID', '=', 'p.PropID')
            ->select(
                'b.BillID',
                'b.BillingDate',
                'b.DueDate',
                'b.TotalBill',
                'b.BalanceCF',
                'b.BillingPeriod',
                'c.CustomerName',
                'c.CustomerEmail',
                'p.PropName as PropertyName'
            );

        // Filter by selected period if not 'all'
        if ($selectedPeriod !== 'all') {
            $query->where('b.BillingPeriod', '=', $selectedPeriod);
        }

        $bills = $query->orderBy('b.BillingDate', 'desc')
            ->paginate(15)
            ->withQueryString(); // Preserve query string for pagination
        
        // Get distinct billing periods for the dropdown
        $billingPeriods = $this->getBillingPeriods();
        
        return view('bills.utility.index', [
            'bills' => $bills,
            'billingPeriods' => $billingPeriods,
            'selectedPeriod' => $selectedPeriod
        ]);
    }
    
    /**
     * Get distinct billing periods for the dropdown
     */
    protected function getBillingPeriods()
    {
        return DB::table('bill')
            ->select('BillingPeriod')
            ->whereNotNull('BillingPeriod')
            ->distinct()
            ->orderBy('BillingPeriod', 'desc')
            ->pluck('BillingPeriod')
            ->mapWithKeys(function ($period) {
                // Convert YYYYMM to Month YYYY (e.g., 202301 -> Jan 2023)
                $date = \DateTime::createFromFormat('Ym', $period);
                $formatted = $date ? $date->format('M Y') : $period;
                return [$period => $formatted];
            })
            ->prepend('All Periods', 'all');
    }

    /**
     * Display a listing of water bills.
     */
    public function waterIndex(Request $request)
    {
        // Get the latest billing period
        $latestPeriod = DB::table('bill as b')
            ->join('bulkinvoice as bi', 'b.BulkInvoiceID', '=', 'bi.BulkInvoiceID')
            ->where('bi.ConsType', 'like', '%WATER%')
            ->select('b.BillingPeriod')
            ->whereNotNull('b.BillingPeriod')
            ->orderBy('b.BillingPeriod', 'desc')
            ->value('b.BillingPeriod');
            
        // Get selected period from request or default to latest period
        $selectedPeriod = $request->input('period', $latestPeriod);
        
        // Build the base query for water bills
        $query = DB::table('bill as b')
            ->join('customer as c', 'b.CustomerID', '=', 'c.CustomerID')
            ->join('bulkinvoice as bi', 'b.BulkInvoiceID', '=', 'bi.BulkInvoiceID')
            ->leftJoin('prounits as u', 'c.UnitID', '=', 'u.UnitID')
            ->leftJoin('property as p', 'u.PropID', '=', 'p.PropID')
            ->where('bi.ConsType', 'like', '%WATER%')
            ->select(
                'b.BillID',
                'b.BillingDate',
                'b.DueDate',
                'b.TotalBill',
                'b.BalanceCF',
                'b.BillingPeriod',
                'c.CustomerName',
                'c.CustomerEmail',
                'p.PropName as PropertyName'
            );

        // Filter by selected period if not 'all'
        if ($selectedPeriod !== 'all') {
            $query->where('b.BillingPeriod', '=', $selectedPeriod);
        }

        $bills = $query->orderBy('b.BillingDate', 'desc')
            ->paginate(15)
            ->withQueryString(); // Preserve query string for pagination
        
        // Get distinct billing periods for the dropdown 
        $billingPeriods = $this->getBillingPeriods();
        
        return view('bills.water.index', [
            'bills' => $bills,
            'billingPeriods' => $billingPeriods,
            'selectedPeriod' => $selectedPeriod
        ]);
    }

    

    public function sendAll(Request $request)
{
    $period = $request->input('period');
    
    if (!$period || $period === 'all') {
        return redirect()->back()->with('error', 'Please select a specific billing period to send all bills.');
    }
    
    // Dispatch the job to process emails in the background
    SendBulkInvoiceEmail::dispatch($period, $this->user()->id)
        ->onQueue('emails'); // Use a separate queue for emails

    return redirect()->back()
        ->with('success', 'Bulk email sending has been queued. You will be notified when all emails have been sent.');
}
    
    /**
     * Send a single utility bill
     *
     * @param  int  $billId
     * @return \Illuminate\Http\Response
     */
    public function send($billId)
    {
        try {
             Log::info('Attempting to generate PDF for bill ID: ' . $billId);
            // Find the bill with customer relationship
            $bill = Bill::with('customer')->findOrFail($billId);
            
            Log::info('Bill found:', [
                'bill_id' => $bill->BillID,
                'customer_id' => $bill->CustomerID,
                'customer' => $bill->customer ? 'Loaded' : 'Not found'
            ]);
            
            // Get bill lines and calculate total amount
            $billLines = \App\Models\BillLine::where('BillID', $bill->BillID)
                ->orderBy('BLineID')
                ->get();

            // Calculate total amount from bill lines
            $totalBillAmount = $billLines->sum('BLineAmount');

            Log::info('Found ' . $billLines->count() . ' bill lines for bill ID: ' . $bill->BillID);
            Log::info('Calculated total bill amount:', [
                'bill_id' => $bill->BillID,
                'total_amount' => $totalBillAmount
            ]);


            // Generate PDF
            $pdf = PDF::loadView('pdf.utility-bill', data:[
                'customer' => $bill->customer,
                'bill' => $bill,
                'billingPeriod' => $bill->BillingPeriod,
                'billLines' => $billLines,
                'totalBillAmount' => $totalBillAmount
            ]);
            $pdfPath = storage_path('app/temp/bill_' . $bill->BillID . '.pdf');
            
            // Ensure directory exists
            if (!file_exists(dirname($pdfPath))) {
                mkdir(dirname($pdfPath), 0755, true);
            }
            
            $pdf->save($pdfPath);

            // Send email - recipient is set in the BillNotification class
            Mail::send(new \App\Mail\BillNotification(
                $bill,
                'Utility Bill - ' . $bill->BillID,
                'Please find your utility bill attached.',
                $pdfPath
            ));

            // Clean up
            if (file_exists($pdfPath)) {
                unlink($pdfPath);
            }

            // Update bill status or add a note that it was sent
            DB::table('bill')
                ->where('BillID', $billId)
                ->update([
                    'LastUpdateDate' => now(),
                    'LastUpdateUser' => $this->user()->id
                ]);

            return redirect()->back()->with('success', 'Bill has been sent successfully to ' . $bill->CustomerEmail);

        } catch (\Exception $e) {
            Log::error('Error sending utility bill: ' . $e->getMessage());
            return redirect()->back()->with('error', 'Failed to send bill. Error: ' . $e->getMessage());
        }
    }

    /**
     * Show the form for creating a new utility bill.
     */
    public function create()
    {
        // Get the company ID and property ID
        $companyID = session('CompanyID');
        $propertyID = $user->PropID ?? session('PropID');

        if (!$companyID) {
            abort(403, 'Company context is missing. Please log in again.');
        }

        $company = Company::where('CompanyID', $companyID)->first();
        $companyName = $company?->CompanyName ?? 'Unknown Company';

        $billingDate = now()->format('d M Y');

        $latestBillingPeriod = BulkInvoice::where('CompanyID', $companyID)
            ->when($propertyID && Schema::hasColumn('bulkinvoice', 'PropID'), function ($query) use ($propertyID) {
                $query->where('PropID', $propertyID);
            })
            ->orderByDesc('BillingPeriod')
            ->value('BillingPeriod');

        $pendingBillsQuery = DB::table('bulkinvoice')
            ->join('company', 'bulkinvoice.CompanyID', '=', 'company.CompanyID')
            ->select(
                'bulkinvoice.BulkInvoiceID as invoice_id',
                'company.CompanyName as company_name',
                'bulkinvoice.BillingPeriod'
            )
            ->where('bulkinvoice.CompanyID', $companyID)
            ->where('bulkinvoice.Status', 'Pending') // Filter for pending bills
            ->orderBy('company.CompanyName');

        if ($propertyID && Schema::hasColumn('bulkinvoice', 'PropID')) {
            $pendingBillsQuery->where('bulkinvoice.PropID', $propertyID);
        }

        if ($latestBillingPeriod) {
            $pendingBillsQuery->where('bulkinvoice.BillingPeriod', $latestBillingPeriod);
        }

        $pendingBills = $pendingBillsQuery->get();

        $billTable = (new Bill())->getTable();

        $bulkInvoiceQuery = BulkInvoice::where('CompanyID', $companyID)
            ->when($propertyID && Schema::hasColumn('bulkinvoice', 'PropID'), function ($query) use ($propertyID) {
                $query->where('PropID', $propertyID);
            });

        if ($latestBillingPeriod) {
            $bulkInvoiceQuery->where('BillingPeriod', $latestBillingPeriod);
        } else {
            $bulkInvoiceQuery->orderByDesc('BulkInvoiceID');
        }

        $bulkInvoice = $bulkInvoiceQuery->first();

        $latestBillingPeriod ??= MeterReading::latest('BillingPeriod')->value('BillingPeriod');
        $billGeneratedCount = $latestBillingPeriod
            ? Bill::where('BillingPeriod', $latestBillingPeriod)->count()
            : 0;

        $billsQuery = Bill::query()
            ->join('customer', "{$billTable}.CustomerID", '=', 'customer.CustomerID')
            ->select(
                'customer.EMeterNo',
                'customer.CustomerID',
                'customer.AccountNo',
                'customer.CustomerName',
                "{$billTable}.BillID",
                "{$billTable}.ConsumptionBilled",
                "{$billTable}.BalanceBF",
                "{$billTable}.BillingPeriod"
            )
            ->orderBy("{$billTable}.BillingPeriod", 'asc');

        if ($latestBillingPeriod) {
            $billsQuery->where("{$billTable}.BillingPeriod", $latestBillingPeriod);
        }

        $bills = $billsQuery->get();

        $billingPeriods = bill::select('BillingPeriod')
            ->distinct()
            ->orderBy('BillingPeriod', 'desc')
            ->get();

        $billExcellQuery = DB::table($billTable)
            ->select('BillingPeriod')
            ->distinct();

        if ($latestBillingPeriod) {
            $billExcellQuery->where('BillingPeriod', $latestBillingPeriod);
        }

        $billExcell = $billExcellQuery->get();
        
        return view('bills.utility.process', [
              'billingDate' => $billingDate,
            'pendingBills' => $pendingBills,
            'companyName' => $companyName,
            'bulkInvoice' => $bulkInvoice,
            'billGeneratedCount' => $billGeneratedCount,
            'companyID' => $companyID,
            'bills' => $bills,
            'billExcell' => $billExcell,
            'billingPeriods' => $billingPeriods,
            'latestBillingPeriod' => $latestBillingPeriod,
        ]);
        
        // return view('bills.utility.process', [
        //     'customers' => $customers,
        //     'properties' => $properties
        // ]);
    }

    
    public function process(ProcessBillsRequest $request): JsonResponse
    {
        Log::info('Starting bill processing', [
            'action' => 'process_bills',
            'user_id' => Auth::id(),
            'company_id' => session('company_id'),
            'request_data' => $request->except(['_token', 'password']) // Exclude sensitive data
        ]);

        $startTime = microtime(true);
        $companyID = session('CompanyID');

        if (!$companyID) {
            $errorMsg = 'Company context is missing. User not properly authenticated.';
            Log::error($errorMsg, [
                'session_data' => session()->all(),
                'user_id' => Auth::id()
            ]);
            return response()->json(['message' => $errorMsg], 403);
        }

        $bulkInvoiceIds = $request->input('bulk_invoices', []);
        $billingPeriod = $request->input('billing_period');
        $billDateInput = $request->input('bill_date');
        $billDate = $billDateInput ? Carbon::parse($billDateInput) : now();
        $isDraft = (bool) $request->input('is_draft', false);

        Log::debug('Processing parameters', [
            'bulk_invoice_count' => count($bulkInvoiceIds),
            'billing_period' => $billingPeriod,
            'bill_date' => $billDate->toDateString(),
            'is_draft' => $isDraft,
            'company_id' => $companyID
        ]);

        if (empty($bulkInvoiceIds)) {
            Log::warning('No bulk invoice IDs provided for processing', [
                'company_id' => $companyID,
                'billing_period' => $billingPeriod
            ]);
            return response()->json(['message' => 'No invoices selected for processing.'], 400);
        }

        $results = [];
        $allSuccess = true;
        $processedCount = 0;
        $failedCount = 0;

        Log::info('Starting to process bulk invoices', [
            'total_invoices' => count($bulkInvoiceIds),
            'billing_period' => $billingPeriod,
            'company_id' => $companyID
        ]);

        foreach ($bulkInvoiceIds as $index => $bulkInvoiceId) {
            $invoiceStartTime = microtime(true);
            $logContext = [
                'bulk_invoice_id' => $bulkInvoiceId,
                'billing_period' => $billingPeriod,
                'company_id' => $companyID,
                'invoice_index' => $index + 1,
                'total_invoices' => count($bulkInvoiceIds)
            ];

            try {
                Log::debug('Processing bulk invoice', $logContext);

                // Format the date without time for the stored procedure
                $formattedBillDate = $billDate->format('Y-m-d');
                
                Log::debug('Calling SP_DO_BILLS with parameters', array_merge($logContext, [
                    'formatted_bill_date' => $formattedBillDate,
                    'is_draft' => $isDraft ? 'Y' : 'N'
                ]));

                // Let the stored procedure handle the transaction
                DB::statement('CALL SP_DO_BILLS(?, ?, ?, ?, ?)', [
                    $bulkInvoiceId,
                    $billingPeriod,
                    $formattedBillDate, // Using date-only format
                    $companyID,
                    $isDraft ? 'Y' : 'N',
                ]);

                // Update status if not draft
                if (!$isDraft) {
                    BulkInvoice::where('BulkInvoiceID', $bulkInvoiceId)
                        ->update(['Status' => 'Processed']);
                    Log::debug('Updated invoice status to Processed', $logContext);
                }

                $results[] = [
                    'bulk_invoice_id' => $bulkInvoiceId,
                    'status' => 'success',
                ];
                $processedCount++;

                Log::info('Successfully processed bulk invoice', array_merge($logContext, [
                    'processing_time' => round(microtime(true) - $invoiceStartTime, 2) . 's'
                ]));

            } catch (\Throwable $e) {
                $allSuccess = false;
                $failedCount++;
                $errorId = uniqid('bill_err_');
                
                Log::error('Failed to process bulk invoice', array_merge($logContext, [
                    'error_id' => $errorId,
                    'error_message' => $e->getMessage(),
                    'exception' => get_class($e),
                    'file' => $e->getFile(),
                    'line' => $e->getLine(),
                    'processing_time' => round(microtime(true) - $invoiceStartTime, 2) . 's'
                ]));

                $results[] = [
                    'bulk_invoice_id' => $bulkInvoiceId,
                    'status' => 'error',
                    'message' => "An error occurred while processing invoice. Reference: {$errorId}",
                    'error_id' => $errorId
                ];
            }
        }

        $executionTime = round(microtime(true) - $startTime, 2);
        $summary = [
            'total_processed' => $processedCount,
            'total_failed' => $failedCount,
            'total_time' => $executionTime . 's',
            'success_rate' => $processedCount > 0 ? round(($processedCount / ($processedCount + $failedCount)) * 100, 2) . '%' : '0%',
            'billing_period' => $billingPeriod,
            'company_id' => $companyID
        ];

        if ($allSuccess) {
            $message = 'Bills processed successfully.';
            Log::info('All invoices processed successfully', $summary);
        } else {
            $message = 'Some invoices failed to process.';
            Log::warning('Some invoices failed to process', $summary);
        }

        return response()->json([
            'success' => $allSuccess,
            'alert' => [
                'type' => $allSuccess ? 'success' : 'warning',
                'title' => $allSuccess ? 'Success!' : 'Warning!',
                'message' => $message,
                'details' => [
                    'total_processed' => $summary['total_processed'],
                    'total_failed' => $summary['total_failed'],
                    'billing_period' => $summary['billing_period']
                ]
            ],
            'results' => $results,
            'summary' => $summary
        ], $allSuccess ? 200 : 207);
    }

    /**
     * Display the specified utility bill.
     *
     * @param  int  $id
     * @return \Illuminate\View\View
     */
    /**
     * Display the specified utility bill.
     *
     * @param  int  $id
     * @return \Illuminate\View\View
     */
    /**
     * Download the specified utility bill as PDF.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function download($id)
    {
        // Get the bill with customer and property details
        $bill = DB::table('bill as b')
            ->join('customer as c', 'b.CustomerID', '=', 'c.CustomerID')
            ->leftJoin('prounits as u', 'c.UnitID', '=', 'u.UnitID')
            ->leftJoin('property as p', 'u.PropID', '=', 'p.PropID')
            ->select(
                'b.*',
                'c.*',
                'c.CustomerID as CustomerID',
                'c.CustomerName as CustomerName',
                'c.TelNo as Phone',
                'c.Address1',
                'c.Address2',
                'c.EMeterNo',
                'c.AccountNo',
                'c.TenantName',
                'p.PropName as PropertyName',
                'u.UnitIdentity as UnitName'
            )
            ->where('b.BillID', $id)
            ->first();

        if (!$bill) {
            abort(404, 'Bill not found');
        }

        // Get bill lines
        $billLines = DB::table('billline')
            ->where('BillID', $id)
            ->get();

        // Calculate subtotal, tax, and total
        $subtotal = $billLines->sum('BLineAmount');
        $tax = $bill->TaxAmount ?? 0;
        $total = $subtotal + $tax;

        // Get customer details
        $customer = DB::table('customer')
            ->where('CustomerID', $bill->CustomerID)
            ->first();

        // Get company information
        $company = DB::table('company')
            ->where('CompanyID', $this->user()->CompanyID)
            ->first();

        // Add logo path and cheque payable to company data
        if ($company) {
            $logoPath = null;
            if (!empty($company->CompanyLogo)) {
                $logoFile = public_path('logo/' . $company->CompanyLogo);
                $logoPath = file_exists($logoFile) ? $logoFile : null;
            }
            $company->logo_path = $logoPath ?? public_path('logo/sulispmslogo.png');
            $company->cheque_payable_to = $company->ChequePayableTo ?? 'HassConsult Real Estate Ltd - Watermark';
        }

        // Format billing period (format: YYYYMM)
        $formattedBillingPeriod = 'N/A';
        if (!empty($bill->BillingPeriod) && is_numeric($bill->BillingPeriod) && strlen($bill->BillingPeriod) === 6) {
            $year = substr($bill->BillingPeriod, 0, 4);
            $month = substr($bill->BillingPeriod, 4, 2);
            $formattedBillingPeriod = \Carbon\Carbon::createFromDate($year, $month, 1)->format('F Y');
        }

        // Load the PDF view with all necessary data
        $pdf = PDF::loadView('pdf.utility-bill', [
            'bill' => $bill,
            'customer' => $customer,
            'billLines' => $billLines,
            'subtotal' => $subtotal,
            'tax' => $tax,
            'total' => $total,
            'company' => $company,
            'formattedBillingPeriod' => $formattedBillingPeriod
        ]);

        // Set paper size and orientation
        $pdf->setPaper('A4', 'portrait');

        // Open the PDF in a new tab
        return $pdf->stream('Utility-Bill-' . $bill->BillID . '.pdf');
    }

    /**
     * Display the specified utility bill.
     *
     * @param  int  $id
     * @return \Illuminate\View\View
     */
    public function show($id)
    {
        // Get the bill with customer and property details
        $bill = DB::table('bill as b')
            ->join('customer as c', 'b.CustomerID', '=', 'c.CustomerID')
            ->leftJoin('prounits as u', 'c.UnitID', '=', 'u.UnitID')
            ->leftJoin('property as p', 'u.PropID', '=', 'p.PropID')
            ->select(
                'b.*',
                'c.CustomerName',
                'c.CustomerEmail',
                'c.TelNo as Phone',
                'c.Address1',
                'c.Address2',
                'c.EMeterNo',
                'c.AccountNo',
                'p.PropName as PropertyName',
                'u.UnitIdentity as UnitName'
            )
            ->where('b.BillID', $id)
            ->first();

        if (!$bill) {
            abort(404, 'Bill not found');
        }

        // Get bill lines
        $billLines = DB::table('billline')
            ->where('BillID', $id)
            ->get();

        // Calculate totals
        $subtotal = $billLines->sum('BLineAmount');
        $tax = 0; // Adjust if you have tax calculations
        $total = $subtotal + $tax;

        return view('bills.utility.show', compact('bill', 'billLines', 'subtotal', 'tax', 'total'));
    }

    /**
     * Display the print preview of a utility bill.
     *
     * @param  int  $id
     * @return \Illuminate\View\View
     */
    public function printPreview($id)
    {
        // Get the bill with customer and property details
        $bill = DB::table('bill as b')
            ->join('customer as c', 'b.CustomerID', '=', 'c.CustomerID')
            ->leftJoin('prounits as u', 'c.UnitID', '=', 'u.UnitID')
            ->leftJoin('property as p', 'u.PropID', '=', 'p.PropID')
            ->select(
                'b.*',
                'c.CustomerName',
                'c.CustomerEmail',
                'c.TelNo',
                'c.Address1',
                'c.Address2',
                'c.EMeterNo',
                'c.AccountNo',
                'p.PropName as PropertyName',
                'u.UnitIdentity as UnitName'
            )
            ->where('b.BillID', $id)
            ->first();

        if (!$bill) {
            abort(404, 'Bill not found');
        }

        // Get bill lines
        $billLines = DB::table('billline')
            ->where('BillID', $id)
            ->get();

        // Calculate total amount from bill lines
        $totalAmount = $billLines->sum('BLineAmount');
        
        // Format the billing period
        $billingDate = \Carbon\Carbon::parse($bill->BillingDate);
        $formattedBillingPeriod = $billingDate->format('F Y');

        return view('pdf.utility-bill', [
            'bill' => $bill,
            'customer' => (object)[
                'TenantName' => $bill->CustomerName,
                'CustomerName' => $bill->CustomerName,
                'TelNo' => $bill->TelNo,
                'Address1' => $bill->Address1,
                'Address2' => $bill->Address2,
                'EMeterNo' => $bill->EMeterNo,
                'AccountNo' => $bill->AccountNo,
            ],
            'billLines' => $billLines,
            'totalAmount' => $totalAmount,
            'formattedBillingPeriod' => $formattedBillingPeriod,
            'subtotal' => $totalAmount
        ]);
    }

    /**
     * Show the form for processing water bills.
     */
    public function waterCreate()
    {
        // Get the company ID and property ID
        $companyID = session('CompanyID');
        $propertyID = session('PropID');

        if (!$companyID) {
            abort(403, 'Company context is missing. Please log in again.');
        }

        $company = Company::where('CompanyID', $companyID)->first();
        $companyName = $company?->CompanyName ?? 'Unknown Company';

        $billingDate = now()->format('d M Y');

        $latestBillingPeriod = BulkInvoice::where('CompanyID', $companyID)
            ->where('ConsType', 'like', '%water%')
            ->when($propertyID && Schema::hasColumn('bulkinvoice', 'PropID'), function ($query) use ($propertyID) {
                $query->where('PropID', $propertyID);
            })
            ->orderByDesc('BillingPeriod')
            ->value('BillingPeriod');

        $pendingBillsQuery = DB::table('bulkinvoice')
            ->join('company', 'bulkinvoice.CompanyID', '=', 'company.CompanyID')
            ->select(
                'bulkinvoice.BulkInvoiceID as invoice_id',
                'company.CompanyName as company_name',
                'bulkinvoice.BillingPeriod',
                'bulkinvoice.ConsType'
            )
            ->where('bulkinvoice.CompanyID', $companyID)
            ->where('bulkinvoice.ConsType', 'like', '%water%')
            ->where('bulkinvoice.Status', 'Pending') // Filter for pending bills
            ->orderBy('company.CompanyName');

        if ($propertyID && Schema::hasColumn('bulkinvoice', 'PropID')) {
            $pendingBillsQuery->where('bulkinvoice.PropID', $propertyID);
        }

        if ($latestBillingPeriod) {
            $pendingBillsQuery->where('bulkinvoice.BillingPeriod', $latestBillingPeriod);
        }

        $pendingBills = $pendingBillsQuery->get();

        $billTable = (new Bill())->getTable();

        $bulkInvoiceQuery = BulkInvoice::where('CompanyID', $companyID)
            ->where('ConsType', 'like', '%water%')
            ->when($propertyID && Schema::hasColumn('bulkinvoice', 'PropID'), function ($query) use ($propertyID) {
                $query->where('PropID', $propertyID);
            });

        if ($latestBillingPeriod) {
            $bulkInvoiceQuery->where('BillingPeriod', $latestBillingPeriod);
        } else {
            $bulkInvoiceQuery->orderByDesc('BulkInvoiceID');
        }

        $bulkInvoice = $bulkInvoiceQuery->first();

        $latestBillingPeriod ??= MeterReading::where('ConsType', 'like', '%water%')
            ->latest('BillingPeriod')
            ->value('BillingPeriod');
            
        $billGeneratedCount = $latestBillingPeriod
            ? Bill::join('bulkinvoice', 'bill.BulkInvoiceID', '=', 'bulkinvoice.BulkInvoiceID')
                ->where('bill.BillingPeriod', $latestBillingPeriod)
                ->where('bulkinvoice.ConsType', 'like', '%water%')
                ->count()
            : 0;

        $billsQuery = Bill::query()
            ->join('customer', "{$billTable}.CustomerID", '=', 'customer.CustomerID')
            ->join('bulkinvoice', "{$billTable}.BulkInvoiceID", '=', 'bulkinvoice.BulkInvoiceID')
            ->where('bulkinvoice.ConsType', 'like', '%water%')
            ->select(
                'customer.WMeterNo',
                'customer.CustomerID',
                'customer.AccountNo',
                'customer.CustomerName',
                "{$billTable}.BillID",
                "{$billTable}.ConsumptionBilled",
                "{$billTable}.BalanceBF",
                "{$billTable}.BillingPeriod"
            )
            ->orderBy("{$billTable}.BillingPeriod", 'asc');

        if ($latestBillingPeriod) {
            $billsQuery->where("{$billTable}.BillingPeriod", $latestBillingPeriod);
        }

        $bills = $billsQuery->get();

        $billingPeriods = $this->getBillingPeriods();

        $billExcellQuery = DB::table($billTable)
            ->join('bulkinvoice', "{$billTable}.BulkInvoiceID", '=', 'bulkinvoice.BulkInvoiceID')
            ->where('bulkinvoice.ConsType', 'like', '%water%')
            ->select("{$billTable}.BillingPeriod")
            ->distinct();

        if ($latestBillingPeriod) {
            $billExcellQuery->where("{$billTable}.BillingPeriod", $latestBillingPeriod);
        }

        $billExcell = $billExcellQuery->get();
        
        return view('bills.water.process', [
            'billingDate' => $billingDate,
            'pendingBills' => $pendingBills,
            'companyName' => $companyName,
            'bulkInvoice' => $bulkInvoice,
            'billGeneratedCount' => $billGeneratedCount,
            'companyID' => $companyID,
            'bills' => $bills,
            'billExcell' => $billExcell,
            'billingPeriods' => $billingPeriods,
            'latestBillingPeriod' => $latestBillingPeriod,
        ]);
    }

    /**
     * Process water bills
     */
    public function waterProcess(ProcessBillsRequest $request): JsonResponse
    {
        Log::info('Starting water bill processing', [
            'action' => 'process_water_bills',
            'user_id' => Auth::id(),
            'company_id' => session('company_id'),
            'request_data' => $request->except(['_token', 'password']) // Exclude sensitive data
        ]);

        $startTime = microtime(true);
        $companyID = session('CompanyID');

        if (!$companyID) {
            $errorMsg = 'Company context is missing. User not properly authenticated.';
            Log::error($errorMsg, [
                'session_data' => session()->all(),
                'user_id' => Auth::id()
            ]);
            return response()->json(['message' => $errorMsg], 403);
        }

        $bulkInvoiceIds = $request->input('bulk_invoices', []);
        $billingPeriod = $request->input('billing_period');
        $billDateInput = $request->input('bill_date');
        $billDate = $billDateInput ? Carbon::parse($billDateInput) : now();
        $isDraft = (bool) $request->input('is_draft', false);

        Log::debug('Processing water bill parameters', [
            'bulk_invoice_count' => count($bulkInvoiceIds),
            'billing_period' => $billingPeriod,
            'bill_date' => $billDate->toDateString(),
            'is_draft' => $isDraft,
            'company_id' => $companyID
        ]);

        if (empty($bulkInvoiceIds)) {
            Log::warning('No bulk invoice IDs provided for water bill processing', [
                'company_id' => $companyID,
                'billing_period' => $billingPeriod
            ]);
            return response()->json(['message' => 'No invoices selected for processing.'], 400);
        }

        // Validate that all selected invoices are water-related
        $waterInvoices = BulkInvoice::whereIn('BulkInvoiceID', $bulkInvoiceIds)
            ->where('ConsType', 'like', '%water%')
            ->where('CompanyID', $companyID)
            ->pluck('BulkInvoiceID')
            ->toArray();

        if (count($waterInvoices) !== count($bulkInvoiceIds)) {
            Log::warning('Some selected invoices are not water-related', [
                'selected_invoices' => $bulkInvoiceIds,
                'water_invoices' => $waterInvoices
            ]);
            return response()->json(['message' => 'Some selected invoices are not water-related.'], 400);
        }

        $results = [];
        $allSuccess = true;
        $processedCount = 0;
        $failedCount = 0;

        Log::info('Starting to process water bulk invoices', [
            'total_invoices' => count($bulkInvoiceIds),
            'billing_period' => $billingPeriod,
            'company_id' => $companyID
        ]);

        foreach ($bulkInvoiceIds as $index => $bulkInvoiceId) {
            $invoiceStartTime = microtime(true);
            $logContext = [
                'bulk_invoice_id' => $bulkInvoiceId,
                'billing_period' => $billingPeriod,
                'company_id' => $companyID,
                'invoice_index' => $index + 1,
                'total_invoices' => count($bulkInvoiceIds)
            ];

            try {
                Log::debug('Processing water bulk invoice', $logContext);

                // Format the date without time for the stored procedure
                $formattedBillDate = $billDate->format('Y-m-d');
                
                Log::debug('Calling SP_DO_BILLS for water invoice with parameters', array_merge($logContext, [
                    'formatted_bill_date' => $formattedBillDate,
                    'is_draft' => $isDraft ? 'Y' : 'N'
                ]));

                // Let the stored procedure handle the transaction
                DB::statement('CALL SP_DO_BILLS(?, ?, ?, ?, ?)', [
                    $bulkInvoiceId,
                    $billingPeriod,
                    $formattedBillDate, // Using date-only format
                    $companyID,
                    $isDraft ? 'Y' : 'N',
                ]);

                // Update status if not draft
                if (!$isDraft) {
                    BulkInvoice::where('BulkInvoiceID', $bulkInvoiceId)
                        ->update(['Status' => 'Processed']);
                    Log::debug('Updated water invoice status to Processed', $logContext);
                }

                $results[] = [
                    'bulk_invoice_id' => $bulkInvoiceId,
                    'status' => 'success',
                ];
                $processedCount++;

                Log::info('Successfully processed water bulk invoice', array_merge($logContext, [
                    'processing_time' => round(microtime(true) - $invoiceStartTime, 2) . 's'
                ]));

            } catch (\Throwable $e) {
                $allSuccess = false;
                $failedCount++;
                $errorId = uniqid('water_bill_err_');
                
                Log::error('Failed to process water bulk invoice', array_merge($logContext, [
                    'error_id' => $errorId,
                    'error_message' => $e->getMessage(),
                    'exception' => get_class($e),
                    'file' => $e->getFile(),
                    'line' => $e->getLine(),
                    'processing_time' => round(microtime(true) - $invoiceStartTime, 2) . 's'
                ]));

                $results[] = [
                    'bulk_invoice_id' => $bulkInvoiceId,
                    'status' => 'error',
                    'message' => "An error occurred while processing water invoice. Reference: {$errorId}",
                    'error_id' => $errorId
                ];
            }
        }

        $executionTime = round(microtime(true) - $startTime, 2);
        $summary = [
            'total_processed' => $processedCount,
            'total_failed' => $failedCount,
            'total_time' => $executionTime . 's',
            'success_rate' => $processedCount > 0 ? round(($processedCount / ($processedCount + $failedCount)) * 100, 2) . '%' : '0%',
            'billing_period' => $billingPeriod,
            'company_id' => $companyID,
            'bill_type' => 'water'
        ];

        if ($allSuccess) {
            $message = 'Water bills processed successfully.';
            Log::info('All water invoices processed successfully', $summary);
        } else {
            $message = 'Some water invoices failed to process.';
            Log::warning('Some water invoices failed to process', $summary);
        }

        return response()->json([
            'success' => $allSuccess,
            'alert' => [
                'type' => $allSuccess ? 'success' : 'warning',
                'title' => $allSuccess ? 'Success!' : 'Warning!',
                'message' => $message,
                'details' => [
                    'total_processed' => $summary['total_processed'],
                    'total_failed' => $summary['total_failed'],
                    'billing_period' => $summary['billing_period']
                ]
            ],
            'results' => $results,
            'summary' => $summary
        ], $allSuccess ? 200 : 207);
    }

    /**
     * Send all water bills for a specific period
     */
    public function waterSendAll(Request $request)
    {
        $period = $request->input('period');
        
        if (!$period || $period === 'all') {
            return redirect()->back()->with('error', 'Please select a specific billing period to send all water bills.');
        }
        
        // Get water bills for the specified period
        $waterBillIds = DB::table('bill as b')
            ->join('bulkinvoice as bi', 'b.BulkInvoiceID', '=', 'bi.BulkInvoiceID')
            ->where('bi.ConsType', 'like', '%water%')
            ->where('b.BillingPeriod', $period)
            ->pluck('b.BillID')
            ->toArray();
        
        if (empty($waterBillIds)) {
            return redirect()->back()->with('error', 'No water bills found for the selected period.');
        }

        // Dispatch the job to process water bill emails in the background
        SendBulkInvoiceEmail::dispatch($period, $this->user()->id, 'water')
            ->onQueue('emails'); // Use a separate queue for emails

        return redirect()->back()
            ->with('success', 'Water bill email sending has been queued. You will be notified when all emails have been sent.');
    }

   
}
