<?php

namespace App\Http\Controllers;

use App\Models\BulkInvoice;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\USer;

class PeriodController extends Controller
{
    public function create()
    {
         // Get the logged-in user's CompanyID
        $companyId = $this->user()->CompanyID;

        // Get unique billing periods from BulkInvoice, ordered by BillingPeriod in descending order
        $billingPeriods = BulkInvoice::select('BillingPeriod')
            ->where('CompanyID', $companyId)
            ->distinct()
            ->orderBy('BillingPeriod', 'desc')
            ->get();

        // Get the latest billing period from tmp_meterreading for this company
        $latestBillingPeriod = DB::table('tmp_meterreading')
            ->select('BillingPeriod')
            ->where('CompanyID', $companyId)
            ->orderBy('BillingPeriod', 'desc')
            ->value('BillingPeriod');
            
        // Get total count of issues for this company
        $totalIssues = DB::table('tmp_meterreading')
            ->where('CompanyID', $companyId)
            ->where(function($query) {
                $query->whereNull('Reading')
                      ->orWhere('Reading', '<=', 0);
            })
            ->count();
            
        // Get paginated meter readings with joins for this company
        $nullReadings = DB::table('tmp_meterreading')
            ->leftJoin('customer', function($join) use ($companyId) {
                $join->on('tmp_meterreading.CustomerID', '=', 'customer.CustomerID')
                     ->where('customer.CompanyID', $companyId);
            })
            ->leftJoin('prounits', 'tmp_meterreading.UnitID', '=', 'prounits.UnitID')
            ->select('tmp_meterreading.*', 'customer.CustomerName', 'prounits.UnitIdentity')
            ->where('tmp_meterreading.CompanyID', $companyId)
            /*
            ->where(function($query) {
                $query->whereNull('tmp_meterreading.Reading')
                      ->orWhere('tmp_meterreading.Reading', '<=', 0);
            })
            */
            ->orderBy('tmp_meterreading.CustomerID')
            ->orderBy('tmp_meterreading.UnitID')
            ->paginate(10);
            
        // Check if there are meter readings without corresponding bulk invoices
        $missingKplcInvoices = false;
        $needsProcessing = false;
        
        if ($latestBillingPeriod) {
            // Check if there are readings in tmp_meterreading for this company
            $hasTmpReadings = DB::table('tmp_meterreading')
                ->where('BillingPeriod', $latestBillingPeriod)
                ->where('CompanyID', $companyId)
                ->exists();
                
            // Check for null readings in tmp_meterreading for the latest billing period and company
            $hasNullTmpReadings = DB::table('tmp_meterreading')
                ->where('BillingPeriod', $latestBillingPeriod)
                ->where('CompanyID', $companyId)
                ->whereNull('Reading')
                ->exists();
                
            // Check for null readings in meterreading for the latest billing period and company
            $hasNullMeterReadings = DB::table('meterreading')
                ->where('BillingPeriod', $latestBillingPeriod)
                ->where('CompanyID', $companyId)
                ->whereNull('Reading')
                ->exists();
                
            if ($hasTmpReadings) {
                // Check if there's already a bulk invoice for this period and company
                $hasBulkInvoice = BulkInvoice::where('BillingPeriod', $latestBillingPeriod)
                    ->where('CompanyID', $companyId)
                    ->exists();
                    
                $missingKplcInvoices = !$hasBulkInvoice;
                
                // If there are null readings in either tmp_meterreading or meterreading, we need processing
                if ($hasNullTmpReadings || $hasNullMeterReadings) {
                    $needsProcessing = true;
                    $disableButton = true;
                } 
                // Otherwise, check for other processing needs if there's a bulk invoice
                else if ($hasBulkInvoice) {
                    // Check for count mismatch between tmp_meterreading and meterreading for this company
                    $tmpCount = DB::table('tmp_meterreading')
                        ->where('BillingPeriod', $latestBillingPeriod)
                        ->where('CompanyID', $companyId)
                        ->count();
                        
                    $processedCount = DB::table('meterreading')
                        ->where('BillingPeriod', $latestBillingPeriod)
                        ->where('CompanyID', $companyId)
                        ->count();
                        
                    $needsProcessing = $tmpCount > 0 && $tmpCount != $processedCount;
                }
            }
        }

        return view('periods.create', [
            'billingPeriods' => $billingPeriods,
            'latestBillingPeriod' => $latestBillingPeriod,
            'nullReadings' => $nullReadings,
            'totalIssues' => $totalIssues,
            'missingKplcInvoices' => $missingKplcInvoices,
            'needsProcessing' => $needsProcessing,
        ]);
    }

    /**
     * Initialize billing periods for all companies
     */
      public function initPeriod(Request $request)
{
    // Log the start of the process
    Log::info('Starting billing period initialization', [
        'previous_period' => $request->input('PrvBMonth'),
        'new_period' => $request->input('BMonth'),
        'user_id' => $this->user()->id,
        'ip' => $request->ip()
    ]);

    try {
        $PrvBMonth = $request->input('PrvBMonth');
        $BMonth = $request->input('BMonth');

        Log::debug('Validating input parameters', [
            'PrvBMonth' => $PrvBMonth,
            'BMonth' => $BMonth
        ]);

        // Validate input parameters
        if (empty($PrvBMonth) || empty($BMonth)) {
            $error = 'Both previous and new billing months are required';
            Log::error($error);
            return back()->with('error', $error);
        }

        // Check if the period already exists for this company
        $companyId = $this->user()->CompanyID;
        Log::debug('Checking if billing period already exists', ['BMonth' => $BMonth, 'CompanyID' => $companyId]);
        $existingPeriod = DB::table('meterreading')
            ->where('BillingPeriod', $BMonth)
            ->where('CompanyID', $companyId)
            ->exists();

        if ($existingPeriod) {
            $error = "Billing period {$BMonth} already exists";
            Log::warning($error);
            return back()->with('error', $error);
        }

        Log::info('Calling SP_INIT_PERIOD stored procedure', [
            'previous_period' => $PrvBMonth,
            'new_period' => $BMonth
        ]);

        // Call the stored procedure with CompanyID
        $startTime = microtime(true);
        DB::statement("CALL SP_INIT_PERIOD(?, ?, ?)", [$PrvBMonth, $BMonth, $companyId]);
        $executionTime = round((microtime(true) - $startTime) * 1000, 2); // in milliseconds

        Log::info('Successfully executed SP_INIT_PERIOD', [
            'previous_period' => $PrvBMonth,
            'new_period' => $BMonth,
            'execution_time_ms' => $executionTime
        ]);

        return back()->with('success', 'Billing period created successfully.');

    } catch (\Exception $e) {
        Log::error('Error initializing billing period', [
            'error' => $e->getMessage(),
            'trace' => $e->getTraceAsString(),
            'previous_period' => $PrvBMonth ?? null,
            'new_period' => $BMonth ?? null
        ]);

        return back()->with('error', 'Error creating billing period: ' . $e->getMessage());
    }
}
}