<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\RentServiceBill;
use App\Models\Property;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Barryvdh\DomPDF\Facade\Pdf;


class RentServiceController extends Controller
{
  

    /**
     * Display a listing of rent service bills.
     */
    public function index(Request $request)
    {
        $companyId = session('CompanyID');
        
        if (!$companyId) {
            return redirect()->route('company.select')
                ->with('error', 'Please select a company first.');
        }

        $billingPeriod = $request->input('billing_period');
        
        $query = RentServiceBill::with(['customer', 'unit.property'])
            ->where('CompanyID', $companyId);
            
        // Get distinct billing periods for the dropdown
        $billingPeriods = RentServiceBill::where('CompanyID', $companyId)
            ->select('BillingPeriod')
            ->distinct()
            ->orderBy('BillingPeriod', 'desc')
            ->pluck('BillingPeriod')
            ->filter()
            ->map(function($period) {
                // Convert YYYYMM to YYYY-MM for display
                if (preg_match('/^(\d{4})(\d{2})$/', $period, $matches)) {
                    return $matches[1] . '-' . $matches[2];
                }
                return $period;
            });
            
        // If no billing period is selected, use the latest one
        if (!$billingPeriod && $billingPeriods->isNotEmpty()) {
            $billingPeriod = $billingPeriods->first();
        }
            
        // Apply billing period filter if provided
        if ($billingPeriod) {
            // Convert YYYY-MM to YYYYMM format for database query
            $dbBillingPeriod = str_replace('-', '', $billingPeriod);
            $query->where('BillingPeriod', $dbBillingPeriod);
        }

        $bills = $query->orderBy('BillingDate', 'desc')
            ->paginate(15)
            ->appends(['billing_period' => $billingPeriod]);

        return view('bills.rent.index', [
            'bills' => $bills,
            'billingPeriods' => $billingPeriods,
            'billingPeriod' => $billingPeriod
        ]);
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        // Redirect to bulk process by default
        return redirect()->route('bills.rent.process');
    }

