<?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 Exception;

class GenerateGeneralLedgerPDF 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);

            // ---------------------------------------------------------------
            // WEBZASH CHANGE 1: No ac_year table.
            // CI job:    SELECT * FROM ac_year WHERE status=1 AND has_closed=0
            // Webzash:   SELECT * FROM {prefix}settings → use fy_start / fy_end
            // ---------------------------------------------------------------
            $prefix = isset($this->dbConfig['db_prefix']) ? $this->dbConfig['db_prefix'] : '';

            $settings = $this->db->table($prefix . 'settings')->first();

            if (!$settings) {
                \Log::error('GenerateGeneralLedgerPDF: No settings row found');
                $this->updateJobStatus('failed', 0, 'No settings found in database');
                $this->cleanupConnection();
                return;
            }

            $yearStartDate = $settings->fy_start;  // e.g. "2025-01-01"
            $yearEndDate   = $settings->fy_end;    // e.g. "2025-12-31"

            $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) {
                    // -------------------------------------------------------
                    // WEBZASH CHANGE 2: No $acYear->id param.
                    // CI job:    prepareLedgerSection($ledger, $from, $to, $acYear->id, $yearStartDate)
                    // Webzash:   prepareLedgerSection($ledger, $from, $to, $yearStartDate)
                    // -------------------------------------------------------
                    $chunkSections[] = $this->prepareLedgerSection(
                        $ledger,
                        $this->fromDate,
                        $this->toDate,
                        $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';

            // ---------------------------------------------------------------
            // WEBZASH CHANGE 3: No admin_profile table.
            // CI job:    $adminProfile = $this->getAdminProfile()
            //            reads from admin_profile table in CI DB
            // Webzash:   org name/address already loaded from {prefix}settings above
            // ---------------------------------------------------------------
            $orgName    = isset($settings->name)    ? $settings->name    : '';
            $orgAddress = isset($settings->address) ? $settings->address : '';

            $snappy = app('snappy.pdf');
            $snappy->setTimeout(600);

            // ---------------------------------------------------------------
            // WEBZASH CHANGE 4: No Blade view.
            // CI job:    view('reports.ledger-statements-pdf', [...]) → Blade template
            // Webzash:   buildHtml() inline method (no such Blade view in Webzash)
            //            same $allSections data passed in, same output structure
            // ---------------------------------------------------------------
            $html = $this->buildHtml($allSections, $yearStartDate, $yearEndDate, $orgName, $orgAddress);

            $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;
        }
    }

    // =========================================================================
    // updateJobStatus — IDENTICAL to CI job, zero changes
    // =========================================================================
    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);
    }

    // =========================================================================
    // setupDynamicConnection — same as CI job + strict=>false for GROUP_CONCAT
    // =========================================================================
    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' => false, // CI used true; false needed for GROUP_CONCAT in Webzash queries
            'engine' => null,
        ]);

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

    // =========================================================================
    // cleanupConnection — IDENTICAL to CI job, zero changes
    // =========================================================================
    private function cleanupConnection()
    {
        if ($this->connectionName) {
            DB::purge($this->connectionName);
        }
    }

    // =========================================================================
    // getLedgersWithHierarchy
    // CI job:   table = 'ledgers' (no prefix)
    //           selects l.left_code, l.right_code
    //           ORDER BY l.left_code, l.right_code
    // Webzash:  table = {prefix}ledgers
    //           NO left_code / right_code columns in Webzash DB
    //           ORDER BY g.parent_id, g.name, l.name instead
    // =========================================================================
    private function getLedgersWithHierarchy()
    {
        $prefix = isset($this->dbConfig['db_prefix']) ? $this->dbConfig['db_prefix'] : '';

        return $this->db->table($prefix . 'ledgers as l')
            ->leftJoin($prefix . 'groups as g', 'l.group_id', '=', 'g.id')
            ->select(
                'l.id',
                'l.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('g.parent_id')
            ->orderBy('g.name')
            ->orderBy('l.name')
            ->get();
    }

    // =========================================================================
    // prepareLedgerSection
    // CI job signature:    ($ledger, $fromDate, $toDate, $acYearId, $yearStartDate)
    // Webzash signature:   ($ledger, $fromDate, $toDate, $yearStartDate)
    //   — $acYearId removed because Webzash has no ac_year_ledger_balance table
    // Everything else — loop, runningBalance, return array — IDENTICAL to CI job
    // =========================================================================
    private function prepareLedgerSection($ledger, $fromDate, $toDate, $yearStartDate)
    {
        $openingBalance = $this->calculateOpeningBalance($ledger->id, $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')
        ];
    }

    // =========================================================================
    // calculateOpeningBalance
    // CI job:   reads ac_year_ledger_balance WHERE ac_year_id = $acYearId
    //           openingBalance = dr_amount - cr_amount
    // Webzash:  NO ac_year_ledger_balance table
    //           reads {prefix}ledgers.op_balance + op_balance_dc directly
    //           op_balance_dc = 'D'  →  positive (debit side)
    //           op_balance_dc = 'C'  →  negative (credit side)
    // Pre-period adjustment block — IDENTICAL logic to CI job
    // =========================================================================
    private function calculateOpeningBalance($ledgerId, $yearStartDate, $reportStartDate)
    {
        $prefix = isset($this->dbConfig['db_prefix']) ? $this->dbConfig['db_prefix'] : '';

        // Webzash: opening balance is on the ledger row itself
        $ledgerRow = $this->db->table($prefix . 'ledgers')
            ->where('id', $ledgerId)
            ->first();

        $openingBalance = 0;
        if ($ledgerRow) {
            $openingBalance = ($ledgerRow->op_balance_dc === 'D')
                ? floatval($ledgerRow->op_balance)
                : -floatval($ledgerRow->op_balance);
        }

        // Pre-period adjustment — same logic as CI job
        if ($yearStartDate < $reportStartDate) {
            $adjustments = $this->db->table($prefix . 'entryitems as ei')
                ->join($prefix . '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;
    }

    // =========================================================================
    // getLedgerTransactions
    // CI job:   table names without prefix (entryitems, entries, ledgers)
    // Webzash:  same query, all table names prefixed with $prefix
    // Query logic — IDENTICAL to CI job
    // =========================================================================
    private function getLedgerTransactions($ledgerId, $fromDate, $toDate)
    {
        $prefix = isset($this->dbConfig['db_prefix']) ? $this->dbConfig['db_prefix'] : '';

        return $this->db->table($prefix . 'entryitems as ei')
            ->join($prefix . 'entries as e', 'ei.entry_id', '=', 'e.id')
            ->leftJoin($prefix . 'entryitems as ei2', function ($join) use ($ledgerId) {
                $join->on('ei2.entry_id', '=', 'e.id')
                     ->where('ei2.ledger_id', '!=', $ledgerId);
            })
            ->leftJoin($prefix . '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();
    }

    // =========================================================================
    // buildHtml — NEW method, does not exist in CI job
    // CI job used:  view('reports.ledger-statements-pdf', [...]) Blade template
    // Webzash:      no Blade view for this report — HTML built here instead
    //               receives same $allSections array that Blade view received
    //               org header uses $orgName/$orgAddress (from settings)
    //               instead of $adminProfile (from admin_profile table)
    // =========================================================================
    private function buildHtml($allSections, $yearStartDate, $yearEndDate, $orgName, $orgAddress)
    {
        $fy     = date('d-m-Y', strtotime($yearStartDate)) . ' to ' . date('d-m-Y', strtotime($yearEndDate));
        $period = date('d-m-Y', strtotime($this->fromDate)) . ' to ' . date('d-m-Y', strtotime($this->toDate));

        $css = '
            * { box-sizing: border-box; }
            body { font-family: Arial, Helvetica, sans-serif; font-size: 9pt; color: #222; margin: 0; }
            .page-header { border-bottom: 2px solid #336699; padding-bottom: 8px; margin-bottom: 10px; }
            .org-name    { font-size: 14pt; font-weight: bold; color: #1a3c5e; margin: 0; }
            .org-addr    { font-size: 8pt; color: #666; margin: 2px 0 4px; }
            .meta        { font-size: 8pt; color: #444; margin-top: 4px; }
            .meta span   { margin-right: 16px; }
            .lh { background: #d9e1f2; font-weight: bold; font-size: 9.5pt;
                  padding: 5px 7px; margin-top: 16px; margin-bottom: 0; border-left: 4px solid #336699; }
            .lg { font-style: italic; font-size: 8pt; color: #666; padding: 2px 7px 5px; margin: 0; }
            table { width: 100%; border-collapse: collapse; margin-bottom: 4px; page-break-inside: auto; }
            tr    { page-break-inside: avoid; page-break-after: auto; }
            th    { background: #bdd7ee; font-size: 8pt; font-weight: bold;
                    padding: 4px 5px; border: 1px solid #8baecf; text-align: center; white-space: nowrap; }
            td    { font-size: 8pt; padding: 3px 5px; border: 1px solid #ddd; }
            .num  { text-align: right; white-space: nowrap; }
            .ob   { background: #f5f5f5; font-weight: bold; }
            .cb   { background: #e2efda; font-weight: bold; }
            .tot td { background: #fef9e7; font-weight: bold; border-top: 2px solid #aaa; }
            .neg  { color: #cc0000; }
        ';

        $html  = '<!DOCTYPE html><html><head><meta charset="UTF-8">';
        $html .= '<style>' . $css . '</style></head><body>';

        // Page header — replaces $adminProfile block in Blade view
        $html .= '<div class="page-header">';
        $html .= '<p class="org-name">' . htmlspecialchars($orgName ?: 'General Ledger') . '</p>';
        if ($orgAddress) {
            $html .= '<p class="org-addr">' . nl2br(htmlspecialchars($orgAddress)) . '</p>';
        }
        $html .= '<div class="meta">';
        $html .= '<span><strong>General Ledger</strong></span>';
        $html .= '<span><strong>Financial Year:</strong> ' . htmlspecialchars($fy) . '</span>';
        $html .= '<span><strong>Period:</strong> ' . htmlspecialchars($period) . '</span>';
        $html .= '<span><strong>Total Ledgers:</strong> ' . count($allSections) . '</span>';
        $html .= '<span><strong>Database:</strong> ' . htmlspecialchars($this->dbConfig['db_database']) . '</span>';
        $html .= '<span><strong>Generated:</strong> ' . date('d-m-Y H:i:s') . '</span>';
        $html .= '</div></div>';

        // One table per ledger section
        foreach ($allSections as $section) {
            $ledger = $section['ledger'];
            $ob     = $section['opening_balance'];
            $cb     = $section['closing_balance'];

            $lName = htmlspecialchars(($ledger->code ? $ledger->code . ' – ' : '') . $ledger->name);
            $gName = htmlspecialchars(isset($ledger->group_name) && $ledger->group_name ? $ledger->group_name : 'N/A');

            $html .= '<p class="lh">' . $lName . '</p>';
            $html .= '<p class="lg">Group: ' . $gName . '</p>';

            $html .= '<table><thead><tr>';
            $html .= '<th style="width:8%">Date</th>';
            $html .= '<th style="width:7%">Ref No</th>';
            $html .= '<th style="width:18%">Contra Ledger</th>';
            $html .= '<th style="width:24%">Description</th>';
            $html .= '<th style="width:10%">Debit</th>';
            $html .= '<th style="width:10%">Credit</th>';
            $html .= '<th style="width:10%">Net Activity</th>';
            $html .= '<th style="width:13%">Balance</th>';
            $html .= '</tr></thead><tbody>';

            // Opening balance row
            $obCls = $ob < 0 ? 'neg' : '';
            $html .= '<tr class="ob">';
            $html .= '<td></td><td></td><td></td><td><strong>Opening Balance</strong></td>';
            $html .= '<td></td><td></td><td></td>';
            $html .= '<td class="num ' . $obCls . '"><strong>' . number_format($ob, 2) . '</strong></td>';
            $html .= '</tr>';

            // Transaction rows
            foreach ($section['transactions'] as $t) {
                $nc = $t['net_activity'] < 0 ? 'num neg' : 'num';
                $bc = $t['balance']      < 0 ? 'num neg' : 'num';
                $html .= '<tr>';
                $html .= '<td>' . date('d-m-Y', strtotime($t['date'])) . '</td>';
                $html .= '<td>' . htmlspecialchars($t['entry_number']       ?: '') . '</td>';
                $html .= '<td>' . htmlspecialchars($t['contra_ledger_name'] ?: '') . '</td>';
                $html .= '<td>' . htmlspecialchars($t['narration']          ?: '') . '</td>';
                $html .= '<td class="num">'     . ($t['debit_amount']  > 0 ? number_format($t['debit_amount'],  2) : '') . '</td>';
                $html .= '<td class="num">'     . ($t['credit_amount'] > 0 ? number_format($t['credit_amount'], 2) : '') . '</td>';
                $html .= '<td class="' . $nc . '">' . number_format($t['net_activity'], 2) . '</td>';
                $html .= '<td class="' . $bc . '">' . number_format($t['balance'],      2) . '</td>';
                $html .= '</tr>';
            }

            // Totals row
            $html .= '<tr class="tot">';
            $html .= '<td colspan="4" style="text-align:right">Totals for period</td>';
            $html .= '<td class="num">' . number_format($section['total_debit'],  2) . '</td>';
            $html .= '<td class="num">' . number_format($section['total_credit'], 2) . '</td>';
            $html .= '<td></td><td></td>';
            $html .= '</tr>';

            // Closing balance row
            $cbCls = $cb < 0 ? 'neg' : '';
            $html .= '<tr class="cb">';
            $html .= '<td></td><td></td><td></td><td><strong>Closing Balance</strong></td>';
            $html .= '<td></td><td></td><td></td>';
            $html .= '<td class="num ' . $cbCls . '"><strong>' . number_format($cb, 2) . '</strong></td>';
            $html .= '</tr>';

            $html .= '</tbody></table>';
        }

        $html .= '</body></html>';
        return $html;
    }

    // =========================================================================
    // sendNotificationEmail — IDENTICAL to CI job, zero changes
    // =========================================================================
    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());
        }
    }

    // =========================================================================
    // getAdminProfile — NOT included
    // CI job used this to read from admin_profile table for PDF header.
    // Webzash has no admin_profile table.
    // Org details (name, address) come from {prefix}settings in handle() above.
    // =========================================================================
}