<?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\DB;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Cache;
use Barryvdh\Snappy\Facades\SnappyPdf;
use Exception;

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

    public $timeout = 7200; // 2 hours
    public $tries = 3;

    protected $dbConfig;
    protected $fromDate;
    protected $toDate;
    protected $notificationEmail;
    protected $jobId;
    protected $connectionName;
    private $db;

    public function __construct($dbConfig, $fromDate, $toDate, $notificationEmail = null, $jobId = null)
    {
        $this->dbConfig = $dbConfig;
        $this->fromDate = $fromDate;
        $this->toDate = $toDate;
        $this->notificationEmail = $notificationEmail;
        $this->jobId = $jobId;
        $this->connectionName = 'dynamic_pdf_' . md5(json_encode($dbConfig) . time());
    }

    public function handle()
    {
        try {
            // Update status to processing
            $this->updateJobStatus('processing', 10);

            // Increase PHP limits
            ini_set('memory_limit', '2G');
            ini_set('max_execution_time', 7200);

            // Setup dynamic database connection
            $this->setupDynamicConnection();
            $this->updateJobStatus('processing', 20);

            // Get active financial year
            $acYear = $this->db->table('ac_year')
                ->where('status', 1)
                ->where('has_closed', 0)
                ->first();

            if (!$acYear) {
                \Log::error('No active financial year found for PDF generation');
                $this->updateJobStatus('failed', 0, 'No active financial year found');
                $this->cleanupConnection();
                return;
            }

            $yearStartDate = $acYear->from_year_month;
            $this->updateJobStatus('processing', 30);

            // Get all ledgers grouped by hierarchy
            $ledgers = $this->getLedgersWithHierarchy();
            $this->updateJobStatus('processing', 40);

            // Process ledgers in chunks
            $chunks = $ledgers->chunk(50);
            $allSections = [];
            $totalChunks = $chunks->count();
            $processedChunks = 0;

            foreach ($chunks as $chunkIndex => $ledgerChunk) {
                $chunkSections = [];
                
                foreach ($ledgerChunk as $ledger) {
                    $chunkSections[] = $this->prepareLedgerSection(
                        $ledger, 
                        $this->fromDate, 
                        $this->toDate, 
                        $acYear->id, 
                        $yearStartDate
                    );
                }

                $allSections = array_merge($allSections, $chunkSections);

                // Update progress (40% to 70%)
                $processedChunks++;
                $progress = 40 + (($processedChunks / $totalChunks) * 30);
                $this->updateJobStatus('processing', $progress);

                // Free memory
                unset($chunkSections);
                gc_collect_cycles();
            }

            $this->updateJobStatus('processing', 75);

            // Generate PDF
            $filename = 'ledger_statements_' . $this->dbConfig['db_database'] . '_' . date('YmdHis') . '.pdf';

            // Get admin profile for header
            $adminProfile = $this->getAdminProfile();
            
            $snappy = app('snappy.pdf');
            $snappy->setTimeout(600);

            $html = view('reports.ledger-statements-pdf', [
                'sections' => $allSections,
                'fromDate' => $this->fromDate,
                'toDate' => $this->toDate,
                'acYear' => $acYear,
                'generatedAt' => now(),
                'totalLedgers' => count($allSections),
                'databaseName' => $this->dbConfig['db_database'],
                'adminProfile' => $adminProfile
            ])->render();

            $snappy->setOption('enable-local-file-access', true);
            $snappy->setOption('margin-top', 15);
            $snappy->setOption('margin-bottom', 15);
            $snappy->setOption('margin-left', 10);
            $snappy->setOption('margin-right', 10);
            $snappy->setOption('footer-center', 'Page [page] of [topage]');
            $snappy->setOption('footer-font-size', 8);
            $snappy->setOption('footer-spacing', 5);
            $snappy->setOption('header-spacing', 5);
            $snappy->setOption('dpi', 96);
            $snappy->setOption('image-quality', 85);

            $pdfContent = $snappy->getOutputFromHtml($html);

            $this->updateJobStatus('processing', 85);

            // Save to storage
            $filepath = "exports/ledger-statements/{$filename}";
            Storage::put($filepath, $pdfContent);
            
            $this->updateJobStatus('processing', 95);

            // Send notification email if provided
            if ($this->notificationEmail) {
                $this->sendNotificationEmail($filename);
            }

            // Update status to completed
            $this->updateJobStatus('completed', 100, 'PDF generated successfully', $filename);

            // Cleanup
            $this->cleanupConnection();
            unset($allSections);
            unset($pdf);
            gc_collect_cycles();

            \Log::info("PDF generated successfully: {$filename}");

            return $filename;

        } catch (Exception $e) {
            \Log::error('PDF Generation Error: ' . $e->getMessage());
            \Log::error($e->getTraceAsString());
            
            $this->updateJobStatus('failed', 0, $e->getMessage());
            $this->cleanupConnection();
            throw $e;
        }
    }

    private function updateJobStatus($status, $progress = 0, $message = null, $filename = null)
    {
        if (!$this->jobId) {
            return;
        }

        $data = [
            'status' => $status,
            'progress' => $progress,
            'database' => $this->dbConfig['db_database'],
            'from_date' => $this->fromDate,
            'to_date' => $this->toDate,
            'updated_at' => now()->toDateTimeString()
        ];

        if ($message) {
            $data['message'] = $message;
        }

        if ($filename) {
            $data['filename'] = $filename;
        }

        if ($status === 'completed') {
            $data['completed_at'] = now()->toDateTimeString();
        }

        Cache::put("pdf_job_{$this->jobId}", $data, 7200);
    }

    // ... rest of your existing methods (setupDynamicConnection, getLedgersWithHierarchy, etc.)
    // Keep all the existing private methods as they are
    
    private function setupDynamicConnection()
    {
        Config::set("database.connections.{$this->connectionName}", [
            'driver' => 'mysql',
            'host' => $this->dbConfig['db_host'],
            'port' => $this->dbConfig['db_port'],
            'database' => $this->dbConfig['db_database'],
            'username' => $this->dbConfig['db_username'],
            'password' => $this->dbConfig['db_password'] ?? '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ]);

        DB::purge($this->connectionName);
        $this->db = DB::connection($this->connectionName);
    }

    private function cleanupConnection()
    {
        if ($this->connectionName) {
            DB::purge($this->connectionName);
        }
    }

    private function getLedgersWithHierarchy()
    {
        return $this->db->table('ledgers as l')
            ->leftJoin('groups as g', 'l.group_id', '=', 'g.id')
            ->select(
                'l.id',
                'l.code',
                'l.left_code',
                'l.right_code',
                'l.name',
                'l.group_id',
                'l.op_balance',
                'l.op_balance_dc',
                'g.name as group_name',
                'g.code as group_code',
                'g.parent_id'
            )
            ->orderBy('l.left_code') 
            ->orderBy('l.right_code') // Assuming nested set model for groups
            // ->orderBy('g.parent_id')
            // ->orderBy('g.name')
            // ->orderBy('l.name')
            ->get();
    }

    private function prepareLedgerSection($ledger, $fromDate, $toDate, $acYearId, $yearStartDate)
    {
        $openingBalance = $this->calculateOpeningBalance($ledger->id, $acYearId, $yearStartDate, $fromDate);
        $transactions = $this->getLedgerTransactions($ledger->id, $fromDate, $toDate);

        $runningBalance = $openingBalance;
        $processedTransactions = [];

        foreach ($transactions as $txn) {
            $netActivity = $txn->debit_amount - $txn->credit_amount;
            $runningBalance += $netActivity;

            $processedTransactions[] = [
                'date' => $txn->date,
                'entry_number' => $txn->entry_number,
                'contra_ledger_name' => $txn->contra_ledger_name,
                'narration' => $txn->narration,
                'debit_amount' => $txn->debit_amount,
                'credit_amount' => $txn->credit_amount,
                'net_activity' => $netActivity,
                'balance' => $runningBalance
            ];
        }

        return [
            'ledger' => $ledger,
            'opening_balance' => $openingBalance,
            'transactions' => $processedTransactions,
            'closing_balance' => $runningBalance,
            'total_debit' => collect($processedTransactions)->sum('debit_amount'),
            'total_credit' => collect($processedTransactions)->sum('credit_amount')
        ];
    }

    private function calculateOpeningBalance($ledgerId, $acYearId, $yearStartDate, $reportStartDate)
    {
        $yearOpening = $this->db->table('ac_year_ledger_balance')
            ->where('ac_year_id', $acYearId)
            ->where('ledger_id', $ledgerId)
            ->first();

        $openingBalance = 0;
        if ($yearOpening) {
            $openingBalance = $yearOpening->dr_amount - $yearOpening->cr_amount;
        }

        if ($yearStartDate < $reportStartDate) {
            $adjustments = $this->db->table('entryitems as ei')
                ->join('entries as e', 'ei.entry_id', '=', 'e.id')
                ->where('ei.ledger_id', $ledgerId)
                ->where('e.date', '>=', $yearStartDate)
                ->where('e.date', '<', $reportStartDate)
                ->select(
                    DB::raw('SUM(CASE WHEN ei.dc = "D" THEN ei.amount ELSE 0 END) as dr_sum'),
                    DB::raw('SUM(CASE WHEN ei.dc = "C" THEN ei.amount ELSE 0 END) as cr_sum')
                )
                ->first();

            if ($adjustments) {
                $openingBalance += ($adjustments->dr_sum - $adjustments->cr_sum);
            }
        }

        return $openingBalance;
    }

    private function getLedgerTransactions($ledgerId, $fromDate, $toDate)
    {
        return $this->db->table('entryitems as ei')
            ->join('entries as e', 'ei.entry_id', '=', 'e.id')
            ->leftJoin('entryitems as ei2', function($join) use ($ledgerId) {
                $join->on('ei2.entry_id', '=', 'e.id')
                     ->where('ei2.ledger_id', '!=', $ledgerId);
            })
            ->leftJoin('ledgers as l2', 'ei2.ledger_id', '=', 'l2.id')
            ->where('ei.ledger_id', $ledgerId)
            ->whereBetween('e.date', [$fromDate, $toDate])
            ->select(
                'e.date',
                'e.number as entry_number',
                'e.narration',
                DB::raw('CASE WHEN ei.dc = "D" THEN ei.amount ELSE 0 END as debit_amount'),
                DB::raw('CASE WHEN ei.dc = "C" THEN ei.amount ELSE 0 END as credit_amount'),
                DB::raw('GROUP_CONCAT(DISTINCT l2.name SEPARATOR " / ") as contra_ledger_name')
            )
            ->groupBy('ei.id', 'e.date', 'e.number', 'e.narration', 'ei.dc', 'ei.amount')
            ->orderBy('e.date')
            ->orderBy('e.id')
            ->get();
    }

    private function sendNotificationEmail($filename)
    {
        try {
            $downloadUrl = url("/ledgers/download-pdf/{$filename}");
            
            \Mail::raw(
                "Your ledger statements PDF has been generated successfully.\n\n" .
                "Database: {$this->dbConfig['db_database']}\n" .
                "Period: {$this->fromDate} to {$this->toDate}\n\n" .
                "Download URL: {$downloadUrl}\n\n" .
                "Note: This link will expire in 24 hours.",
                function($message) use ($filename) {
                    $message->to($this->notificationEmail)
                            ->subject('Ledger Statements PDF Ready - ' . $this->dbConfig['db_database']);
                }
            );
        } catch (Exception $e) {
            \Log::error('Email notification failed: ' . $e->getMessage());
        }
    }

    private function getAdminProfile()
    {
        return $this->db->table('admin_profile')
            ->select(
                'name',
                'name_tamil',
                'name',
                'since_eng',
                'since_tamil',
                'city_tamil',
                'address1',
                'address2',
                'city',
                'postcode',
                'telephone',
                'mobile',
                'email',
                'regno'
            )
            ->first();
    }
}