    /**
     * Show the bulk process form
     */
   /**
 * Show the bulk process form and list unprocessed rent services
 */
public function process(Request $request)
{
    // Get company ID from session
    $companyId = session('CompanyID');
    
    if (!$companyId) {
        return redirect()->route('company.select')
            ->with('error', 'Please select a company first.');
    }

    try {
        // Find the latest billing period that has customers needing bills
        $latestPeriod = null;
        $currentDate = now();

        // Check last 12 months backwards from current month
        for ($i = 0; $i <= 12; $i++) {
            $checkDate = $currentDate->copy()->subMonths($i);
            $periodYm = $checkDate->format('Ym');
            $periodY_m = $checkDate->format('Y-m');

            // Check if there are customers who need billing for this period
            $customersNeedingBills = \App\Models\Customer::where('CompanyID', $companyId)
                ->where('AccountStatus', 'A')
                ->whereDoesntHave('rentServiceBills', function($query) use ($periodYm, $companyId) {
                    $query->where('BillingPeriod', $periodYm)
                          ->where('CompanyID', $companyId);
                })
                ->count();

            if ($customersNeedingBills > 0) {
                $latestPeriod = [
                    'value' => $periodY_m,
                    'label' => $checkDate->format('F Y'),
                    'raw_period' => $periodYm
                ];
                break; // Found the latest period, stop searching
            }
        }

        // If no period found, use current month
        if (!$latestPeriod) {
            $latestPeriod = [
                'value' => $currentDate->format('Y-m'),
                'label' => $currentDate->format('F Y'),
                'raw_period' => $currentDate->format('Ym')
            ];
        }

        $availablePeriods = collect([$latestPeriod]);
        $allPeriods = collect([$latestPeriod]); // For dropdown compatibility

        // If no periods found, show message
        if ($availablePeriods->isEmpty()) {
            return view('bills.rent.process', [
                'unprocessedServices' => collect(),
                'period' => now()->format('Y-m'),
                'availablePeriods' => $availablePeriods,
                'allPeriods' => $allPeriods
            ])->with('info', 'No unprocessed billing periods found.');
        }

        // Get the selected period or default to current month
        $selectedPeriod = $request->input('period');
        
        if ($selectedPeriod) {
            $period = $selectedPeriod;
        } else {
            // Default to current month if it's in our period range, otherwise the most recent period
            $currentMonth = now()->format('Y-m');
            $currentMonthRaw = \Carbon\Carbon::createFromFormat('Y-m', $currentMonth)->format('Ym');
            
            if ($allPeriods->contains('raw_period', $currentMonthRaw)) {
                $period = $currentMonth;
            } else {
                $period = $allPeriods->first() ? \Carbon\Carbon::createFromFormat('Ym', $allPeriods->first()['raw_period'])->format('Y-m') : $currentMonth;
            }
        }
        
        // Convert period to YYYYMM format for comparison
        $billingPeriod = \Carbon\Carbon::createFromFormat('Y-m', $period)->format('Ym');
        
        // Get properties for the filter dropdown
        $properties = \App\Models\Property::where('CompanyID', $companyId)
            ->orderBy('PropID', 'asc')
            ->get();
        
        // Get the selected property filter
        $selectedProperty = $request->input('property');
        
        // Build the query for customers that don't have a rent service bill for this period
        $query = \App\Models\Customer::with(['unit.property'])
            ->where('CompanyID', $companyId)
            ->where('AccountStatus', 'A') // Active customers only
            ->whereDoesntHave('rentServiceBills', function($query) use ($billingPeriod, $companyId) {
                $query->where('BillingPeriod', $billingPeriod)
                      ->where('CompanyID', $companyId);
            });
        
        // Add property filter if selected
        if ($selectedProperty) {
            $query->whereHas('unit', function($query) use ($selectedProperty) {
                $query->where('PropID', $selectedProperty);
            });
        }
        
        $unprocessedServices = $query->paginate(15);

        return view('bills.rent.process', [
            'unprocessedServices' => $unprocessedServices,
            'period' => $period,
            'availablePeriods' => $availablePeriods,
            'allPeriods' => $allPeriods,
            'properties' => $properties
        ]);

    } catch (\Exception $e) {
        return back()->with('error', 'Error fetching unprocessed services: ' . $e->getMessage());
    }
}

