<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\Response;
use Illuminate\Support\Facades\Storage;
use App\Jobs\GenerateLedgerStatementsPDFDynamic;
use Exception;

class DynamicDbController extends Controller
{
    private $db;
    public function fetchEntries(Request $request)
    {
        try {
            // Validate request parameters
            $validated = $request->validate([
                'db_host' => 'required|string',
                'db_port' => 'required|integer',
                'db_database' => 'required|string',
                'db_username' => 'required|string',
                'db_password' => 'nullable|string',
                'from_date' => 'required|date',
                'to_date' => 'required|date|after_or_equal:from_date'
            ]);

            // Create dynamic connection name
            $connectionName = 'dynamic_' . md5(json_encode($validated));

            // Set up dynamic database configuration
            Config::set("database.connections.{$connectionName}", [
                'driver' => 'mysql',
                'host' => $validated['db_host'],
                'port' => $validated['db_port'],
                'database' => $validated['db_database'],
                'username' => $validated['db_username'],
                'password' => $validated['db_password'] ?? '',
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'strict' => true,
                'engine' => null,
            ]);

            // Purge the connection to ensure fresh connection
            DB::purge($connectionName);
            $this->db = DB::connection($connectionName);

            // Test connection and fetch data
            $fromDate = $request->from_date;
            $toDate = $request->to_date;

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

            if (!$acYear) {
                return response()->json(['error' => 'No active financial year found'], 404);
            }

            $yearStartDate = $acYear->from_year_month;
            $yearEndDate = $acYear->to_year_month;

            // Set headers for CSV download
            $filename = 'all_ledger_statements_' . date('Y-m-d_His') . '.csv';
            $headers = [
                'Content-Type' => 'text/csv',
                'Content-Disposition' => "attachment; filename=\"{$filename}\"",
                'Pragma' => 'no-cache',
                'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
                'Expires' => '0'
            ];

            $callback = function () use ($fromDate, $toDate, $acYear, $yearStartDate) {
                $file = fopen('php://output', 'w');

                // Write main header
                fputcsv($file, ['General Ledger']);
                fputcsv($file, ['Financial Year: ' . date('d-m-Y', strtotime($acYear->from_year_month)) . ' TO ' . date('d-m-Y', strtotime($acYear->to_year_month))]);
                fputcsv($file, ['Report Period: ' . date('d-m-Y', strtotime($fromDate)) . ' TO ' . date('d-m-Y', strtotime($toDate))]);
                fputcsv($file, []); // Empty line

                // Get all ledgers grouped by hierarchy
                $ledgers = $this->getLedgersWithHierarchy();

                foreach ($ledgers as $ledger) {
                    $this->writeLedgerSection($file, $ledger, $fromDate, $toDate, $acYear->id, $yearStartDate);
                }

                fclose($file);
            };

            // Purge connection after use
            DB::purge($connectionName);

            return Response::stream($callback, 200, $headers);

            return response()->json([
                'success' => true,
                'message' => 'Data fetched successfully',
                'total_records' => $entries->count(),
                'data' => $entries
            ], 200);

        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error: ' . $e->getMessage(),
                'error_details' => $e->getTraceAsString()
            ], 500);
        }
    }

    public function generatePDF(Request $request)
    {
        try {
            // Validate request parameters
            $validated = $request->validate([
                'db_host' => 'required|string',
                'db_port' => 'required|integer',
                'db_database' => 'required|string',
                'db_username' => 'required|string',
                'db_password' => 'nullable|string',
                'from_date' => 'required|date',
                'to_date' => 'required|date|after_or_equal:from_date',
                'notification_email' => 'nullable|email'
            ]);
            print_r($dbConfig);
            die;
            // Test connection first
            $connectionName = 'test_' . md5(json_encode($validated));

            Config::set("database.connections.{$connectionName}", [
                'driver' => 'mysql',
                'host' => $validated['db_host'],
                'port' => $validated['db_port'],
                'database' => $validated['db_database'],
                'username' => $validated['db_username'],
                'password' => $validated['db_password'] ?? '',
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'strict' => true,
                'engine' => null,
            ]);

            // Test connection
            DB::purge($connectionName);
            $testDb = DB::connection($connectionName);
            $testDb->getPdo(); // This will throw exception if connection fails

            // Check if ac_year table exists
            $acYear = $testDb->table('ac_year')
                ->where('status', 1)
                ->where('has_closed', 0)
                ->first();

            if (!$acYear) {
                DB::purge($connectionName);
                return response()->json([
                    'success' => false,
                    'message' => 'No active financial year found in the database'
                ], 404);
            }

            // Cleanup test connection
            DB::purge($connectionName);

            // Prepare DB config
            $dbConfig = [
                'db_host' => $validated['db_host'],
                'db_port' => $validated['db_port'],
                'db_database' => $validated['db_database'],
                'db_username' => $validated['db_username'],
                'db_password' => $validated['db_password'] ?? ''
            ];
            print_r($dbConfig);
            die;
            // Dispatch job to queue
            $job = GenerateLedgerStatementsPDFDynamic::dispatch(
                $dbConfig,
                $validated['from_date'],
                $validated['to_date'],
                $validated['notification_email'] ?? null
            );

            return response()->json([
                'success' => true,
                'message' => 'PDF generation started in background. You will be notified when ready.',
                'status' => 'processing',
                'database' => $validated['db_database'],
                'date_range' => [
                    'from' => $validated['from_date'],
                    'to' => $validated['to_date']
                ]
            ], 202);

        } catch (\PDOException $e) {
            return response()->json([
                'success' => false,
                'message' => 'Database connection failed: ' . $e->getMessage()
            ], 500);
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Download generated PDF
     */
    public function downloadPDF($filename)
    {
        try {
            $path = "exports/ledger-statements/{$filename}";

            if (!Storage::exists($path)) {
                return response()->json([
                    'success' => false,
                    'message' => 'PDF file not found or expired'
                ], 404);
            }

            // Optional: Delete file after 24 hours
            $fileTime = Storage::lastModified($path);
            if (time() - $fileTime > 86400) { // 24 hours
                Storage::delete($path);
                return response()->json([
                    'success' => false,
                    'message' => 'PDF file has expired (older than 24 hours)'
                ], 410);
            }

            return Storage::download($path);

        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error downloading file: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Check if PDF is ready (polling endpoint)
     */
    public function checkPDFStatus(Request $request)
    {
        try {
            $validated = $request->validate([
                'database' => 'required|string',
                'from_date' => 'required|date',
                'to_date' => 'required|date'
            ]);

            // List files matching pattern
            $pattern = "ledger_statements_{$validated['database']}_*.pdf";
            $files = Storage::files('exports/ledger-statements');

            $matchingFiles = array_filter($files, function ($file) use ($pattern, $validated) {
                $filename = basename($file);
                // Check if filename matches database name
                return strpos($filename, "ledger_statements_{$validated['database']}_") === 0;
            });

            if (empty($matchingFiles)) {
                return response()->json([
                    'success' => true,
                    'status' => 'processing',
                    'message' => 'PDF is being generated...'
                ]);
            }

            // Get the most recent file
            usort($matchingFiles, function ($a, $b) {
                return Storage::lastModified($b) - Storage::lastModified($a);
            });

            $latestFile = basename($matchingFiles[0]);

            return response()->json([
                'success' => true,
                'status' => 'ready',
                'message' => 'PDF is ready for download',
                'filename' => $latestFile,
                'download_url' => url("/ledgers/download-pdf/{$latestFile}"),
                'file_size' => Storage::size($matchingFiles[0]),
                'generated_at' => date('Y-m-d H:i:s', Storage::lastModified($matchingFiles[0]))
            ]);

        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error: ' . $e->getMessage()
            ], 500);
        }
    }

    private function getLedgersWithHierarchy()
    {
        // Get all ledgers with their group information, ordered by group hierarchy
        return $this->db->table('ledgers as l')
            ->leftJoin('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();
    }

    private function writeLedgerSection($file, $ledger, $fromDate, $toDate, $acYearId, $yearStartDate)
    {
        // Write ledger header
        fputcsv($file, []);
        fputcsv($file, ['=' . str_repeat('=', 100)]); // Separator
        fputcsv($file, [$ledger->code . ' - ' . $ledger->name]);
        fputcsv($file, ['Group: ' . ($ledger->group_name ?? 'N/A')]);
        fputcsv($file, []); // Empty line

        // Write column headers
        fputcsv($file, [
            'Date',
            'Ref No',
            'Ledger',
            'Description',
            'Debit Amount',
            'Credit Amount',
            'Net Activity',
            'Balance Amount'
        ]);

        // Calculate opening balance
        $openingBalance = $this->calculateOpeningBalance($ledger->id, $acYearId, $yearStartDate, $fromDate);

        // Write opening balance row
        fputcsv($file, [
            '',
            '',
            '',
            'Opening Balance',
            '',
            '',
            '',
            number_format($openingBalance, 2)
        ]);

        // Get transactions for this ledger in date range
        $transactions = $this->getLedgerTransactions($ledger->id, $fromDate, $toDate);

        $runningBalance = $openingBalance;

        foreach ($transactions as $txn) {
            // Calculate net activity (Dr - Cr for debit balance ledgers, Cr - Dr for credit balance ledgers)
            $netActivity = $txn->debit_amount - $txn->credit_amount;
            $runningBalance += $netActivity;

            fputcsv($file, [
                date('d-m-Y', strtotime($txn->date)),
                $txn->entry_number,
                $txn->contra_ledger_name,
                $txn->narration,
                $txn->debit_amount > 0 ? number_format($txn->debit_amount, 2) : '',
                $txn->credit_amount > 0 ? number_format($txn->credit_amount, 2) : '',
                number_format($netActivity, 2),
                number_format($runningBalance, 2)
            ]);
        }

        // Write closing balance
        fputcsv($file, [
            '',
            '',
            '',
            'Closing Balance',
            '',
            '',
            '',
            number_format($runningBalance, 2)
        ]);

        fputcsv($file, []); // Empty line after ledger section
    }

    private function calculateOpeningBalance($ledgerId, $acYearId, $yearStartDate, $reportStartDate)
    {
        // Step 1: Get opening balance from ac_year_ledger_balance
        $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;
        }

        // Step 2: Add transactions from year start to report start date (excluding report start date)
        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();
    }
}