<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\Customer;
use App\Models\User;
use App\Models\BulkInvoice;
use App\Models\BulkInvoiceLine;
use App\Models\Property;
use App\Models\Company;
use App\Models\MeterReading;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Barryvdh\DomPDF\Facade\Pdf;
use Illuminate\Support\Facades\Log;

class BulkInvoiceController extends Controller
{
    public function index(Request $request)
    {
        // Get the authenticated user's CompanyID
        $companyId =  $this->user()->CompanyID;
        
        if (!$companyId) {
            return redirect()->back()->with('error', 'No company associated with this account.');
        }
        
        $search = $request->input('search');
        $propertyId = $request->input('property_id');
        $consType = $request->input('cons_type');
        
        $invoices = BulkInvoice::with(['property', 'company'])
            ->whereHas('property', function($query) use ($companyId) {
                $query->where('CompanyID', $companyId);
            })
            ->when($propertyId, function($query) use ($propertyId) {
                return $query->where('PropID', $propertyId);
            })
            ->when($consType, function($query) use ($consType) {
                return $query->where('ConsType', $consType);
            })
            ->when($search, function($query) use ($search) {
                return $query->where(function($q) use ($search) {
                    $q->where('InvoiceNo', 'like', "%{$search}%")
                      ->orWhere('BillingPeriod', 'like', "%{$search}%");
                });
            })
            ->orderBy('InvoiceDate', 'desc')
            ->paginate(15)
            ->withQueryString();
            
        // Get properties for the dropdown filter
        $properties = Property::where('CompanyID', $companyId)
            ->pluck('PropName', 'PropID');
            
        // Get consumption types for the dropdown filter
        $consumptionTypes = \DB::table('tblconstype')->select('tID', 'ConsType', 'ConsTypeDescription')->get();
        
        return view('invoices.bulk.index', compact('invoices', 'properties', 'consumptionTypes', 'consType'));
    }

    public function create(Request $request)
    {
        // Get the authenticated user's CompanyID
        $companyId = $this->user()->CompanyID;
        
        if (!$companyId) {
            return redirect()->back()->with('error', 'No company associated with this account.');
        }
        
        // Get the ConsType from the request (default to ENERGY for backward compatibility)
        $consType = $request->input('cons_type', 'ENERGY');
        
        $latestBillingPeriod = MeterReading::latest('BillingPeriod')->value('BillingPeriod') ?? date('Ym');
        
        // Get properties that DON'T have bulk invoices for the current billing period and specified ConsType
        $properties = Property::where('CompanyID', $companyId)
            ->whereNotIn('PropID', function($query) use ($latestBillingPeriod, $consType) {
                $query->select('PropID')
                    ->from('bulkinvoice')
                    ->where('BillingPeriod', $latestBillingPeriod)
                    ->where('ConsType', $consType);
            })
            ->pluck('PropName', 'PropID');
            
        $bulkInvoiceLines = collect();
        
        $userCompany = Company::find($companyId);

        $latestInvoiceID = BulkInvoiceLine::max('BulkInvoiceID'); 
        if ($latestInvoiceID) {
            $bulkInvoiceLines = BulkInvoiceLine::where('BulkInvoiceID', $latestInvoiceID)
                ->select('BulkInvoiceID', 'BulkLineID', 'BulkCategID', 'BulkLineDesc', 'BulkLineUnitPrice', 'BulkLineAmount')
                ->get();
        }
        
        // Get previous readings for all properties
        $previousReadings = BulkInvoice::select('PropID', 'PreviousReading1', 'PreviousReading2', 'BillingPeriod')
            ->whereIn('PropID', $properties->keys())
            ->whereIn('BulkInvoiceID', function($query) {
                $query->select(DB::raw('MAX(BulkInvoiceID)'))
                    ->from('bulkinvoice')
                    ->groupBy('PropID');
            })
            ->get()
            ->keyBy('PropID');
            
        // Get consumption types from tblconstype
        $consumptionTypes = \DB::table('tblconstype')->select('tID', 'ConsType', 'ConsTypeDescription')->get();
            
        return view('invoices.bulk.create', [
            'properties' => $properties,
            'userCompany' => $userCompany,
            'bulkInvoiceLines' => $bulkInvoiceLines,
            'latestBillingPeriod' => $latestBillingPeriod,
            'previousReadings' => $previousReadings,
            'consType' => $consType,
            'consumptionTypes' => $consumptionTypes
        ]);
    }