    /**
     * Store a newly created resource in storage.
     */
    /**
     * Store a bulk set of rent bills
     */
    public function bulkStore(Request $request)
    {
        // Validate the request
        $validated = $request->validate([
            'BillingPeriod' => 'required|digits:6',
            'BillingDate' => 'required|date',
            'DueDate' => 'required|date|after_or_equal:BillingDate',
            'BaseAmount' => 'required|numeric|min:0',
            'Notes' => 'nullable|string|max:1000',
            'confirmBulk' => 'accepted',
        ], [
            'DueDate.after_or_equal' => 'Due date must be on or after billing date.',
            'confirmBulk.accepted' => 'You must confirm that you want to generate bills for all active customers.',
        ]);

        // Get all active customers
        $customers = \App\Models\Customer::where('AccountStatus', 'A')->get();
        
        if ($customers->isEmpty()) {
            return redirect()->back()->with('error', 'No active customers found.');
        }

        $successCount = 0;
        $errors = [];

        // Process each customer
        foreach ($customers as $customer) {
            try {
                // Create the bill for this customer
                $bill = new \App\Models\Bill([
                    'CustomerID' => $customer->CustomerID,
                    'BillingPeriod' => $validated['BillingPeriod'],
                    'BillingDate' => $validated['BillingDate'],
                    'DueDate' => $validated['DueDate'],
                    'BillType' => 'RENT',
                    'TotalBill' => $validated['BaseAmount'],
                    'BalanceBF' => 0,
                    'TotalPayments' => 0,
                    'BalanceCF' => $validated['BaseAmount'],
                    'BillQuality' => 'E',
                    'LastUpdateUser' => $this->user()->id ?? 'system',
                ]);

                // Save the bill
                $bill->save();

                // Add notes if any
                if (!empty($validated['Notes'])) {
                    $bill->notes()->create([
                        'NoteText' => $validated['Notes'],
                        'CreatedBy' => $this->user()->id ?? 'system',
                        'CreatedDate' => now(),
                    ]);
                }

                $successCount++;
            } catch (\Exception $e) {
                $errors[] = "Failed to create bill for customer {$customer->CustomerName} (ID: {$customer->CustomerID}): " . $e->getMessage();
                Log::error("Failed to create bill for customer {$customer->CustomerID}", [
                    'error' => $e->getMessage(),
                    'customer_id' => $customer->CustomerID,
                    'trace' => $e->getTraceAsString()
                ]);
            }
        }

        // Prepare response message
        $message = "Successfully created {$successCount} rent bills.";
        if (!empty($errors)) {
            $message .= " " . count($errors) . " bills failed to process. Check the logs for details.";
            return redirect()->route('bills.rent.index')
                ->with('warning', $message);
        }

        return redirect()->route('bills.rent.index')
            ->with('success', $message);
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        // Validate the request
        $validated = $request->validate([
            'CustomerID' => 'required|exists:CUSTOMER,CustomerID',
            'BillingPeriod' => 'required|digits:6',
            'BillingDate' => 'required|date',
            'DueDate' => 'required|date|after_or_equal:BillingDate',
            'PrvReading' => 'nullable|numeric|min:0',
            'CurReading' => 'nullable|numeric|min:0|gt:PrvReading',
            'TotalBill' => 'required|numeric|min:0',
            'Notes' => 'nullable|string|max:1000',
        ], [
            'CurReading.gt' => 'Current reading must be greater than previous reading.',
            'DueDate.after_or_equal' => 'Due date must be on or after billing date.',
        ]);

        try {
            // Calculate consumption if readings are provided
            $consumption = null;
            if ($request->filled('PrvReading') && $request->filled('CurReading')) {
                $consumption = $request->CurReading - $request->PrvReading;
            }

            // Create the bill
            $bill = new \App\Models\Bill([
                'CustomerID' => $validated['CustomerID'],
                'BillingPeriod' => $validated['BillingPeriod'],
                'BillingDate' => $validated['BillingDate'],
                'DueDate' => $validated['DueDate'],
                'BillType' => 'ENERGY', // Changed from 'RENT' to match tblconstype
                'PrvReading' => $request->PrvReading,
                'CurReading' => $request->CurReading,
                'ConsumptionBilled' => $consumption,
                'TotalBill' => $validated['TotalBill'],
                'BalanceBF' => 0,
                'TotalPayments' => 0,
                'BalanceCF' => $validated['TotalBill'], // Initial balance equals total bill
                'BillQuality' => 'E',
                'LastUpdateUser' => $this->user()->id ?? 'system',
            ]);

            // Save the bill
            $bill->save();

            // If there are any notes, save them as a bill note
            if ($request->filled('Notes')) {
                $bill->notes()->create([
                    'NoteText' => $request->Notes,
                    'CreatedBy' => $this->user()->id ?? 'system',
                    'CreatedDate' => now(),
                ]);
            }

            return redirect()->route('bills.rent.show', $bill->BillID)
                ->with('success', 'Rent bill created successfully.');

        } catch (\Exception $e) {
            return back()->withInput()
                ->with('error', 'Error creating rent bill: ' . $e->getMessage());
        }
    }

    /**
     * Display the specified resource.
     */
    public function show(string $id)
    {
        // Eager load the customer, unit, and lines relationships
        $bill = RentServiceBill::with(['customer', 'unit', 'lines'])
            ->findOrFail($id);
            
        // Return the view with the bill data
        return view('bills.rent.show', compact('bill'));
    }

