<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\RateLimiter;
use App\Mail\BillNotification;
use App\Models\Bill;
use Carbon\Carbon;

class SendBulkInvoiceEmail implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $period;
    protected $userId;
    protected $billType;
    protected $maxTries = 3; // Maximum number of attempts
    public $tries = 3; // Maximum number of attempts
    public $timeout = 300; // 5 minutes timeout per job

    /**
     * Create a new job instance.
     *
     * @param string $period Billing period in YYYY-MM format
     * @param int $userId ID of the user triggering the job
     * @param string|null $billType Type of bills to send ('water', 'utility', or null for all)
     * @return void
     */
    public function __construct($period, $userId, $billType = null)
    {
        $this->period = $period;
        $this->userId = $userId;
        $this->billType = $billType;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        // Get all bills for the period
        $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.CustomerID',
                'b.BillingDate',
                'b.DueDate',
                'b.TotalBill',
                'b.BalanceCF',
                'b.BillingPeriod',
                'b.PrvReading',
                'b.PrvReadingDate',
                'b.CurReading',
                'b.CurReadingDate',
                'b.ConsumptionBilled',
                'c.*',
                'p.PropName as PropertyName',
                'u.UnitID'
            ])
            ->where(DB::raw("DATE_FORMAT(b.BillingDate, '%Y-%m')"), '=', $this->period);

        // Filter by bill type if specified
        if ($this->billType === 'water') {
            $query->join('bulkinvoice as bi', 'b.BulkInvoiceID', '=', 'bi.BulkInvoiceID')
                  ->where('bi.ConsType', 'like', '%water%');
        }

        $bills = $query->get();

        if ($bills->isEmpty()) {
            Log::warning("No bills found for period: {$this->period}" . ($this->billType ? " (type: {$this->billType})" : ""));
            return;
        }

        $successCount = 0;
        $errorCount = 0;
        $errors = [];
        $emailsPerMinute = 10; // Adjust based on your email provider's limits
        $delayBetweenEmails = 60 / $emailsPerMinute; // Calculate delay in seconds
        $startTime = microtime(true);

        Log::info("Starting bulk email job", [
            'period' => $this->period,
            'bill_type' => $this->billType,
            'total_bills' => count($bills),
            'emails_per_minute' => $emailsPerMinute,
            'delay_between_emails' => $delayBetweenEmails . ' seconds'
        ]);

        foreach ($bills as $index => $bill) {
            $iterationStart = microtime(true);
            
            try {
                Log::info("Processing bill #{$bill->BillID}", [
                    'customer_email' => $bill->CustomerEmail,
                    'progress' => ($index + 1) . '/' . count($bills)
                ]);

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

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

                // Log the original BillingPeriod value from the database
                Log::info('BillingPeriod from database', [
                    'BillID' => $bill->BillID,
                    'BillingPeriod' => $bill->BillingPeriod,
                    'BillingPeriodType' => gettype($bill->BillingPeriod),
                    'BillingPeriodLength' => is_string($bill->BillingPeriod) ? strlen($bill->BillingPeriod) : 'N/A'
                ]);

                // Format billing period (e.g., 202601 -> 'January 2026')
                $billingPeriod = '';
                if (isset($bill->BillingPeriod) && is_numeric($bill->BillingPeriod) && strlen((string)$bill->BillingPeriod) >= 6) {
                    $year = substr((string)$bill->BillingPeriod, 0, 4);
                    $month = (int)substr((string)$bill->BillingPeriod, 4, 2);
                    $billingPeriod = date('F Y', mktime(0, 0, 0, $month, 1, $year));
                    
                    Log::debug('Formatted billing period', [
                        'BillID' => $bill->BillID,
                        'formattedBillingPeriod' => $billingPeriod,
                        'year' => $year,
                        'month' => $month
                    ]);
                } else {
                    Log::warning('Invalid BillingPeriod format', [
                        'BillID' => $bill->BillID,
                        'BillingPeriod' => $bill->BillingPeriod,
                        'is_numeric' => is_numeric($bill->BillingPeriod) ? 'true' : 'false',
                        'length' => is_string($bill->BillingPeriod) ? strlen($bill->BillingPeriod) : 'N/A'
                    ]);
                }
                
                // Generate PDF with all necessary data
                $pdf = \Barryvdh\DomPDF\Facade\Pdf::loadView('pdf.utility-bill', [
                    'bill' => $bill,
                    'billLines' => $billLines,
                    'customer' => $customer,
                    'totalAmount' => $bill->TotalBill ?? 0,
                    'formattedBillingPeriod' => $billingPeriod
                ]);
                $pdfPath = storage_path('app/temp/bill_' . $bill->BillID . '_' . time() . '.pdf');
                
                // Ensure directory exists
                if (!file_exists(dirname($pdfPath))) {
                    mkdir(dirname($pdfPath), 0755, true);
                }
                
                $pdf->save($pdfPath);

                // Send email
                $subject = $this->billType === 'water' ? 'Water Bill' : 'Utility Bill';
                Mail::to($bill->CustomerEmail)
                    ->send(new BillNotification(
                        $bill,
                        $subject . ' - ' . $bill->BillID,
                        'Please find your ' . strtolower($subject) . ' attached.',
                        $pdfPath
                    ));

                // Update bill status
                DB::table('Bill')
                    ->where('BillID', $bill->BillID)
                    ->update([
                        'LastUpdateDate' => now(),
                        'LastUpdateUser' => $this->userId
                    ]);

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

                $successCount++;
                Log::info("Successfully processed bill #{$bill->BillID}");

            } catch (\Exception $e) {
                $errorCount++;
                $errorMsg = "Error processing bill #{$bill->BillID}: " . $e->getMessage();
                $errors[] = $errorMsg;
                Log::error($errorMsg, [
                    'exception' => get_class($e),
                    'file' => $e->getFile(),
                    'line' => $e->getLine(),
                    'trace' => $e->getTraceAsString()
                ]);
            }

            // Calculate remaining time and sleep if needed
            $elapsed = microtime(true) - $iterationStart;
            $remainingDelay = max(0, $delayBetweenEmails - $elapsed);
            
            if ($remainingDelay > 0 && $index < count($bills) - 1) {
                Log::debug("Sleeping for {$remainingDelay} seconds to respect rate limits");
                sleep($remainingDelay);
            }
        }

        // Log the results
        Log::info("Bulk email job completed", [
            'period' => $this->period,
            'total' => $bills->count(),
            'success' => $successCount,
            'failed' => $errorCount,
            'errors' => $errors
        ]);
    }

    /**
     * Handle a job failure.
     *
     * @param  \Throwable  $exception
     * @return void
     */
    public function failed(\Throwable $exception)
    {
        Log::error('Bulk email job failed', [
            'period' => $this->period,
            'error' => $exception->getMessage(),
            'exception' => get_class($exception),
            'file' => $exception->getFile(),
            'line' => $exception->getLine(),
            'trace' => $exception->getTraceAsString()
        ]);
    }
}