    public function getAvailableProperties(Request $request)
    {
        try {
            $billingPeriod = str_replace('-', '', $request->input('billing_period'));
            $consType = $request->input('cons_type', 'ENERGY'); // Get ConsType from request
            $user = $this->user();
            
            // Log debugging information
            Log::info('getAvailableProperties called', [
                'billing_period' => $billingPeriod,
                'cons_type' => $consType,
                'user_authenticated' => !is_null($user),
                'user_id' => $user ? $user->id : null
            ]);
            
            if (!$user) {
                Log::warning('User not authenticated in getAvailableProperties');
                return response()->json(['error' => 'User not authenticated.'], 401);
            }
            
            $companyId = $user->CompanyID;
            
            if (!$companyId) {
                Log::warning('No CompanyID associated with user', ['user_id' => $user->id]);
                return response()->json(['error' => 'No company associated with this account.'], 403);
            }
            
            Log::info('Fetching properties for company', ['company_id' => $companyId, 'billing_period' => $billingPeriod, 'cons_type' => $consType]);
            
            // Get properties that DON'T have bulk invoices for the specified billing period and ConsType
            $properties = Property::where('CompanyID', $companyId)
                ->whereNotIn('PropID', function($query) use ($billingPeriod, $consType) {
                    $query->select('PropID')
                        ->from('bulkinvoice')
                        ->where('BillingPeriod', $billingPeriod)
                        ->where('ConsType', $consType);
                })
                ->select('PropID', 'PropName')
                ->get();
            
            Log::info('Found properties', ['count' => $properties->count()]);
            
            // Get previous readings for these properties
            $propertyIds = $properties->pluck('PropID');
            $previousReadings = BulkInvoice::select('PropID', 'PreviousReading1', 'PreviousReading2', 'CurrentReading1', 'CurrentReading2', 'BillingPeriod')
                ->whereIn('PropID', $propertyIds)
                ->whereIn('BulkInvoiceID', function($query) {
                    $query->select(DB::raw('MAX(BulkInvoiceID)'))
                        ->from('bulkinvoice')
                        ->groupBy('PropID');
                })
                ->get()
                ->keyBy('PropID');
            
            // Combine the data
            $result = $properties->map(function($property) use ($previousReadings) {
                $reading = $previousReadings->get($property->PropID);
                return [
                    'PropID' => $property->PropID,
                    'PropName' => $property->PropName,
                    'PreviousReading1' => $reading ? $reading->PreviousReading1 : '',
                    'PreviousReading2' => $reading ? $reading->PreviousReading2 : '',
                    'CurrentReading1' => $reading ? $reading->CurrentReading1 : '',
                    'CurrentReading2' => $reading ? $reading->CurrentReading2 : '',
                    'LastBillingPeriod' => $reading ? $reading->BillingPeriod : ''
                ];
            });
            
            Log::info('Returning properties data', ['count' => $result->count()]);
            
            return response()->json($result);
            
        } catch (\Exception $e) {
            Log::error('Error in getAvailableProperties', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
                'request_data' => $request->all()
            ]);
            
            return response()->json(['error' => 'An error occurred while loading properties: ' . $e->getMessage()], 500);
        }
    }

    public function store(Request $request)
    {
        // Get the authenticated user's CompanyID
        $companyId = $this->user()->CompanyID;
        
        if (!$companyId) {
            return redirect()->back()->with('error', 'No company associated with this account.');
        }
        
        // Validate the request
        $request->validate([
            'cons_type' => 'required|in:ENERGY,WATER',
            'PropID' => 'required',
            'BillingPeriod' => 'required',
            'InvoiceNo' => 'required',
            'InvoiceAmount' => 'required|numeric|min:0',
        ]);
        
        try {
            // Verify the property belongs to the user's company
            $property = Property::where('PropID', $request->input('PropID'))
                ->where('CompanyID', $companyId)
                ->firstOrFail();
            
            // Start transaction
            DB::beginTransaction();
            
            try {
                // Get ConsType from request (now validated)
                $consType = $request->input('cons_type');
                
                // Check if invoice already exists for this property and billing period
                $billingPeriod = str_replace('-', '', $request->input('BillingPeriod'));
                $propId = $request->input('PropID');
                
                Log::info('Checking for duplicate invoice', [
                    'PropID' => $propId,
                    'BillingPeriod' => $billingPeriod,
                    'ConsType' => $consType
                ]);
                
                $existingInvoice = BulkInvoice::where('PropID', $propId)
                    ->where('BillingPeriod', $billingPeriod)
                    ->where('ConsType', $consType)
                    ->first();

                Log::info('Duplicate check result', [
                    'found' => $existingInvoice ? true : false,
                    'invoice_id' => $existingInvoice ? $existingInvoice->BulkInvoiceID : null
                ]);

                if ($existingInvoice) {
                    Log::warning('Duplicate invoice found', [
                        'BulkInvoiceID' => $existingInvoice->BulkInvoiceID,
                        'PropID' => $existingInvoice->PropID,
                        'BillingPeriod' => $existingInvoice->BillingPeriod,
                        'ConsType' => $existingInvoice->ConsType,
                        'Status' => $existingInvoice->Status
                    ]);
                    
                    // Return JSON response for AJAX requests
                    if ($request->ajax() || $request->wantsJson()) {
                        return response()->json([
                            'success' => false,
                            'message' => 'An invoice already exists for this property and billing period.'
                        ], 422);
                    }
                    
                    return back()
                        ->withInput()
                        ->with('error', 'An invoice already exists for this property and billing period.');
                }
                
                // Create new invoice
                Log::info('Creating new invoice', [
                    'CompanyID' => $companyId,
                    'PropID' => $request->input('PropID'),
                    'BillingPeriod' => str_replace('-', '', $request->input('BillingPeriod')),
                    'InvoiceNo' => $request->input('InvoiceNo'),
                    'ConsType' => $consType
                ]);
                
                $invoice = new BulkInvoice();
                $invoice->CompanyID = $companyId;
                $invoice->PropID = $request->input('PropID');
                $invoice->BillingPeriod = str_replace('-', '', $request->input('BillingPeriod'));
                $invoice->ConsType = $consType; // Use validated ConsType
                $invoice->InvoiceNo = $request->input('InvoiceNo');
                $invoice->InvoiceDate = date('Y-m-d', strtotime($request->input('InvoiceDate')));
                $invoice->DueDate = date('Y-m-d', strtotime($request->input('DueDate')));
                $invoice->PreviousReading1 = $request->input('PreviousReading1');
                $invoice->CurrentReading1 = $request->input('CurrentReading1');
                $invoice->PreviousReading2 = $request->input('PreviousReading2');
                $invoice->CurrentReading2 = $request->input('CurrentReading2');
                $invoice->Consumption = $request->input('Consumption');
                $invoice->ConsAmount = $request->input('ConsAmount');
                $invoice->InvoiceAmount = $request->input('InvoiceAmount');
                $invoice->FixedAmount = $request->input('FixedAmount');
                $invoice->Status = "Pending";
                
                Log::info('Saving invoice to database');
                $invoice->save();
                
                Log::info('Invoice saved successfully', ['BulkInvoiceID' => $invoice->BulkInvoiceID]);

                // Save standard bulk invoice lines if they exist
                if ($request->has('items') && is_array($request->items)) {
                    foreach ($request->items as $index => $item) {
                        Log::info('Creating bulk invoice line', [
                            'BulkInvoiceID' => $invoice->BulkInvoiceID,
                            'BulkCategID' => $item['BulkCategID'],
                            'BulkLineID' => $item['BulkLineID'],
                            'BulkLineDesc' => $item['BulkLineDesc'],
                            'BulkLineUnitPrice' => (float) ($request->BulkLineUnitPrice[$index] ?? 0),
                            'BulkLineAmount' => (float) ($request->BulkLineAmount[$index] ?? 0),
                        ]);
                        
                        BulkInvoiceLine::create([
                            'BulkInvoiceID' => $invoice->BulkInvoiceID,
                            'BulkCategID' => $item['BulkCategID'],
                            'BulkLineID' => $item['BulkLineID'],
                            'BulkLineDesc' => $item['BulkLineDesc'],
                            'BulkLineUnitPrice' => (float) ($request->BulkLineUnitPrice[$index] ?? 0),
                            'BulkLineAmount' => (float) ($request->BulkLineAmount[$index] ?? 0),
                        ]);
                    }
                }

                // Save optional invoice items if they exist
                if ($request->has('optionalItems') && is_array($request->optionalItems)) {
                    foreach ($request->optionalItems as $item) {
                        if (!empty($item['BulkCategID']) && !empty($item['BulkLineDesc'])) {
                            BulkInvoiceLine::create([
                                'BulkInvoiceID' => $invoice->BulkInvoiceID,
                                'BulkCategID' => $item['BulkCategID'],
                                'BulkLineID' => 'OPT', // Mark as optional item
                                'BulkLineDesc' => $item['BulkLineDesc'],
                                'BulkLineUnitPrice' => (float) ($item['BulkLineUnitPrice'] ?? 0),
                                'BulkLineAmount' => (float) ($item['BulkLineAmount'] ?? 0),
                            ]);
                        }
                    }
                }

                // Commit transaction
                DB::commit();

                // Return JSON response for AJAX requests
                if ($request->ajax() || $request->wantsJson()) {
                    return response()->json([
                        'success' => true,
                        'message' => 'Invoice created successfully!',
                        'redirect' => route('invoices.bulk.show', $invoice->BulkInvoiceID)
                    ]);
                }

                return redirect()->route('invoices.bulk.show', $invoice->BulkInvoiceID)
                    ->with('success', 'Invoice saved successfully.');
                    
            } catch (\Illuminate\Database\QueryException $e) {
                // Handle database constraint violations
                DB::rollBack();
                
                Log::error('Database constraint violation', [
                    'error_code' => $e->getCode(),
                    'error_message' => $e->getMessage(),
                    'sql_state' => $e->errorInfo[0] ?? null,
                    'bindings' => $e->getBindings() ?? []
                ]);
                
                if ($e->getCode() == 23000) {
                    // Duplicate entry error
                    if ($request->ajax() || $request->wantsJson()) {
                        return response()->json([
                            'success' => false,
                            'message' => 'An invoice already exists for this property and billing period.'
                        ], 422);
                    }
                    
                    return back()
                        ->withInput()
                        ->with('error', 'An invoice already exists for this property and billing period.');
                }
                
                throw $e; // Re-throw other database errors
            }
                
        } catch (\Exception $e) {
            // Rollback transaction on error
            if (DB::transactionLevel() > 0) {
                DB::rollBack();
            }
            
            Log::error('General exception in invoice creation', [
                'error_message' => $e->getMessage(),
                'error_code' => $e->getCode(),
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'trace' => $e->getTraceAsString()
            ]);
            
            // Return JSON response for AJAX requests
            if ($request->ajax() || $request->wantsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Failed to save invoice: ' . $e->getMessage()
                ], 500);
            }
            
            return back()->withInput()->with('error', 'Failed to save invoice: ' . $e->getMessage());
        }
    }

    public function edit(BulkInvoice $invoice)
    {
        $invoice->load(['property', 'company', 'bulkInvoiceLines']);
        $properties = Property::pluck('PropName', 'PropID');
        $companyId = session('company_id');
        $userCompany = $companyId ? Company::find($companyId) : null;
        
        // Get consumption types from tblconstype
        $consumptionTypes = \DB::table('tblconstype')->select('tID', 'ConsType', 'ConsTypeDescription')->get();
        
        return view('invoices.bulk.edit', [
            'invoice' => $invoice,
            'properties' => $properties,
            'userCompany' => $userCompany,
            'consumptionTypes' => $consumptionTypes
        ]);
    }

    public function show(BulkInvoice $invoice)
    {
        // Get the authenticated user's CompanyID
        $companyId = $this->user()->CompanyID;
        
        if (!$companyId) {
            return redirect()->back()->with('error', 'No company associated with this account.');
        }
        
        // Verify the invoice's property belongs to the user's company
        $invoice->load(['property' => function($query) use ($companyId) {
            $query->where('CompanyID', $companyId);
        }, 'company', 'bulkInvoiceLines']);
        
        if (!$invoice->property) {
            abort(403, 'Unauthorized to view this invoice.');
        }
        
        return view('invoices.bulk.show', compact('invoice'));
    }

    public function print($id)
    {
        // Get the authenticated user's company ID
        $userCompanyId = $this->user()->CompanyID;
        
        if (!$userCompanyId) {
            abort(403, 'No company associated with this account.');
        }
        
        // Load the invoice with relationships
        $invoice = BulkInvoice::with([
                'property' => function($query) use ($userCompanyId) {
                    $query->where('CompanyID', $userCompanyId);
                },
                'bulkInvoiceLines',
                'company' => function($query) use ($userCompanyId) {
                    $query->where('CompanyID', $userCompanyId)
                          ->select('CompanyID', 'CompanyName', 'CompanyLogo');
                }
            ])
            ->whereHas('property', function($query) use ($userCompanyId) {
                $query->where('CompanyID', $userCompanyId);
            })
            ->where('BulkInvoiceID', $id)
            ->firstOrFail();
            
        // Additional check in case the invoice was loaded but property is null
        if (!$invoice->property) {
            abort(403, 'Unauthorized to print this invoice.');
        }
            
        // Handle company logo from the logged-in user's company
        if ($invoice->company) {
            $logoName = trim($invoice->company->CompanyLogo);
            
            if (!empty($logoName)) {
                // Handle case where CompanyLogo contains full path
                $logoName = basename(str_replace('\\', '/', trim($logoName)));
                $logoPath = public_path('logo' . DIRECTORY_SEPARATOR . $logoName);
                
                // Normalize path for Windows
                $logoPath = str_replace(['/', '\\'], DIRECTORY_SEPARATOR, $logoPath);
                
                if (file_exists($logoPath)) {
                    // Just return the filename, we'll add the 'logo/' prefix in the view
                    $invoice->company->CompanyLogo = $logoName;
                } else {
                    $invoice->company->CompanyLogo = null;
                }
            } else {
                $invoice->company->CompanyLogo = null;
            }
        }
            
        $pdf = PDF::loadView('invoices.bulk.print', compact('invoice'));
        
        // Set paper size and orientation
        $pdf->setPaper('A4', 'portrait');
        
        // Return the PDF as a download with a filename
        return $pdf->stream("Bulk_Invoice_{$invoice->InvoiceNo}.pdf");
    }

    public function update(Request $request, BulkInvoice $invoice)
    {
        // Validate the request
        $request->validate([
            'cons_type' => 'required|in:ENERGY,WATER',
            'PropID' => 'required',
            'BillingPeriod' => 'required',
            'InvoiceNo' => 'required',
            'InvoiceAmount' => 'required|numeric|min:0',
        ]);

        try {
            // Start transaction
            DB::beginTransaction();

            // Update the main invoice details
            $invoice->update([
                'CompanyID' => $request->input('CompanyID'),
                'PropID' => $request->input('PropID'),
                'BillingPeriod' => str_replace('-', '', $request->input('BillingPeriod')),
                'ConsType' => $request->input('cons_type'), // Add ConsType update
                'InvoiceNo' => $request->input('InvoiceNo'),
                'InvoiceDate' => date('Y-m-d', strtotime($request->input('InvoiceDate'))),
                'DueDate' => date('Y-m-d', strtotime($request->input('DueDate'))),
                'PreviousReading1' => $request->input('PreviousReading1'),
                'CurrentReading1' => $request->input('CurrentReading1'),
                'PreviousReading2' => $request->input('PreviousReading2'),
                'CurrentReading2' => $request->input('CurrentReading2'),
                'Consumption' => $request->input('Consumption'),
                'ConsAmount' => $request->input('ConsAmount'),
                'InvoiceAmount' => $request->input('InvoiceAmount'),
                'FixedAmount' => $request->input('FixedAmount'),
            ]);

            // Delete existing invoice lines
            $invoice->bulkInvoiceLines()->delete();

            // Save standard bulk invoice lines if they exist
            if ($request->has('items') && is_array($request->items)) {
                foreach ($request->items as $item) {
                    // Skip if required fields are missing
                    if (empty($item['BulkCategID']) || empty($item['BulkLineDesc'])) {
                        continue;
                    }
                    
                    BulkInvoiceLine::create([
                        'BulkInvoiceID' => $invoice->BulkInvoiceID,
                        'BulkCategID' => $item['BulkCategID'],
                        'BulkLineID' => $item['BulkLineID'] ?? 'LINE',
                        'BulkLineDesc' => $item['BulkLineDesc'],
                        'BulkLineUnitPrice' => (float) ($item['BulkLineUnitPrice'] ?? 0),
                        'BulkLineAmount' => (float) ($item['BulkLineAmount'] ?? 0),
                    ]);
                }
            }

            // Save optional invoice items if they exist
            if ($request->has('optionalItems') && is_array($request->optionalItems)) {
                foreach ($request->optionalItems as $item) {
                    if (!empty($item['BulkCategID']) && !empty($item['BulkLineDesc'])) {
                        BulkInvoiceLine::create([
                            'BulkInvoiceID' => $invoice->BulkInvoiceID,
                            'BulkCategID' => $item['BulkCategID'],
                            'BulkLineID' => 'OPT', // Mark as optional item
                            'BulkLineDesc' => $item['BulkLineDesc'],
                            'BulkLineUnitPrice' => (float) ($item['BulkLineUnitPrice'] ?? 0),
                            'BulkLineAmount' => (float) ($item['BulkLineAmount'] ?? 0),
                        ]);
                    }
                }
            }

            // Commit transaction
            DB::commit();

            // Return JSON response for AJAX requests
            if ($request->ajax() || $request->wantsJson()) {
                return response()->json([
                    'success' => true,
                    'message' => 'Invoice updated successfully!',
                    'redirect' => route('invoices.bulk.show', $invoice->BulkInvoiceID)
                ]);
            }

            return redirect()->route('invoices.bulk.show', $invoice->BulkInvoiceID)
                ->with('success', 'Invoice updated successfully.');

        } catch (\Exception $e) {
            // Rollback transaction on error
            DB::rollBack();
            
            $errorMessage = 'Failed to update invoice: ' . $e->getMessage();
           Log::error($errorMessage, [
                'exception' => $e,
                'invoice_id' => $invoice->BulkInvoiceID ?? null,
                'request_data' => $request->except(['_token', '_method'])
            ]);
            
            // Return JSON response for AJAX requests
            if ($request->ajax() || $request->wantsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => $errorMessage
                ], 500);
            }
            
            return back()->withInput()->with('error', $errorMessage);
        }
    }
}