    /**
     * Print the specified rent service bill.
     */
    public function print(string $id)
    {
        $bill = RentServiceBill::with(['customer', 'unit', 'lines'])
            ->where('CompanyID', session('CompanyID'))
            ->findOrFail($id);

        $customer = $bill->customer;
        $totalAmount = $bill->TotalAmount ?? 0;

        $pdf = PDF::loadView('bills.rent.print', compact('bill', 'customer', 'totalAmount'));
        
        return $pdf->stream("rent-bill-{$bill->BillID}.pdf");
    }


/**
 * Generate all rent and service bills for a given period
 * 
 * @param  \Illuminate\Http\Request  $request
 * @return \Illuminate\Http\Response
 */
public function generateAllBills(Request $request)
{
    // Get company ID from session
    $companyId = session('CompanyID');
    
    if (!$companyId) {
        return redirect()->route('company.select')
            ->with('error', 'Please select a company first.');
    }

    // Get the billing period from the request or use current month
    $period = $request->input('period', now()->format('Y-m'));
    
    try {
        // Convert period to YYYYMM format for the stored procedure
        $billingPeriod = \Carbon\Carbon::createFromFormat('Y-m', $period)->format('Ym');
        
        // Get the authenticated user's ID
        $user = $this->user()->id ?? auth()->id();

        // Log the start of the process
        Log::info('Starting bill generation', [
            'company_id' => $companyId,
            'billing_period' => $billingPeriod,
            'user' => $user
        ]);

        // Call the stored procedure directly without wrapping in a transaction
        $result = DB::select('CALL SP_PROCESS_RENT_AND_SERVICES_BILL(?, ?, ?)', [
            $companyId,
            $billingPeriod,
            $user
        ]);

        // Log the result from the stored procedure
        Log::info('Stored procedure result', [
            'result' => $result,
            'result_count' => count($result)
        ]);

        // Get the result message
        $message = $result[0]->result ?? 'Bills generated successfully.';

        // Log the final message
        Log::info('Bill generation completed', ['message' => $message]);

        return redirect()->route('bills.rent.process', ['period' => $period])
            ->with('success', $message);

    } catch (\Exception $e) {
        // Log the error
        Log::error('Error in generateAllBills: ' . $e->getMessage(), [
            'exception' => $e,
            'company_id' => $companyId ?? 'not_set',
            'period' => $period ?? 'not_set',
            'trace' => $e->getTraceAsString()
        ]);
        
        return back()->with('error', 'Error generating bills: ' . $e->getMessage());
    }
}

/**
 * Generate a bill for an individual customer
 * 
 * @param  \Illuminate\Http\Request  $request
 * @return \Illuminate\Http\RedirectResponse
 */
public function generateIndividualBill(Request $request)
{
    // Get company ID from session
    $companyId = session('CompanyID');
    
    if (!$companyId) {
        return redirect()->route('company.select')
            ->with('error', 'Please select a company first.');
    }

    // Validate request
    $validated = $request->validate([
        'customer_id' => 'required|exists:CUSTOMER,CustomerID',
        'period' => 'required|date_format:Y-m'
    ]);

    $customerId = $validated['customer_id'];
    $period = $validated['period'];
    
    // Additional validation: ensure customer belongs to the current company
    $customerExists = \App\Models\Customer::where('CustomerID', $customerId)
        ->where('CompanyID', $companyId)
        ->exists();
            
    if (!$customerExists) {
        return back()->with('error', 'Customer not found or does not belong to your company.');
    }

    try {
        // Convert period to YYYYMM format for the stored procedure
        $billingPeriod = \Carbon\Carbon::createFromFormat('Y-m', $period)->format('Ym');
        
        // Get the authenticated user's ID
        $user = $this->user()->id ?? auth()->id();

        // Log the start of the process
        Log::info('Starting individual bill generation', [
            'company_id' => $companyId,
            'customer_id' => $customerId,
            'billing_period' => $billingPeriod,
            'user' => $user
        ]);

        // For individual bill generation, we'll use a simpler approach
        // by creating the bill directly in PHP instead of a stored procedure
        
        // Get customer information
        $customer = \App\Models\Customer::with(['unit.property', 'propServices'])
            ->where('CustomerID', $customerId)
            ->where('CompanyID', $companyId)
            ->where('AccountStatus', 'A')
            ->first();
            
        if (!$customer) {
            return back()->with('error', 'Customer not found or not active.');
        }
        
        // Check if bill already exists
        $billId = $billingPeriod . str_pad($customerId, 5, '0', STR_PAD_LEFT);
        $existingBill = RentServiceBill::find($billId);
        
        if ($existingBill) {
            return back()->with('error', 'Bill already exists for this customer in the selected period.');
        }
        
        // Calculate previous balance
        $previousBalance = RentServiceBill::where('CustomerID', $customerId)
            ->where('CompanyID', $companyId)
            ->where('BillingPeriod', '<', $billingPeriod)
            ->orderBy('BillingPeriod', 'desc')
            ->value('TotalPayable') ?? 0;
        
        // Calculate rent charge
        $rentCharge = 0;
        $rentService = $customer->propServices->where('IDService', 1)->first();
        if ($rentService) {
            $rentPrice = $rentService->pivot->Price ?? $customer->unit->MonthlyRent ?? 0;
            $rentQuantity = $rentService->pivot->Quantity ?? 1;
            $rentMonths = $rentService->NoMonthPerYear ?? 1;
            $rentCharge = $rentPrice * $rentQuantity * $rentMonths;
        }
        
        // Calculate service charges (excluding rent)
        $serviceTotal = $customer->propServices
            ->where('IDService', '<>', 1)
            ->sum(function($service) {
                $price = $service->pivot->Price ?? $service->Price ?? 0;
                $quantity = $service->pivot->Quantity ?? 1;
                $months = $service->NoMonthPerYear ?? 1;
                return $price * $quantity * $months;
            });
        
        $currentCharges = $rentCharge + $serviceTotal;
        $totalPayable = $previousBalance + $currentCharges;
        
        // Create the bill
        $bill = RentServiceBill::create([
            'BillID' => $billId,
            'CompanyID' => $companyId,
            'CustomerID' => $customerId,
            'UnitID' => $customer->UnitID,
            'BillingPeriod' => $billingPeriod,
            'BillingDate' => now(),
            'TotalAmount' => $currentCharges,
            'BillStatus' => 'OPEN',
            'CreatedAt' => now(),
            'CreatedBy' => $user,
            'PreviousBalance' => $previousBalance,
            'CurrentCharges' => $currentCharges,
            'TotalPayable' => $totalPayable
        ]);
        
        // Create rent line item if applicable
        if ($rentCharge > 0) {
            $bill->lines()->create([
                'LineType' => 'RENT',
                'ItemID' => 1,
                'ItemName' => 'Monthly Rent',
                'Units' => ($rentService->pivot->Quantity ?? 1) * ($rentService->NoMonthPerYear ?? 1),
                'UnitPrice' => $rentService->pivot->Price ?? $customer->unit->MonthlyRent ?? 0,
                'LineAmount' => $rentCharge,
                'CreatedAt' => now()
            ]);
        }
        
        // Create service line items
        foreach ($customer->propServices->where('IDService', '<>', 1) as $service) {
            $price = $service->pivot->Price ?? $service->Price ?? 0;
            $quantity = $service->pivot->Quantity ?? 1;
            $months = $service->NoMonthPerYear ?? 1;
            $lineAmount = $price * $quantity * $months;
            
            if ($lineAmount > 0) {
                $bill->lines()->create([
                    'LineType' => 'SERVICE',
                    'ItemID' => $service->IDService,
                    'ItemName' => $service->ServiceName,
                    'Units' => $quantity * $months,
                    'UnitPrice' => $price,
                    'LineAmount' => $lineAmount,
                    'CreatedAt' => now()
                ]);
            }
        }

        // Log the result
        Log::info('Individual bill generated successfully', [
            'company_id' => $companyId,
            'customer_id' => $customerId,
            'billing_period' => $billingPeriod,
            'bill_id' => $billId,
            'total_amount' => $currentCharges
        ]);

        return redirect()->route('bills.rent.process', ['period' => $period])
            ->with('success', "Bill generated successfully for {$customer->CustomerName}. Total amount: {$currentCharges}");

    } catch (\Exception $e) {
        Log::error('Error generating individual bill: ' . $e->getMessage(), [
            'exception' => $e,
            'company_id' => $companyId ?? 'not_set',
            'customer_id' => $customerId ?? 'not_set',
            'period' => $period ?? 'not_set',
            'trace' => $e->getTraceAsString()
        ]);
        
        return back()->with('error', 'Error generating bill: ' . $e->getMessage());
    }
}

}