<?php

namespace App\Http\Controllers\Accounting;

use App\Http\Controllers\Controller;
use App\Http\Controllers\Concerns\ResolvesTenantHotel;
use Illuminate\Http\Request;
use App\Models\Account;
use App\Models\JournalEntry;
use App\Models\JournalEntryItem;
use App\Models\JournalTransaction;
use App\Models\Tag;
use App\Models\Budget;
use App\Models\AccountBalance;
use App\Models\Invoice;
use App\Models\Receipt;
use App\Models\PaymentVoucher;
use App\Models\JournalVoucher;
use App\Models\Accountability;
use App\Models\Reservation;
use App\Models\Order;
use App\Models\ServiceRequest;
use App\Models\InventoryOrder;
use App\Models\Expense;
use App\Models\ManufacturingOrder;
use App\Models\Staff;
use App\Models\Guest;
use App\Models\Contact;
use App\Services\Accounting\JournalEntryService;
use App\Services\Accounting\AccountingSyncService;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;
use Illuminate\Validation\Rule;
use Barryvdh\DomPDF\Facade\Pdf;

class AccountingController extends Controller
{
    use ResolvesTenantHotel;

    protected $journalEntryService;
    protected $syncService;

    public function __construct(JournalEntryService $journalEntryService, AccountingSyncService $syncService)
    {
        $this->journalEntryService = $journalEntryService;
        $this->syncService = $syncService;
    }

    /**
     * Get the current hotel ID from authenticated user
     */
    protected function getHotelId()
    {
        abort_unless(Auth::check(), 403, 'Unauthorized');

        return $this->tenantHotelIdOrAbort();
    }

    // ==================== DASHBOARD ====================

    /**
     * Display accounting dashboard
     */
    public function dashboard()
    {
        $hotelId = $this->getHotelId();
        
        // Get financial stats
        $stats = $this->getFinancialStats($hotelId);
        
        // Get recent journal entries
        $recentEntries = JournalEntry::with(['items.account', 'createdBy'])
            ->forHotel($hotelId)
            ->orderBy('date', 'desc')
            ->orderBy('created_at', 'desc')
            ->limit(5)
            ->get();
        
        // Get chart data
        $chartData = $this->getChartData($hotelId);
        
        // Get accounts with balances
        $accounts = Account::with('accountBalances')
            ->forHotel($hotelId)
            ->get();
        
        // Get system alerts
        $alerts = $this->getSystemAlerts($hotelId);
        
        // Get sync status for modules
        $syncStatus = $this->getModuleSyncStatus($hotelId);

        return view('accounting.dashboard', compact(
            'stats',
            'recentEntries',
            'chartData',
            'accounts',
            'alerts',
            'syncStatus'
        ));
    }

    // ==================== CHART OF ACCOUNTS ====================

    /**
     * Display chart of accounts
     */
    public function chartOfAccounts(Request $request)
    {
        $hotelId = $this->getHotelId();
        
        $query = Account::with(['parent', 'children', 'accountBalances' => function($q) {
                $q->orderBy('period_date', 'desc')->limit(1);
            }]);

        // Apply filters
        if ($request->filled('search')) {
            $search = $request->search;
            $query->whereNested(function($q) use ($search) {
                $q->where('name', 'like', "%{$search}%")
                  ->orWhere('code', 'like', "%{$search}%")
                  ->orWhere('description', 'like', "%{$search}%");
            });
        }

        if ($request->filled('type')) {
            $query->where('account_type', $request->type);
        }

        if ($request->filled('report_section')) {
            $query->where('report_section', $request->report_section);
        }

        if ($request->filled('status')) {
            $query->where('is_active', $request->status === 'active');
        }

        // Get root accounts (no parent)
        if ($request->boolean('root_only')) {
            $query->whereNull('parent_id');
        }

        $accounts = $query->orderBy('code')->get();

        // Build hierarchical tree
        $accountTree = $this->buildAccountTree($accounts);

        // Get statistics
        $stats = [
            'total_accounts' => Account::forHotel($hotelId)->count(),
            'posting_accounts' => Account::forHotel($hotelId)->postingAccounts()->count(),
            'header_accounts' => Account::forHotel($hotelId)->headerAccounts()->count(),
            'active_accounts' => Account::forHotel($hotelId)->active()->count(),
            'inactive_accounts' => Account::forHotel($hotelId)->where('is_active', false)->count(),
        ];

        // Get report sections for filter
        $reportSections = Account::forHotel($hotelId)
            ->whereNotNull('report_section')
            ->distinct()
            ->pluck('report_section');

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'accounts' => $accounts,
                'account_tree' => $accountTree,
                'stats' => $stats
            ]);
        }

        return view('accounting.chart-of-accounts', compact(
            'accounts',
            'accountTree',
            'stats',
            'reportSections'
        ));
    }

    /**
     * Public chart data endpoint for dashboard widgets.
     */
    public function chartData()
    {
        $hotelId = $this->getHotelId();

        return response()->json([
            'success' => true,
            'data' => $this->getChartData($hotelId),
        ]);
    }

    /**
     * Build hierarchical account tree
     */
    private function buildAccountTree($accounts, $parentId = null)
    {
        $tree = [];
        
        foreach ($accounts as $account) {
            if ($account->parent_id == $parentId) {
                $children = $this->buildAccountTree($accounts, $account->id);
                $tree[] = [
                    'account' => $account,
                    'children' => $children,
                    'level' => $parentId === null ? 0 : ($account->parent ? $account->parent->level + 1 : 0)
                ];
            }
        }
        
        return $tree;
    }

    /**
     * Store a new account
     */
    public function storeAccount(Request $request)
    {
        $hotelId = $this->getHotelId();
        $user = Auth::user();

        $validated = $request->validate([
            'code' => 'required|string|max:20',
            'name' => 'required|string|max:255',
            'l1_statement' => 'nullable|string|max:50',
            'l2_department' => 'nullable|string|max:50',
            'l3_group' => 'nullable|string|max:50',
            'l4_sub_group' => 'nullable|string|max:50',
            'account_type' => 'required|in:H,P',
            'normal_balance' => 'required|in:Dr,Cr',
            'category' => 'nullable|string|max:100',
            'pl_line' => 'nullable|string|max:100',
            'report_section' => 'nullable|string|max:50',
            'parent_id' => [
                'nullable',
                Rule::exists('accounts', 'id')->where(function ($query) use ($hotelId) {
                    $query->where(function ($q) use ($hotelId) {
                        $q->where('hotel_id', $hotelId)->orWhereNull('hotel_id');
                    });
                }),
            ],
            'is_active' => 'boolean',
            'notes' => 'nullable|string',
            'is_global' => 'boolean',
        ]);

        // Determine if this should be a global account
        // Only super_admin can create global accounts
        $isGlobal = $request->boolean('is_global') && $user->role === 'super_admin';
        
        // Check for unique code within scope
        // Use withoutGlobalScope to check both global and tenant accounts if necessary, 
        // but here we just need to ensure it's unique in the TARGET scope.
        $query = Account::withoutGlobalScopes()->where('code', $validated['code']);
        
        if ($isGlobal) {
            $query->whereNull('hotel_id');
        } else {
            $query->where('hotel_id', $hotelId);
        }

        if ($query->exists()) {
            return back()->withErrors(['code' => 'Account code must be unique within its scope.'])->withInput();
        }

        DB::transaction(function () use ($validated, $hotelId, $isGlobal) {
            $account = Account::create(array_merge($validated, [
                'hotel_id' => $isGlobal ? null : $hotelId
            ]));

            Log::info('Account created', [
                'account_id' => $account->id,
                'code' => $account->code,
                'name' => $account->name,
                'hotel_id' => $account->hotel_id,
                'is_global' => $isGlobal,
                'user_id' => Auth::id()
            ]);
        });

        return redirect()->route('accounting.chart-of-accounts')
            ->with('success', 'Account created successfully.');
    }

    /**
     * Show account details
     */
    public function showAccount(Account $account)
    {
        $hotelId = $this->getHotelId();
        abort_unless(Account::forHotel($hotelId)->where('id', $account->id)->exists(), 404);

        $account->load(['parent', 'children', 'tags']);

        // Get transactions
        $transactions = JournalEntryItem::where('account_id', $account->id)
            ->with(['journalEntry' => function($q) {
                $q->with('createdBy');
            }])
            ->orderBy('created_at', 'desc')
            ->paginate(20);

        // Get balances by period
        $balances = AccountBalance::where('account_id', $account->id)
            ->orderBy('period_date', 'desc')
            ->limit(12)
            ->get();

        // Calculate current balance
        $currentBalance = $account->balance;

        if (request()->wantsJson()) {
            return response()->json([
                'id' => $account->id,
                'code' => $account->code,
                'name' => $account->name,
                'account_type' => $account->account_type,
                'normal_balance' => $account->normal_balance,
                'category' => $account->category,
                'pl_line' => $account->pl_line,
                'report_section' => $account->report_section,
                'parent_id' => $account->parent_id,
                'parent' => $account->parent,
                'is_active' => $account->is_active,
                'notes' => $account->notes,
                'balance' => $account->balance,
            ]);
        }

        return redirect()->route('accounting.chart-of-accounts')
            ->with('info', 'Account detail is available via API/JSON response.');
    }

    /**
     * Edit account endpoint mapped in routes.
     */
    public function editAccount(Account $account)
    {
        return $this->showAccount($account);
    }

    /**
     * Update an account
     */
    public function updateAccount(Request $request, Account $account)
    {
        $hotelId = $this->getHotelId();
        abort_unless(Account::forHotel($hotelId)->where('id', $account->id)->exists(), 404);

        $validated = $request->validate([
            'code' => 'required|string|max:20',
            'name' => 'required|string|max:255',
            'l1_statement' => 'nullable|string|max:50',
            'l2_department' => 'nullable|string|max:50',
            'l3_group' => 'nullable|string|max:50',
            'l4_sub_group' => 'nullable|string|max:50',
            'account_type' => 'required|in:H,P',
            'normal_balance' => 'required|in:Dr,Cr',
            'category' => 'nullable|string|max:100',
            'pl_line' => 'nullable|string|max:100',
            'report_section' => 'nullable|string|max:50',
            'parent_id' => [
                'nullable',
                Rule::exists('accounts', 'id')->where(function ($query) use ($hotelId) {
                    $query->where(function ($q) use ($hotelId) {
                        $q->where('hotel_id', $hotelId)->orWhereNull('hotel_id');
                    });
                }),
            ],
            'is_active' => 'boolean',
            'notes' => 'nullable|string',
        ]);

        // Check for unique code within hotel/global scope
        $exists = Account::withoutGlobalScopes()
            ->where('hotel_id', $account->hotel_id)
            ->where('code', $validated['code'])
            ->where('id', '!=', $account->id)
            ->exists();

        if ($exists) {
            return back()->withErrors(['code' => 'Account code must be unique within this hotel.'])->withInput();
        }

        // Prevent circular reference
        if ($validated['parent_id'] == $account->id) {
            return back()->withErrors(['parent_id' => 'Cannot set account as its own parent.'])->withInput();
        }

        DB::transaction(function () use ($account, $validated) {
            $account->update($validated);

            Log::info('Account updated', [
                'account_id' => $account->id,
                'user_id' => Auth::id()
            ]);
        });

        return redirect()->route('accounting.chart-of-accounts')
            ->with('success', 'Account updated successfully.');
    }

    /**
     * Delete an account
     */
    public function destroyAccount(Account $account)
    {
        $hotelId = $this->getHotelId();
        abort_unless(Account::forHotel($hotelId)->where('id', $account->id)->exists(), 404);

        try {
            DB::transaction(function () use ($account) {
                // Check if account has journal entries
                if ($account->journalEntryItems()->exists()) {
                    throw new \Exception('Cannot delete account with existing journal entries.');
                }

                // Check if account has children
                if ($account->children()->exists()) {
                    throw new \Exception('Cannot delete account that has sub-accounts.');
                }

                $account->delete();

                Log::info('Account deleted', [
                    'account_id' => $account->id,
                    'code' => $account->code,
                    'name' => $account->name,
                    'user_id' => Auth::id()
                ]);
            });

            return redirect()->route('accounting.chart-of-accounts')
                ->with('success', 'Account deleted successfully.');

        } catch (\Exception $e) {
            return redirect()->route('accounting.chart-of-accounts')
                ->with('error', $e->getMessage());
        }
    }

    // ==================== JOURNAL ENTRIES ====================

    /**
     * Display journal entries
     */
    public function journalEntries(Request $request)
    {
        $hotelId = $this->getHotelId();

        $query = JournalEntry::with(['items.account', 'createdBy']);

        // Apply filters
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('date_from')) {
            $query->where('date', '>=', $request->date_from);
        }

        if ($request->filled('date_to')) {
            $query->where('date', '<=', $request->date_to);
        }

        if ($request->filled('account_id')) {
            $query->whereHas('items', function($q) use ($request) {
                $q->where('account_id', $request->account_id);
            });
        }

        if ($request->filled('search')) {
            $search = $request->search;
            $query->whereNested(function($q) use ($search) {
                $q->where('entry_number', 'like', "%{$search}%")
                  ->orWhere('description', 'like', "%{$search}%")
                  ->orWhere('reference', 'like', "%{$search}%");
            });
        }

        $journalEntries = $query->orderBy('date', 'desc')
            ->orderBy('created_at', 'desc')
            ->paginate(20)
            ->withQueryString();

        // Calculate totals
        $totals = [
            'debit' => $journalEntries->sum(function($entry) {
                return $entry->items->sum('debit');
            }),
            'credit' => $journalEntries->sum(function($entry) {
                return $entry->items->sum('credit');
            })
        ];

        // Get accounts for filter
        $accounts = Account::postingAccounts()
            ->orderBy('code')
            ->get(['id', 'code', 'name']);

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'data' => $journalEntries,
                'totals' => $totals
            ]);
        }

        return view('accounting.journal-entries', compact(
            'journalEntries',
            'accounts',
            'totals'
        ));
    }

    /**
     * Show form to create journal entry
     */
    public function createJournalEntry()
    {
        $hotelId = $this->getHotelId();

        $accounts = Account::forHotel($hotelId)
            ->postingAccounts()
            ->active()
            ->orderBy('code')
            ->get();

        $templates = $this->getJournalTemplates();

        return redirect()->route('accounting.journal-entries');
    }

    /**
     * Store a new journal entry
     */
    public function storeJournalEntry(Request $request)
    {
        $hotelId = $this->getHotelId();
        $user = Auth::user();

        $validated = $request->validate([
            'date' => 'required|date',
            'description' => 'required|string',
            'reference' => 'nullable|string|max:255',
            'items' => 'required|array|min:2',
            'items.*.account_id' => [
                'required',
                Rule::exists('accounts', 'id')->where(function ($query) use ($hotelId) {
                    $query->where(function ($q) use ($hotelId) {
                        $q->where('hotel_id', $hotelId)->orWhereNull('hotel_id');
                    });
                }),
            ],
            'items.*.debit' => 'nullable|numeric|min:0',
            'items.*.credit' => 'nullable|numeric|min:0',
            'items.*.description' => 'nullable|string',
            'is_global' => 'boolean',
        ]);

        // Validate that total debits equal total credits
        $totalDebit = collect($validated['items'])->sum('debit');
        $totalCredit = collect($validated['items'])->sum('credit');

        if (abs($totalDebit - $totalCredit) > 0.01) {
            return back()->withErrors(['items' => 'Total debits must equal total credits.'])->withInput();
        }

        // Validate that each item has either debit or credit
        foreach ($validated['items'] as $index => $item) {
            if (($item['debit'] > 0 && $item['credit'] > 0) || 
                ($item['debit'] == 0 && $item['credit'] == 0)) {
                return back()->withErrors(["items.$index" => 'Each item must have either debit or credit amount (not both).'])->withInput();
            }
        }

        // Determine if this should be a global journal
        $isGlobal = $request->boolean('is_global') && $user->role === 'super_admin';

        DB::transaction(function () use ($validated, $hotelId, $totalDebit, $isGlobal) {
            $journalEntry = JournalEntry::create([
                'hotel_id' => $isGlobal ? null : $hotelId,
                'date' => $validated['date'],
                'entry_number' => JournalEntry::generateEntryNumber($isGlobal ? null : $hotelId),
                'description' => $validated['description'],
                'reference' => $validated['reference'] ?? null,
                'total_amount' => $totalDebit,
                'status' => JournalEntry::STATUS_DRAFT,
                'created_by' => Auth::id(),
            ]);

            foreach ($validated['items'] as $item) {
                JournalEntryItem::create([
                    'hotel_id' => $journalEntry->hotel_id,
                    'journal_entry_id' => $journalEntry->id,
                    'account_id' => $item['account_id'],
                    'debit' => $item['debit'] ?? 0,
                    'credit' => $item['credit'] ?? 0,
                    'description' => $item['description'] ?? null,
                ]);
            }

            Log::info('Journal entry created', [
                'entry_id' => $journalEntry->id,
                'entry_number' => $journalEntry->entry_number,
                'hotel_id' => $journalEntry->hotel_id,
                'is_global' => $isGlobal,
                'user_id' => Auth::id()
            ]);
        });

        return redirect()->route('accounting.journal-entries')
            ->with('success', 'Journal entry created successfully.');
    }

    /**
     * Create journal entry from template
     */
    public function createFromTemplate(Request $request, $templateId)
    {
        $hotelId = $this->getHotelId();

        try {
            $journalEntry = $this->journalEntryService->createFromTemplate(
                $templateId,
                $request->all(),
                $hotelId
            );

            return redirect()->route('accounting.journal-entries.show', $journalEntry)
                ->with('success', 'Journal entry created from template successfully.');

        } catch (\Exception $e) {
            Log::error('Failed to create journal entry from template', [
                'template_id' => $templateId,
                'error' => $e->getMessage()
            ]);

            return back()->with('error', 'Failed to create journal entry: ' . $e->getMessage());
        }
    }

    /**
     * Show journal entry details
     */
    public function showJournalEntry(JournalEntry $journalEntry)
    {
        $hotelId = $this->getHotelId();
        abort_unless(JournalEntry::forHotel($hotelId)->where('id', $journalEntry->id)->exists(), 404);

        $journalEntry->load(['items.account', 'createdBy']);

        $itemsByType = [
            'debit' => $journalEntry->items->where('debit', '>', 0),
            'credit' => $journalEntry->items->where('credit', '>', 0)
        ];

        if (request()->wantsJson()) {
            return response()->json([
                'id' => $journalEntry->id,
                'entry_number' => $journalEntry->entry_number,
                'date' => $journalEntry->date->format('Y-m-d'),
                'description' => $journalEntry->description,
                'reference' => $journalEntry->reference,
                'status' => $journalEntry->status,
                'items' => $journalEntry->items->map(function($item) {
                    return [
                        'id' => $item->id,
                        'account_id' => $item->account_id,
                        'account' => $item->account,
                        'debit' => $item->debit,
                        'credit' => $item->credit,
                        'description' => $item->description,
                    ];
                }),
            ]);
        }

        return redirect()->route('accounting.journal-entries');
    }

    /**
     * Show form to edit journal entry
     */
    public function editJournalEntry(JournalEntry $journalEntry)
    {
        $hotelId = $this->getHotelId();
        abort_unless(JournalEntry::forHotel($hotelId)->where('id', $journalEntry->id)->exists(), 404);

        if (!$journalEntry->canBeEdited()) {
            return redirect()->route('accounting.journal-entries')
                ->with('error', 'This journal entry cannot be edited.');
        }

        $accounts = Account::forHotel($hotelId)
            ->postingAccounts()
            ->active()
            ->orderBy('code')
            ->get();

        $journalEntry->load('items');

        if (request()->wantsJson()) {
            return response()->json([
                'id' => $journalEntry->id,
                'date' => $journalEntry->date->format('Y-m-d'),
                'description' => $journalEntry->description,
                'reference' => $journalEntry->reference,
                'items' => $journalEntry->items->map(function($item) {
                    return [
                        'id' => $item->id,
                        'account_id' => $item->account_id,
                        'debit' => $item->debit,
                        'credit' => $item->credit,
                        'description' => $item->description,
                    ];
                }),
            ]);
        }

        return redirect()->route('accounting.journal-entries');
    }

    /**
     * Update a journal entry
     */
    public function updateJournalEntry(Request $request, JournalEntry $journalEntry)
    {
        $hotelId = $this->getHotelId();
        abort_unless(JournalEntry::forHotel($hotelId)->where('id', $journalEntry->id)->exists(), 404);

        if (!$journalEntry->canBeEdited()) {
            return redirect()->route('accounting.journal-entries')
                ->with('error', 'This journal entry cannot be edited.');
        }

        $validated = $request->validate([
            'date' => 'required|date',
            'description' => 'required|string',
            'reference' => 'nullable|string|max:255',
            'items' => 'required|array|min:2',
            'items.*.id' => [
                'nullable',
                Rule::exists('journal_entry_items', 'id')->where(function ($query) use ($journalEntry, $hotelId) {
                    $query->where('journal_entry_id', $journalEntry->id)
                        ->where('hotel_id', $hotelId);
                }),
            ],
            'items.*.account_id' => [
                'required',
                Rule::exists('accounts', 'id')->where(function ($query) use ($journalEntry) {
                    $query->where(function ($q) use ($journalEntry) {
                        $q->where('hotel_id', $journalEntry->hotel_id)->orWhereNull('hotel_id');
                    });
                }),
            ],
            'items.*.debit' => 'nullable|numeric|min:0',
            'items.*.credit' => 'nullable|numeric|min:0',
            'items.*.description' => 'nullable|string',
        ]);

        // Validate that total debits equal total credits
        $totalDebit = collect($validated['items'])->sum('debit');
        $totalCredit = collect($validated['items'])->sum('credit');

        if (abs($totalDebit - $totalCredit) > 0.01) {
            return back()->withErrors(['items' => 'Total debits must equal total credits.'])->withInput();
        }

        DB::transaction(function () use ($journalEntry, $validated, $totalDebit) {
            // Update header
            $journalEntry->update([
                'date' => $validated['date'],
                'description' => $validated['description'],
                'reference' => $validated['reference'] ?? null,
                'total_amount' => $totalDebit,
            ]);

            // Get existing item IDs
            $existingIds = $journalEntry->items->pluck('id')->toArray();
            $updatedIds = [];

            foreach ($validated['items'] as $itemData) {
                if (isset($itemData['id']) && in_array($itemData['id'], $existingIds)) {
                    // Update existing item
                    $item = JournalEntryItem::find($itemData['id']);
                    $item->update([
                        'account_id' => $itemData['account_id'],
                        'debit' => $itemData['debit'] ?? 0,
                        'credit' => $itemData['credit'] ?? 0,
                        'description' => $itemData['description'] ?? null,
                    ]);
                    $updatedIds[] = $itemData['id'];
                } else {
                    // Create new item
                    $newItem = JournalEntryItem::create([
                        'hotel_id' => $journalEntry->hotel_id,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $itemData['account_id'],
                        'debit' => $itemData['debit'] ?? 0,
                        'credit' => $itemData['credit'] ?? 0,
                        'description' => $itemData['description'] ?? null,
                    ]);
                    $updatedIds[] = $newItem->id;
                }
            }

            // Delete removed items
            $itemsToDelete = array_diff($existingIds, $updatedIds);
            if (!empty($itemsToDelete)) {
                JournalEntryItem::whereIn('id', $itemsToDelete)->delete();
            }
        });

        return redirect()->route('accounting.journal-entries.show', $journalEntry)
            ->with('success', 'Journal entry updated successfully.');
    }

    /**
     * Post a journal entry
     */
    public function postJournalEntry(JournalEntry $journalEntry)
    {
        $hotelId = $this->getHotelId();
        abort_unless(JournalEntry::forHotel($hotelId)->where('id', $journalEntry->id)->exists(), 404);

        try {
            DB::transaction(function () use ($journalEntry) {
                if (!$journalEntry->post()) {
                    throw new \Exception('Failed to post journal entry.');
                }

                Log::info('Journal entry posted', [
                    'entry_id' => $journalEntry->id,
                    'entry_number' => $journalEntry->entry_number,
                    'user_id' => Auth::id()
                ]);
            });

            return redirect()->route('accounting.journal-entries.show', $journalEntry)
                ->with('success', 'Journal entry posted successfully.');

        } catch (\Exception $e) {
            return back()->with('error', $e->getMessage());
        }
    }

    /**
     * Reverse a journal entry
     */
    public function reverseJournalEntry(Request $request, JournalEntry $journalEntry)
    {
        $hotelId = $this->getHotelId();
        abort_unless(JournalEntry::forHotel($hotelId)->where('id', $journalEntry->id)->exists(), 404);

        $validated = $request->validate([
            'reversal_date' => 'nullable|date',
        ]);

        try {
            DB::transaction(function () use ($journalEntry, $validated) {
                $reversalDate = isset($validated['reversal_date']) 
                    ? Carbon::parse($validated['reversal_date']) 
                    : now();

                $reversal = $journalEntry->reverse($reversalDate);

                Log::info('Journal entry reversed', [
                    'original_entry_id' => $journalEntry->id,
                    'reversal_entry_id' => $reversal->id,
                    'user_id' => Auth::id()
                ]);
            });

            return redirect()->route('accounting.journal-entries')
                ->with('success', 'Journal entry reversed successfully.');

        } catch (\Exception $e) {
            return back()->with('error', $e->getMessage());
        }
    }

    /**
     * Delete a journal entry
     */
    public function destroyJournalEntry(JournalEntry $journalEntry)
    {
        $hotelId = $this->getHotelId();
        abort_unless(JournalEntry::forHotel($hotelId)->where('id', $journalEntry->id)->exists(), 404);

        try {
            DB::transaction(function () use ($journalEntry) {
                if (!$journalEntry->canBeDeleted()) {
                    throw new \Exception('Cannot delete a posted journal entry.');
                }

                $journalEntry->items()->delete();
                $journalEntry->delete();

                Log::info('Journal entry deleted', [
                    'entry_id' => $journalEntry->id,
                    'entry_number' => $journalEntry->entry_number,
                    'user_id' => Auth::id()
                ]);
            });

            return redirect()->route('accounting.journal-entries')
                ->with('success', 'Journal entry deleted successfully.');

        } catch (\Exception $e) {
            return back()->with('error', $e->getMessage());
        }
    }

    // ==================== REPORTS ====================

    /**
     * Generate trial balance
     */
    public function trialBalance(Request $request)
    {
        $hotelId = $this->getHotelId();
        
        $asOfDate = $request->get('as_of_date', now()->format('Y-m-d'));
        $asOfDate = Carbon::parse($asOfDate);

        // Get all posting accounts with their balances up to asOfDate
        $accounts = Account::forHotel($hotelId)
            ->postingAccounts()
            ->active()
            ->with(['accountBalances' => function($q) use ($asOfDate) {
                $q->where('period_date', '<=', $asOfDate->format('Y-m-01'))
                  ->orderBy('period_date', 'desc');
            }])
            ->orderBy('code')
            ->get();

        $trialBalance = [];
        $totalDebits = 0;
        $totalCredits = 0;

        foreach ($accounts as $account) {
            $balance = $account->balance;
            
            if (abs($balance) > 0.01) {
                $debit = $account->normal_balance === 'Dr' && $balance > 0 ? $balance : 0;
                $credit = $account->normal_balance === 'Cr' && $balance > 0 ? $balance : 0;
                
                // For accounts with negative balance, flip the side
                if ($balance < 0) {
                    $debit = $account->normal_balance === 'Cr' ? abs($balance) : 0;
                    $credit = $account->normal_balance === 'Dr' ? abs($balance) : 0;
                }

                $trialBalance[] = [
                    'code' => $account->code,
                    'name' => $account->name,
                    'debit' => $debit,
                    'credit' => $credit,
                ];

                $totalDebits += $debit;
                $totalCredits += $credit;
            }
        }

        // Verify balance
        $isBalanced = abs($totalDebits - $totalCredits) < 0.01;

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'data' => $trialBalance,
                'totals' => [
                    'debit' => $totalDebits,
                    'credit' => $totalCredits,
                ],
                'is_balanced' => $isBalanced,
                'as_of_date' => $asOfDate->format('Y-m-d')
            ]);
        }

        return view('accounting.reports.trial-balance', compact(
            'trialBalance',
            'totalDebits',
            'totalCredits',
            'isBalanced',
            'asOfDate'
        ));
    }

    /**
     * Generate profit and loss statement
     */
    public function profitLoss(Request $request)
    {
        $hotelId = $this->getHotelId();

        $startDate = $request->get('start_date', now()->startOfMonth()->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->endOfMonth()->format('Y-m-d'));
        $startDate = Carbon::parse($startDate);
        $endDate = Carbon::parse($endDate);

        // Get all revenue accounts
        $revenueAccounts = Account::forHotel($hotelId)
            ->where('normal_balance', 'Cr')
            ->whereIn('report_section', ['REVENUE', 'REVENUE_DEPT'])
            ->orderBy('code')
            ->get();

        // Get all expense accounts
        $expenseAccounts = Account::forHotel($hotelId)
            ->where('normal_balance', 'Dr')
            ->whereIn('report_section', ['COGS', 'COGS_DEPT', 'OPEX'])
            ->orderBy('code')
            ->get();

        // Calculate revenue
        $revenues = [];
        $totalRevenue = 0;

        foreach ($revenueAccounts as $account) {
            $balance = $this->getAccountBalanceForPeriod($account, $startDate, $endDate);
            if (abs($balance) > 0.01) {
                $revenues[] = [
                    'account' => $account,
                    'balance' => $balance,
                ];
                $totalRevenue += $balance;
            }
        }

        // Calculate expenses
        $expenses = [];
        $totalExpenses = 0;

        foreach ($expenseAccounts as $account) {
            $balance = $this->getAccountBalanceForPeriod($account, $startDate, $endDate);
            if (abs($balance) > 0.01) {
                $expenses[] = [
                    'account' => $account,
                    'balance' => $balance,
                ];
                $totalExpenses += $balance;
            }
        }

        $netIncome = $totalRevenue - $totalExpenses;

        // Group by P&L line
        $groupedRevenue = $this->groupByPlLine($revenues);
        $groupedExpenses = $this->groupByPlLine($expenses);

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'revenues' => $revenues,
                'total_revenue' => $totalRevenue,
                'expenses' => $expenses,
                'total_expenses' => $totalExpenses,
                'net_income' => $netIncome,
                'period' => [
                    'start' => $startDate->format('Y-m-d'),
                    'end' => $endDate->format('Y-m-d')
                ]
            ]);
        }

        return view('accounting.reports.profit-loss', compact(
            'revenues',
            'expenses',
            'groupedRevenue',
            'groupedExpenses',
            'totalRevenue',
            'totalExpenses',
            'netIncome',
            'startDate',
            'endDate'
        ));
    }

    /**
     * Generate balance sheet
     */
    public function balanceSheet(Request $request)
    {
        $hotelId = $this->getHotelId();

        $asOfDate = $request->get('as_of_date', now()->format('Y-m-d'));
        $asOfDate = Carbon::parse($asOfDate);

        // Get asset accounts
        $assetAccounts = Account::forHotel($hotelId)
            ->where('report_section', 'BS')
            ->where('normal_balance', 'Dr')
            ->orderBy('code')
            ->get();

        // Get liability accounts
        $liabilityAccounts = Account::forHotel($hotelId)
            ->where('report_section', 'BS')
            ->where('normal_balance', 'Cr')
            ->whereIn('category', ['Liabilities - LT Loans', 'Liabilities - Current', 'Liabilities - Tax'])
            ->orderBy('code')
            ->get();

        // Get equity accounts
        $equityAccounts = Account::forHotel($hotelId)
            ->where('report_section', 'BS')
            ->where('normal_balance', 'Cr')
            ->where('category', 'Equity')
            ->orderBy('code')
            ->get();

        // Calculate balances
        $assets = [];
        $totalAssets = 0;

        foreach ($assetAccounts as $account) {
            $balance = $account->balance;
            if (abs($balance) > 0.01) {
                $assets[] = [
                    'account' => $account,
                    'balance' => $balance,
                ];
                $totalAssets += $balance;
            }
        }

        $liabilities = [];
        $totalLiabilities = 0;

        foreach ($liabilityAccounts as $account) {
            $balance = $account->balance;
            if (abs($balance) > 0.01) {
                $liabilities[] = [
                    'account' => $account,
                    'balance' => $balance,
                ];
                $totalLiabilities += $balance;
            }
        }

        $equities = [];
        $totalEquity = 0;

        foreach ($equityAccounts as $account) {
            $balance = $account->balance;
            if (abs($balance) > 0.01) {
                $equities[] = [
                    'account' => $account,
                    'balance' => $balance,
                ];
                $totalEquity += $balance;
            }
        }

        // Verify balance sheet equation
        $isBalanced = abs($totalAssets - ($totalLiabilities + $totalEquity)) < 0.01;

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'assets' => $assets,
                'total_assets' => $totalAssets,
                'liabilities' => $liabilities,
                'total_liabilities' => $totalLiabilities,
                'equity' => $equities,
                'total_equity' => $totalEquity,
                'is_balanced' => $isBalanced,
                'as_of_date' => $asOfDate->format('Y-m-d')
            ]);
        }

        return view('accounting.reports.balance-sheet', compact(
            'assets',
            'liabilities',
            'equities',
            'totalAssets',
            'totalLiabilities',
            'totalEquity',
            'isBalanced',
            'asOfDate'
        ));
    }

    /**
     * Generate general ledger
     */
    public function generalLedger(Request $request)
    {
        $hotelId = $this->getHotelId();

        $startDate = $request->get('start_date', now()->startOfMonth()->format('Y-m-d'));
        $endDate = $request->get('end_date', now()->endOfMonth()->format('Y-m-d'));
        $accountId = $request->get('account_id');

        $startDate = Carbon::parse($startDate);
        $endDate = Carbon::parse($endDate);

        $query = JournalEntryItem::whereHas('journalEntry', function($q) use ($hotelId, $startDate, $endDate) {
            $q->forHotel($hotelId)
              ->where('status', JournalEntry::STATUS_POSTED)
              ->whereBetween('date', [$startDate, $endDate]);
        })->with(['journalEntry', 'account']);

        if ($accountId) {
            $query->where('account_id', $accountId);
        }

        $items = $query->orderBy('journal_entry_id')
            ->orderBy('id')
            ->get();

        // Group by account
        $ledger = [];
        foreach ($items->groupBy('account_id') as $accountId => $accountItems) {
            $account = $accountItems->first()->account;
            $openingDebit = JournalEntryItem::where('account_id', $account->id)
                ->whereHas('journalEntry', function ($q) use ($hotelId, $startDate) {
                    $q->forHotel($hotelId)
                        ->where('status', JournalEntry::STATUS_POSTED)
                        ->where('date', '<', $startDate);
                })
                ->sum('debit');

            $openingCredit = JournalEntryItem::where('account_id', $account->id)
                ->whereHas('journalEntry', function ($q) use ($hotelId, $startDate) {
                    $q->forHotel($hotelId)
                        ->where('status', JournalEntry::STATUS_POSTED)
                        ->where('date', '<', $startDate);
                })
                ->sum('credit');

            $runningBalance = $account->normal_balance === 'Dr'
                ? ($openingDebit - $openingCredit)
                : ($openingCredit - $openingDebit);
            $transactions = [];

            foreach ($accountItems as $item) {
                if ($account->normal_balance === 'Dr') {
                    $runningBalance += ($item->debit - $item->credit);
                } else {
                    $runningBalance += ($item->credit - $item->debit);
                }

                $transactions[] = [
                    'date' => $item->journalEntry->date,
                    'entry_number' => $item->journalEntry->entry_number,
                    'description' => $item->description ?: $item->journalEntry->description,
                    'debit' => $item->debit,
                    'credit' => $item->credit,
                    'balance' => $runningBalance,
                ];
            }

            $ledger[] = [
                'account' => $account,
                'transactions' => $transactions,
                'opening_balance' => $account->normal_balance === 'Dr'
                    ? ($openingDebit - $openingCredit)
                    : ($openingCredit - $openingDebit),
                'closing_balance' => $runningBalance,
            ];
        }

        $accounts = Account::forHotel($hotelId)
            ->postingAccounts()
            ->active()
            ->orderBy('code')
            ->get(['id', 'code', 'name']);

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'ledger' => $ledger,
                'period' => [
                    'start' => $startDate->format('Y-m-d'),
                    'end' => $endDate->format('Y-m-d')
                ]
            ]);
        }

        return view('accounting.reports.general-ledger', compact(
            'ledger',
            'accounts',
            'startDate',
            'endDate',
            'accountId'
        ));
    }

    // ==================== TAGS ====================

    /**
     * Get all tags
     */
    public function getTags(Request $request)
    {
        $hotelId = $this->getHotelId();

        $query = Tag::forHotel($hotelId);

        if ($request->filled('group')) {
            $query->byGroup($request->group);
        }

        $tags = $query->orderBy('name')->get();

        return response()->json([
            'success' => true,
            'tags' => $tags
        ]);
    }

    /**
     * Store a new tag
     */
    public function storeTag(Request $request)
    {
        $hotelId = $this->getHotelId();

        $validated = $request->validate([
            'name' => 'required|string|max:100',
            'group' => 'nullable|string|max:100',
        ]);

        $tag = Tag::create(array_merge($validated, [
            'hotel_id' => $hotelId
        ]));

        return response()->json([
            'success' => true,
            'message' => 'Tag created successfully.',
            'tag' => $tag
        ]);
    }

    /**
     * Assign tags to account
     */
    public function assignTags(Request $request, Account $account)
    {
        $hotelId = $this->getHotelId();
        abort_unless(Account::forHotel($hotelId)->where('id', $account->id)->exists(), 404);

        $validated = $request->validate([
            'tag_ids' => 'required|array',
            'tag_ids.*' => [
                Rule::exists('tags', 'id')->where(function ($query) use ($hotelId) {
                    $query->where('hotel_id', $hotelId);
                }),
            ],
        ]);

        $validTagIds = Tag::forHotel($hotelId)
            ->whereIn('id', $validated['tag_ids'])
            ->pluck('id')
            ->toArray();

        if (count($validTagIds) !== count($validated['tag_ids'])) {
            return response()->json([
                'success' => false,
                'message' => 'One or more tags are invalid for this hotel.',
            ], 422);
        }

        $account->tags()->sync($validTagIds);

        return response()->json([
            'success' => true,
            'message' => 'Tags assigned successfully.'
        ]);
    }

    // ==================== BUDGETS ====================

    /**
     * Get budgets
     */
    public function budgets(Request $request)
    {
        $hotelId = $this->getHotelId();

        $query = Budget::forHotel($hotelId)
            ->with('account');

        if ($request->filled('fiscal_year')) {
            $query->forYear($request->fiscal_year);
        }

        if ($request->filled('period')) {
            $query->byPeriod($request->period);
        }

        if ($request->filled('account_id')) {
            $query->where('account_id', $request->account_id);
        }

        $budgets = $query->orderBy('fiscal_year', 'desc')
            ->orderBy('period')
            ->get();

        // Calculate summary statistics
        $totalBudget = $budgets->sum('amount');
        $totalSpent = $budgets->sum(function ($budget) {
            return $budget->spent_amount;
        });
        $totalRemaining = $totalBudget - $totalSpent;
        $utilization = $totalBudget > 0 ? round(($totalSpent / $totalBudget) * 100, 1) : 0;

        // Get accounts for filter dropdown
        $accounts = Account::forHotel($hotelId)
            ->where('is_active', true)
            ->orderBy('code')
            ->get(['id', 'code', 'name']);

        // If no accounts exist, create a placeholder
        if ($accounts->isEmpty()) {
            $accounts = collect([]);
        }

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'budgets' => $budgets,
                'summary' => [
                    'total_budget' => $totalBudget,
                    'total_spent' => $totalSpent,
                    'total_remaining' => $totalRemaining,
                    'utilization' => $utilization
                ]
            ]);
        }

        return view('accounting.budgets', compact(
            'budgets',
            'accounts',
            'totalBudget',
            'totalSpent',
            'totalRemaining',
            'utilization'
        ));
    }

    /**
     * Get single budget endpoint mapped in routes.
     */
    public function getBudget($id)
    {
        $hotelId = $this->getHotelId();
        $budget = Budget::forHotel($hotelId)->with('account')->findOrFail($id);

        return response()->json([
            'success' => true,
            'budget' => $budget,
        ]);
    }

    /**
     * Store a new budget
     */
    public function storeBudget(Request $request)
    {
        $hotelId = $this->getHotelId();

        $validated = $request->validate([
            'account_id' => [
                'required',
                Rule::exists('accounts', 'id')->where(function ($query) use ($hotelId) {
                    $query->where(function ($q) use ($hotelId) {
                        $q->where('hotel_id', $hotelId)->orWhereNull('hotel_id');
                    });
                }),
            ],
            'fiscal_year' => 'required|integer|min:2000|max:2100',
            'amount' => 'required|numeric|min:0',
            'period' => 'required|in:monthly,quarterly,yearly',
            'notes' => 'nullable|string',
        ]);

        // Check for existing budget
        $exists = Budget::forHotel($hotelId)
            ->where('account_id', $validated['account_id'])
            ->where('fiscal_year', $validated['fiscal_year'])
            ->where('period', $validated['period'])
            ->exists();

        if ($exists) {
            return response()->json([
                'success' => false,
                'message' => 'A budget already exists for this account, year, and period.'
            ], 422);
        }

        DB::transaction(function () use ($validated, $hotelId) {
            Budget::create(array_merge($validated, [
                'hotel_id' => $hotelId,
                'created_by' => Auth::id()
            ]));
        });

        return response()->json([
            'success' => true,
            'message' => 'Budget created successfully.'
        ]);
    }

    /**
     * Update a budget
     */
    public function updateBudget(Request $request, Budget $budget)
    {
        $hotelId = $this->getHotelId();
        abort_unless(Budget::forHotel($hotelId)->where('id', $budget->id)->exists(), 404);

        $validated = $request->validate([
            'amount' => 'required|numeric|min:0',
            'notes' => 'nullable|string',
        ]);

        $budget->update($validated);

        return response()->json([
            'success' => true,
            'message' => 'Budget updated successfully.'
        ]);
    }

    /**
     * Edit budget endpoint mapped in routes.
     */
    public function editBudget($id)
    {
        return $this->getBudget($id);
    }

    /**
     * Delete budget endpoint mapped in routes.
     */
    public function deleteBudget($id)
    {
        $hotelId = $this->getHotelId();
        $budget = Budget::forHotel($hotelId)->findOrFail($id);
        $budget->delete();

        return response()->json([
            'success' => true,
            'message' => 'Budget deleted successfully.',
        ]);
    }

    /**
     * Get budget variance report
     */
    public function budgetVariance(Request $request)
    {
        $hotelId = $this->getHotelId();

        $fiscalYear = $request->get('fiscal_year', now()->year);
        $period = $request->get('period', 'monthly');

        $budgets = Budget::forHotel($hotelId)
            ->forYear($fiscalYear)
            ->byPeriod($period)
            ->with('account')
            ->get();

        $varianceReport = [];
        $totalBudget = 0;
        $totalActual = 0;
        $totalVariance = 0;

        foreach ($budgets as $budget) {
            $actual = $budget->actual_amount;
            $variance = $budget->variance;
            $variancePercent = $budget->variance_percentage;

            $varianceReport[] = [
                'account' => $budget->account,
                'budget' => $budget,
                'actual' => $actual,
                'variance' => $variance,
                'variance_percent' => $variancePercent,
            ];

            $totalBudget += $budget->amount;
            $totalActual += $actual;
            $totalVariance += $variance;
        }

        if ($request->wantsJson()) {
            return response()->json([
                'success' => true,
                'data' => $varianceReport,
                'totals' => [
                    'budget' => $totalBudget,
                    'actual' => $totalActual,
                    'variance' => $totalVariance,
                    'variance_percent' => $totalBudget > 0 ? ($totalVariance / $totalBudget) * 100 : 0
                ]
            ]);
        }

        return redirect()->route('accounting.budgets');
    }

    // ==================== SYNC FUNCTIONS ====================
    /**
     * Sync payments to accounting (Cash/Bank entries)
     */
    public function syncPayments()
    {
        $hotelId = $this->getHotelId();
        $syncedCount = 0;
        $totalAmount = 0;
        $errors = [];

        try {
            DB::beginTransaction();

            $payments = \App\Models\Payment::where('hotel_id', $hotelId)
                ->where('status', 'completed')
                ->where('accounting_synced', false)
                ->get();

            foreach ($payments as $payment) {
                try {
                    // 11251: Petty Cash-General, 11241: Bank, 11254: MTN MM
                    $map = [
                        'cash' => '11251',
                        'credit_card' => '11241',
                        'debit_card' => '11241',
                        'mobile_mm' => '11254',
                        'mobile_money' => '11254',
                        'bank_transfer' => '11241',
                    ];
                    $accountCode = $map[strtolower($payment->payment_method)] ?? '11251';
                    
                    $cashAccount = Account::forHotel($hotelId)->where('code', $accountCode)->first();
                    $receivableAccount = Account::forHotel($hotelId)->where('code', '11221')->first();

                    if (!$cashAccount || !$receivableAccount) {
                        throw new \Exception("Required accounts (Cash/Receivables) not found for sync.");
                    }

                    // Check for existing entry to prevent duplication
                    $existingEntry = JournalEntry::where('module_source', 'payment')
                        ->where('source_id', $payment->id)
                        ->first();

                    if ($existingEntry) {
                        $payment->accounting_synced = true;
                        $payment->accounting_sync_at = now();
                        $payment->save();
                        continue;
                    }

                    // Create journal entry
                    $journalEntry = JournalEntry::create([
                        'hotel_id' => $hotelId,
                        'date' => $payment->created_at ? $payment->created_at->toDateString() : now()->toDateString(),
                        'entry_number' => JournalEntry::generateEntryNumber($hotelId),
                        'description' => "Payment Settlement: " . ($payment->notes ?? "Receipt #{$payment->payment_number}"),
                        'reference' => "PAY-{$payment->id}",
                        'total_amount' => $payment->amount,
                        'status' => JournalEntry::STATUS_POSTED,
                        'module_source' => 'payment',
                        'source_id' => $payment->id,
                        'created_by' => Auth::id() ?? 1,
                        'posted_at' => now(),
                    ]);

                    // Debit Cash (Asset increases)
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $cashAccount->id,
                        'debit' => $payment->amount,
                        'credit' => 0,
                        'description' => "Cash/Bank received",
                    ]);

                    // Credit Receivables (Asset decreases)
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $receivableAccount->id,
                        'debit' => 0,
                        'credit' => $payment->amount,
                        'description' => "Reduction in guest receivables",
                    ]);

                    $payment->accounting_synced = true;
                    $payment->accounting_sync_at = now();
                    $payment->save();

                    $syncedCount++;
                    $totalAmount += $payment->amount;

                } catch (\Exception $e) {
                    $errors[] = "Payment #{$payment->id}: " . $e->getMessage();
                }
            }

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => "Synced {$syncedCount} payments totaling UGX " . number_format($totalAmount),
                'synced_count' => $syncedCount,
                'total_amount' => $totalAmount,
                'errors' => $errors
            ]);

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


    /**
     * Sync POS revenue to accounting - FIXED VERSION
     */
   // Add these simplified sync methods to your AccountingController.php

/**
 * Sync POS revenue to accounting - SIMPLIFIED VERSION
 */
public function syncPosRevenue()
{
    $hotelId = $this->getHotelId();
    
    try {
        DB::beginTransaction();
        
        // Get unsynced POS orders
        $orders = Order::where('hotel_id', $hotelId)
            ->where('status', 'completed')
            ->where('accounting_synced', false)
            ->get();

        $syncedCount = 0;
        $totalAmount = 0;
        $errors = [];

        foreach ($orders as $order) {
            try {
                // Get account IDs
                $cashAccount = Account::where('code', '11251')
                    ->where(function($q) use ($hotelId) {
                        $q->whereNull('hotel_id')->orWhere('hotel_id', $hotelId);
                    })->first();
                    
                $revenueAccount = Account::where('code', '21211')
                    ->where(function($q) use ($hotelId) {
                        $q->whereNull('hotel_id')->orWhere('hotel_id', $hotelId);
                    })->first();

                if (!$cashAccount || !$revenueAccount) {
                    throw new \Exception("Required accounts not found. Please ensure accounts 11251 and 21211 exist.");
                }

                // Check for existing entry to prevent duplication
                $existingEntry = JournalEntry::where('module_source', 'pos')
                    ->where('source_id', $order->id)
                    ->first();

                if ($existingEntry) {
                    $order->accounting_synced = true;
                    $order->accounting_sync_at = now();
                    $order->save();
                    continue;
                }

                // Calculate VAT (assuming 18% is included in total_amount)
                $vatRate = 0.18;
                $vatAmount = round($order->total_amount * ($vatRate / (1 + $vatRate)), 2);
                $netAmount = $order->total_amount - $vatAmount;

                // Create journal entry
                $journalEntry = JournalEntry::create([
                    'hotel_id' => $hotelId,
                    'date' => now()->toDateString(),
                    'entry_number' => JournalEntry::generateEntryNumber($hotelId),
                    'description' => "POS Order #{$order->order_number}",
                    'reference' => $order->order_number,
                    'total_amount' => $order->total_amount,
                    'status' => JournalEntry::STATUS_POSTED,
                    'module_source' => 'pos',
                    'source_id' => $order->id,
                    'created_by' => Auth::id() ?? 1,
                    'posted_at' => now(),
                ]);

                // Debit cash
                JournalEntryItem::create([
                    'hotel_id' => $hotelId,
                    'journal_entry_id' => $journalEntry->id,
                    'account_id' => $cashAccount->id,
                    'debit' => $order->total_amount,
                    'credit' => 0,
                    'description' => "Payment received for POS #{$order->order_number}",
                ]);

                // Credit revenue (NET)
                JournalEntryItem::create([
                    'hotel_id' => $hotelId,
                    'journal_entry_id' => $journalEntry->id,
                    'account_id' => $revenueAccount->id,
                    'debit' => 0,
                    'credit' => $netAmount,
                    'description' => "Revenue (Net of VAT) - POS #{$order->order_number}",
                ]);

                // Credit VAT payable
                $taxAccount = Account::forHotel($hotelId)
                    ->where('code', '12251') // VAT Output Tax Payable
                    ->first();
                
                if ($taxAccount && $vatAmount > 0) {
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $taxAccount->id,
                        'debit' => 0,
                        'credit' => $vatAmount,
                        'description' => "VAT output on POS sale",
                    ]);
                }

                // Mark as synced
                $order->accounting_synced = true;
                $order->accounting_sync_at = now();
                $order->save();

                $syncedCount++;
                $totalAmount += $order->total_amount;

            } catch (\Exception $e) {
                $errors[] = "Order #{$order->order_number}: " . $e->getMessage();
            }
        }

        DB::commit();

        return response()->json([
            'success' => true,
            'module' => 'pos',
            'synced_count' => $syncedCount,
            'total_amount' => $totalAmount,
            'errors' => $errors,
            'message' => "Synced {$syncedCount} POS orders totaling UGX " . number_format($totalAmount)
        ]);

    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'success' => false,
            'module' => 'pos',
            'error' => $e->getMessage(),
            'message' => 'Sync failed: ' . $e->getMessage()
        ], 500);
    }
} /**
     * Sync service revenue to accounting (dashboard /accounting/sync-services).
     * Delegates to AccountingSyncService::syncServiceRequests — single source of truth.
     */
    public function syncServiceRevenue()
    {
        $hotelId = $this->getHotelId();

        try {
            $result = $this->syncService->syncServiceRequests($hotelId);

            $errors = $result['errors'] ?? [];
            $syncedCount = (int) ($result['synced_count'] ?? 0);
            $totalRevenue = (float) ($result['total_amount'] ?? 0);
            $ok = !empty($result['success']);

            $message = $result['message'] ?? 'Service sync finished.';
            if (!empty($errors)) {
                $message .= ' Details: ' . implode(' ', $errors);
            }

            return response()->json([
                'success' => $ok,
                'module' => 'services',
                'synced_count' => $syncedCount,
                'total_amount' => $totalRevenue,
                'errors' => $errors,
                'message' => $message,
            ]);
        } catch (\Exception $e) {
            Log::error('Failed to sync service revenue', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
            ]);

            return response()->json([
                'success' => false,
                'module' => 'services',
                'synced_count' => 0,
                'total_amount' => 0,
                'errors' => [$e->getMessage()],
                'message' => 'Failed to sync service revenue: ' . $e->getMessage(),
            ], 500);
        }
    }

    /**
     * Sync room revenue to accounting - FIXED VERSION
     */
   /**
 * Sync room revenue to accounting - UPDATED to include checked_in reservations with payments
 */
public function syncRoomRevenue()
{
    $hotelId = $this->getHotelId();
    $syncedCount = 0;
    $totalRevenue = 0;
    $errors = [];

    try {
        DB::beginTransaction();

        // Get ALL unsynced reservations that have payments (paid_amount > 0)
        // This includes checked_in, checked_out, and confirmed reservations
        // Get ALL unsynced reservations that are checked out OR have payments
        $reservations = Reservation::where('hotel_id', $hotelId)
            ->where('accounting_synced', false)
            ->where(function($q) {
                $q->where('status', 'checked_out')
                  ->orWhere('paid_amount', '>', 0);
            })
            ->get();

        Log::info('SyncRoomRevenue - Found reservations:', [
            'count' => $reservations->count(),
            'ids' => $reservations->pluck('id')->toArray()
        ]);

        foreach ($reservations as $reservation) {
            try {
                // Prevent duplicate reservation revenue journals for the same source reservation.
                $existingEntry = JournalEntry::where('hotel_id', $hotelId)
                    ->where('module_source', 'reservation')
                    ->where('source_id', $reservation->id)
                    ->first();

                if ($existingEntry) {
                    $reservation->accounting_synced = true;
                    $reservation->accounting_sync_at = now();
                    $reservation->save();
                    continue;
                }

                // Determine revenue account based on source
                $revenueAccountCode = '21111'; // Default Walk-in
                if ($reservation->source === 'ota') {
                    $revenueAccountCode = '21112'; // OTA
                } elseif ($reservation->source === 'corporate') {
                    $revenueAccountCode = '21123'; // Corporate
                }
                
                $revenueAccount = Account::forHotel($hotelId)
                    ->where('code', $revenueAccountCode)
                    ->first();
                    
                // Get cash/bank account based on reservation payment method (if available)
                $method = $reservation->payment_method ?? 'cash';
                $codeMap = [
                    'cash' => '11251',
                    'credit_card' => '11241',
                    'bank_transfer' => '11241',
                    'mobile_money' => '11254',
                    'pos' => '11251',
                ];
                $cashAccountCode = $codeMap[$method] ?? '11251';
                
                $cashAccount = Account::forHotel($hotelId)
                    ->where('code', $cashAccountCode)
                    ->first();
                    
                if (!$cashAccount) {
                    $cashAccount = Account::forHotel($hotelId)
                        ->where('code', '11251') // Fallback to Petty Cash
                        ->first();
                }
                    
                $receivableAccount = Account::forHotel($hotelId)
                    ->where('code', '11221') // Hotel Guest Receivables
                    ->first();

                if (!$revenueAccount) {
                    throw new \Exception("Room revenue account not found for code: {$revenueAccountCode}");
                }

                // Determine if paid or on account
                $debitAccount = ($reservation->paid_amount ?? 0) > 0 ? $cashAccount : $receivableAccount;
                if (!$debitAccount) {
                    $debitAccount = $receivableAccount;
                }

                // Calculate VAT (assuming 18% is included in total_amount)
                $vatRate = setting('tax_rate', 18) / 100;
                $vatAmount = $reservation->total_amount * ($vatRate / (1 + $vatRate));
                $netAmount = $reservation->total_amount - $vatAmount;

                // Create journal entry
                $journalEntry = JournalEntry::create([
                    'hotel_id' => $hotelId,
                    'date' => $reservation->actual_check_out 
                        ? Carbon::parse($reservation->actual_check_out)->toDateString() 
                        : now()->toDateString(),
                    'entry_number' => JournalEntry::generateEntryNumber($hotelId),
                    'description' => "Room reservation #{$reservation->reservation_number} - {$reservation->guest->first_name} {$reservation->guest->last_name}",
                    'reference' => $reservation->reservation_number,
                    'total_amount' => $reservation->total_amount,
                    'status' => JournalEntry::STATUS_POSTED,
                    'module_source' => 'reservation',
                    'source_id' => $reservation->id,
                    'created_by' => Auth::id() ?? 1,
                    'posted_at' => now(),
                ]);

                // Debit entry (Cash or Receivable)
                JournalEntryItem::create([
                    'hotel_id' => $hotelId,
                    'journal_entry_id' => $journalEntry->id,
                    'account_id' => $debitAccount->id,
                    'debit' => $reservation->total_amount,
                    'credit' => 0,
                    'description' => "Room revenue received",
                ]);

                // Credit revenue (net of VAT)
                JournalEntryItem::create([
                    'hotel_id' => $hotelId,
                    'journal_entry_id' => $journalEntry->id,
                    'account_id' => $revenueAccount->id,
                    'debit' => 0,
                    'credit' => $netAmount,
                    'description' => "Room revenue (net)",
                ]);

                // Credit VAT payable
                $taxAccount = Account::forHotel($hotelId)
                    ->where('code', '12251')
                    ->first();
                
                if ($taxAccount && $vatAmount > 0) {
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $taxAccount->id,
                        'debit' => 0,
                        'credit' => $vatAmount,
                        'description' => "VAT on room charges",
                    ]);
                }

                // Mark as synced
                $reservation->accounting_synced = true;
                $reservation->accounting_sync_at = now();
                $reservation->save();

                $syncedCount++;
                $totalRevenue += $reservation->total_amount;

                Log::info("Synced reservation #{$reservation->id}: {$reservation->total_amount} UGX");

            } catch (\Exception $e) {
                $errors[] = "Reservation #{$reservation->id}: " . $e->getMessage();
                Log::error("Failed to sync reservation {$reservation->id}: " . $e->getMessage());
            }
        }

        DB::commit();

        return response()->json([
            'success' => true,
            'module' => 'rooms',
            'synced_count' => $syncedCount,
            'total_amount' => $totalRevenue,
            'errors' => $errors,
            'message' => "Successfully synced {$syncedCount} reservations totaling UGX " . number_format($totalRevenue)
        ]);

    } catch (\Exception $e) {
        DB::rollBack();
        Log::error('Failed to sync room revenue', [
            'error' => $e->getMessage(),
            'trace' => $e->getTraceAsString()
        ]);

        return response()->json([
            'success' => false,
            'module' => 'rooms',
            'synced_count' => $syncedCount,
            'total_amount' => $totalRevenue,
            'error' => $e->getMessage(),
            'message' => 'Failed to sync room revenue: ' . $e->getMessage()
        ], 500);
    }
}
    /**
     * Sync purchase orders to accounting - FIXED VERSION
     */
    public function syncPurchaseOrders()
    {
        $hotelId = $this->getHotelId();
        $syncedCount = 0;
        $totalAmount = 0;
        $errors = [];
        
        try {
            DB::beginTransaction();
            
            $purchaseOrders = InventoryOrder::where('hotel_id', $hotelId)
                ->where('status', 'received')
                ->where('accounting_synced', false)
                ->get();

            foreach ($purchaseOrders as $po) {
                try {
                    // Get accounts
                    $inventoryAccount = Account::forHotel($hotelId)
                        ->where('code', '11212') // F&B Stock
                        ->first();
                        
                    $payableAccount = Account::forHotel($hotelId)
                        ->where('code', '12211') // Trade Creditors
                        ->first();

                    if (!$inventoryAccount || !$payableAccount) {
                        throw new \Exception("Required accounts not found for purchase order sync");
                    }

                    // Create journal entry
                    $journalEntry = JournalEntry::create([
                        'hotel_id' => $hotelId,
                        'date' => Carbon::parse($po->received_date ?? $po->created_at)->toDateString(),
                        'entry_number' => JournalEntry::generateEntryNumber($hotelId),
                        'description' => "Purchase order #{$po->po_number}",
                        'reference' => $po->po_number,
                        'total_amount' => $po->total_amount,
                        'status' => JournalEntry::STATUS_POSTED,
                        'module_source' => 'purchase_order',
                        'source_id' => $po->id,
                        'created_by' => Auth::id() ?? 1,
                        'posted_at' => now(),
                    ]);

                    // Debit inventory
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $inventoryAccount->id,
                        'debit' => $po->total_amount,
                        'credit' => 0,
                        'description' => "Inventory purchase",
                    ]);

                    // Credit accounts payable
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $payableAccount->id,
                        'debit' => 0,
                        'credit' => $po->total_amount,
                        'description' => "Amount payable to supplier",
                    ]);

                    // Mark as synced
                    $po->accounting_synced = true;
                    $po->accounting_sync_at = now();
                    $po->save();

                        // Balances auto-refresh via JournalEntryItem created hook

                    $syncedCount++;
                    $totalAmount += $po->total_amount;

                } catch (\Exception $e) {
                    $errors[] = "PO #{$po->po_number}: " . $e->getMessage();
                    Log::error("Failed to sync purchase order {$po->id}: " . $e->getMessage());
                }
            }
            
            DB::commit();
            
            return response()->json([
                'success' => true,
                'module' => 'purchase_orders',
                'synced_count' => $syncedCount,
                'total_amount' => $totalAmount,
                'errors' => $errors,
                'message' => "Successfully synced {$syncedCount} purchase orders totaling UGX " . number_format($totalAmount)
            ]);
            
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Failed to sync purchase orders', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'module' => 'purchase_orders',
                'synced_count' => $syncedCount,
                'total_amount' => $totalAmount,
                'error' => $e->getMessage(),
                'message' => 'Failed to sync purchase orders: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Sync expenses to accounting - FIXED VERSION
     */
    public function syncExpenses()
    {
        $hotelId = $this->getHotelId();
        $syncedCount = 0;
        $totalAmount = 0;
        $errors = [];
        
        try {
            DB::beginTransaction();
            
            $expenses = Expense::where('hotel_id', $hotelId)
                ->where('status', 'approved')
                ->where('accounting_synced', false)
                ->get();

            foreach ($expenses as $expense) {
                try {
                    // Get accounts
                    $expenseAccount = Account::forHotel($hotelId)
                        ->where('code', '5600') // Administrative expenses
                        ->first();
                        
                    $cashAccount = Account::forHotel($hotelId)
                        ->where('code', '11251') // Petty Cash-General
                        ->first();

                    if (!$expenseAccount || !$cashAccount) {
                        throw new \Exception("Required accounts not found for expense sync");
                    }

                    // Create journal entry
                    $journalEntry = JournalEntry::create([
                        'hotel_id' => $hotelId,
                        'date' => Carbon::parse($expense->date)->toDateString(),
                        'entry_number' => JournalEntry::generateEntryNumber($hotelId),
                        'description' => $expense->description ?? "Expense payment",
                        'reference' => "EXP-{$expense->id}",
                        'total_amount' => $expense->amount,
                        'status' => JournalEntry::STATUS_POSTED,
                        'module_source' => 'expense',
                        'source_id' => $expense->id,
                        'created_by' => Auth::id() ?? 1,
                        'posted_at' => now(),
                    ]);

                    // Debit expense
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $expenseAccount->id,
                        'debit' => $expense->amount,
                        'credit' => 0,
                        'description' => "Expense payment",
                    ]);

                    // Credit cash
                    JournalEntryItem::create([
                        'hotel_id' => $hotelId,
                        'journal_entry_id' => $journalEntry->id,
                        'account_id' => $cashAccount->id,
                        'debit' => 0,
                        'credit' => $expense->amount,
                        'description' => "Payment for expense",
                    ]);

                    // Mark as synced
                    $expense->accounting_synced = true;
                    $expense->accounting_synced_at = now();
                    $expense->save();

                        // Balances auto-refresh via JournalEntryItem created hook

                    $syncedCount++;
                    $totalAmount += $expense->amount;

                } catch (\Exception $e) {
                    $errors[] = "Expense #{$expense->id}: " . $e->getMessage();
                    Log::error("Failed to sync expense {$expense->id}: " . $e->getMessage());
                }
            }
            
            DB::commit();
            
            return response()->json([
                'success' => true,
                'module' => 'expenses',
                'synced_count' => $syncedCount,
                'total_amount' => $totalAmount,
                'errors' => $errors,
                'message' => "Successfully synced {$syncedCount} expenses totaling UGX " . number_format($totalAmount)
            ]);
            
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Failed to sync expenses', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'module' => 'expenses',
                'synced_count' => $syncedCount,
                'total_amount' => $totalAmount,
                'error' => $e->getMessage(),
                'message' => 'Failed to sync expenses: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Sync all modules - FIXED VERSION
     */
    public function syncAll()
    {
        $hotelId = $this->getHotelId();
        $results = [];
        $hasErrors = false;
        
        try {
            DB::beginTransaction();
            
            // Sync POS
            $posResult = $this->syncPosRevenue()->getData(true);
            $results['pos'] = $posResult;
            if (!$posResult['success']) $hasErrors = true;
            
            // Sync Services
            $serviceResult = $this->syncServiceRevenue()->getData(true);
            $results['services'] = $serviceResult;
            if (!$serviceResult['success']) $hasErrors = true;
            
            // Sync Rooms
            $roomResult = $this->syncRoomRevenue()->getData(true);
            $results['rooms'] = $roomResult;
            if (!$roomResult['success']) $hasErrors = true;
            
            // Sync Purchase Orders
            $poResult = $this->syncPurchaseOrders()->getData(true);
            $results['purchase_orders'] = $poResult;
            if (!$poResult['success']) $hasErrors = true;
            
            // Sync Expenses
            $expenseResult = $this->syncExpenses()->getData(true);
            $results['expenses'] = $expenseResult;
            if (!$expenseResult['success']) $hasErrors = true;

            // Sync Payments
            $paymentResult = $this->syncPayments()->getData(true);
            $results['payments'] = $paymentResult;
            if (!$paymentResult['success']) $hasErrors = true;
            
            DB::commit();
            
            $totalSynced = collect($results)->sum('synced_count');
            $totalAmount = collect($results)->sum('total_amount');
            
            return response()->json([
                'success' => !$hasErrors,
                'results' => $results,
                'total_synced' => $totalSynced,
                'total_amount' => $totalAmount,
                'message' => "Sync completed: {$totalSynced} items totaling UGX " . number_format($totalAmount)
            ]);
            
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error('Failed to sync all modules', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            
            return response()->json([
                'success' => false,
                'error' => $e->getMessage(),
                'message' => 'Failed to sync all modules: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Sync specific module
     */
    public function syncModule($module)
    {
        $hotelId = $this->getHotelId();
        
        $methodMap = [
            'pos' => 'syncOrders',
            'services' => 'syncServiceRequests',
            'rooms' => 'syncReservations',
            'purchase_orders' => 'syncInventoryOrders',
            'expenses' => 'syncExpenses',
            'payments' => 'syncPayments'
        ];
        
        if (!isset($methodMap[$module])) {
            return response()->json([
                'success' => false,
                'message' => "Module '{$module}' not recognized"
            ], 400);
        }
        
        $method = $methodMap[$module];
        
        try {
            $result = $this->syncService->$method($hotelId);
            return response()->json($result);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'module' => $module,
                'message' => 'Sync failed: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get pending sync counts
     */
    public function getPendingSyncCounts()
    {
        $hotelId = $this->getHotelId();
        $status = $this->getModuleSyncStatus($hotelId);
        
        $counts = [];
        foreach ($status as $module => $data) {
            $counts[$module] = $data['pending_count'] ?? 0;
        }
        
        return response()->json([
            'success' => true,
            'counts' => $counts
        ]);
    }

    /**
     * Get sync status for dashboard
     */
    public function getSyncStatus()
    {
        $hotelId = $this->getHotelId();
        $status = $this->getModuleSyncStatus($hotelId);
        
        return response()->json([
            'success' => true,
            'status' => $status
        ]);
    }

    // ==================== HELPER METHODS ====================

    /**
     * Get financial statistics
     */
    private function getFinancialStats($hotelId)
    {
        // Get all accounts for this hotel
        $accounts = Account::forHotel($hotelId)->get();
        $accountIds = $accounts->pluck('id');

        // 1. CASH & EQUIVALENTS (Source: POSTED Journal Entries)
        $cashBalance = JournalEntryItem::whereIn('account_id', $accounts->whereIn('category', ['Assets - Cash at Bank', 'Assets - Cash at Hand'])->pluck('id'))
            ->whereHas('journalEntry', fn($q) => $q->where('status', 'posted')->where('hotel_id', $hotelId))
            ->selectRaw('SUM(debit) - SUM(credit) as balance')
            ->value('balance') ?: 0;

        // 2. RECEIVABLES (Source: POSTED Journal Entries)
        $receivableBalance = JournalEntryItem::whereIn('account_id', $accounts->where('category', 'Assets - Receivables')->pluck('id'))
            ->whereHas('journalEntry', fn($q) => $q->where('status', 'posted')->where('hotel_id', $hotelId))
            ->selectRaw('SUM(debit) - SUM(credit) as balance')
            ->value('balance') ?: 0;
            
        // 3. OTHER ASSETS (Inventory, Fixed Assets, etc.)
        $otherAssets = JournalEntryItem::whereIn('account_id', $accounts->whereIn('category', ['Assets - Inventory', 'Assets - Fixed Assets', 'Assets - Prepayments'])->pluck('id'))
            ->whereHas('journalEntry', fn($q) => $q->where('status', 'posted')->where('hotel_id', $hotelId))
            ->selectRaw('SUM(debit) - SUM(credit) as balance')
            ->value('balance') ?: 0;

        // 4. WORK IN PROGRESS (WIP) REVENUE (Source: GuestLedger for checked_in guests not yet synced)
        // This is the "missing link" between Front Office and Back Office
        $wipRevenue = \App\Models\GuestLedger::whereHas('reservation', function($q) use ($hotelId) {
                $q->where('hotel_id', $hotelId)
                  ->where('status', 'checked_in')
                  ->where('accounting_synced', false);
            })
            ->sum('debit');

        // Total Assets
        $totalAssets = $cashBalance + $receivableBalance + $otherAssets + $wipRevenue;

        // 5. LIABILITIES (Source: POSTED Journal Entries)
        $liabilityAccountIds = $accounts->where('normal_balance', 'Cr')
            ->whereIn('category', [
                'Liabilities - Current', 
                'Liabilities - Long Term', 
                'Liabilities - Tax'
            ])
            ->pluck('id');
            
        $recordedLiabilities = JournalEntryItem::whereIn('account_id', $liabilityAccountIds)
            ->whereHas('journalEntry', function($q) use ($hotelId) {
                $q->where('status', 'posted');
                $q->where('hotel_id', $hotelId);
            })
            ->selectRaw('SUM(credit) - SUM(debit) as balance')
            ->value('balance') ?: 0;
            
        // Handle negative receivables as Liabilites (Prepaid Deposits)
        $deposits = (float)abs(min(0, $receivableBalance));
        $totalLiabilities = $recordedLiabilities + $deposits;

        // 6. MONTHLY REVENUE (Source: POSTED Journal Entries) - THE ACCURATE REVENUE
        $revenueAccountIds = $accounts->whereIn('report_section', ['REVENUE', 'REVENUE_DEPT'])
            ->pluck('id');

        $monthlyRevenueFromAccounts = JournalEntryItem::whereIn('account_id', $revenueAccountIds)
            ->whereHas('journalEntry', function($q) use ($hotelId) {
                $q->where('status', 'posted')
                  ->where('hotel_id', $hotelId)
                  ->whereMonth('date', now()->month)
                  ->whereYear('date', now()->year);
            })
            ->selectRaw('SUM(credit) - SUM(debit) as balance')
            ->value('balance') ?: 0;

        // Ignore duplicate posted reservation journals (same source reservation posted multiple times).
        $reservationEntriesThisMonth = JournalEntry::where('hotel_id', $hotelId)
            ->where('status', 'posted')
            ->where('module_source', 'reservation')
            ->whereNotNull('source_id')
            ->whereMonth('date', now()->month)
            ->whereYear('date', now()->year)
            ->orderBy('id')
            ->get(['id', 'source_id']);

        $duplicateReservationEntryIds = $reservationEntriesThisMonth
            ->groupBy('source_id')
            ->flatMap(function ($entries) {
                return $entries->slice(1)->pluck('id');
            })
            ->values();

        if ($duplicateReservationEntryIds->isNotEmpty()) {
            $duplicateRevenueImpact = JournalEntryItem::whereIn('journal_entry_id', $duplicateReservationEntryIds)
                ->whereIn('account_id', $revenueAccountIds)
                ->selectRaw('SUM(credit) - SUM(debit) as balance')
                ->value('balance') ?: 0;
            $monthlyRevenueFromAccounts -= $duplicateRevenueImpact;
        }

        // Include paid, unsynced room sales so dashboard reflects new front-desk sales immediately.
        // This avoids the "monthly revenue = 0" gap before accounting sync runs.
        $pendingPaidRoomSales = Reservation::where('hotel_id', $hotelId)
            ->where('accounting_synced', false)
            ->where('paid_amount', '>', 0)
            ->whereMonth('created_at', now()->month)
            ->whereYear('created_at', now()->year)
            ->whereNotExists(function ($q) {
                $q->select(DB::raw(1))
                    ->from('journal_entries')
                    ->whereColumn('journal_entries.source_id', 'reservations.id')
                    ->where('journal_entries.module_source', 'reservation')
                    ->where('journal_entries.status', JournalEntry::STATUS_POSTED);
            })
            ->sum('paid_amount');

        $vatRate = (float) setting('tax_rate', 18) / 100;
        $pendingPaidRoomSalesNet = $pendingPaidRoomSales > 0
            ? $pendingPaidRoomSales / (1 + $vatRate)
            : 0;

        // Gross sales view used by dashboard card toggle:
        // posted operational sales (reservation + pos + service requests) + pending paid room sales.
        $postedSalesGross = JournalEntry::where('hotel_id', $hotelId)
            ->where('status', JournalEntry::STATUS_POSTED)
            ->whereIn('module_source', ['reservation', 'pos', 'service_request'])
            ->whereMonth('date', now()->month)
            ->whereYear('date', now()->year)
            ->sum('total_amount');

        $duplicateGrossImpact = 0;
        if ($duplicateReservationEntryIds->isNotEmpty()) {
            $duplicateGrossImpact = JournalEntry::whereIn('id', $duplicateReservationEntryIds)
                ->sum('total_amount');
        }
        $postedSalesGross -= $duplicateGrossImpact;

        $monthlyRevenueGross = $postedSalesGross + $pendingPaidRoomSales;
        $postedSalesNet = $postedSalesGross > 0
            ? $postedSalesGross / (1 + $vatRate)
            : 0;
        $monthlyRevenueNet = $postedSalesNet + $pendingPaidRoomSalesNet;

        // Keep chart/report compatibility: prefer operational net derivation,
        // but never under-report compared to correctly-mapped account revenue.
        $monthlyRevenue = max($monthlyRevenueNet, $monthlyRevenueFromAccounts + $pendingPaidRoomSalesNet);

        // 7. MONTHLY EXPENSES (Source: POSTED Journal Entries)
        $expenseAccountIds = $accounts->whereIn('report_section', ['COGS', 'COGS_DEPT', 'OPEX', 'Expenses', 'EXPENSES'])
            ->merge($accounts->filter(function ($account) {
                return stripos((string) ($account->category ?? ''), 'expense') !== false;
            }))
            ->pluck('id');

        $monthlyExpenses = JournalEntryItem::whereIn('account_id', $expenseAccountIds)
            ->whereHas('journalEntry', function($q) use ($hotelId) {
                $q->where('status', 'posted')
                  ->where('hotel_id', $hotelId)
                  ->whereMonth('date', now()->month)
                  ->whereYear('date', now()->year);
            })
            ->selectRaw('SUM(debit) - SUM(credit) as balance')
            ->value('balance') ?: 0;

        // Net Income
        $netIncome = $monthlyRevenueNet - $monthlyExpenses;

        // 8. UNRECOGNIZED REVENUE (Front-office potential revenue)
        $bookedUpcoming = \App\Models\Reservation::where('hotel_id', $hotelId)
            ->where('status', 'confirmed')
            ->sum('total_amount');
            
        $pendingPosRevenue = \App\Models\Order::where('hotel_id', $hotelId)
            ->where('status', 'completed')
            ->where('accounting_synced', false)
            ->sum('total_amount');

        return [
            'total_assets' => $totalAssets,
            'total_liabilities' => $totalLiabilities,
            'monthly_revenue' => $monthlyRevenue,
            'monthly_revenue_net' => $monthlyRevenueNet,
            'monthly_revenue_gross' => $monthlyRevenueGross,
            'monthly_expenses' => $monthlyExpenses,
            'net_income' => $netIncome,
            'cash_balance' => $cashBalance,
            'receivable_balance' => max(0, $receivableBalance), // Dashboard should show +ve receivables
            'wip_revenue' => $wipRevenue,
            'booked_revenue' => $bookedUpcoming, // Upcoming guaranteed revenue
            'pending_pos' => $pendingPosRevenue,
            'revenue_breakdown' => [
                'posted_net' => $postedSalesNet,
                'pending_unsynced_net' => $pendingPaidRoomSalesNet,
                'posted_gross' => $postedSalesGross,
                'pending_unsynced_gross' => $pendingPaidRoomSales,
                'duplicate_revenue_removed' => ($duplicateGrossImpact > 0) ? ($duplicateGrossImpact / (1 + $vatRate)) : ($duplicateRevenueImpact ?? 0),
                'duplicate_gross_removed' => $duplicateGrossImpact,
            ],
        ];
    }

    /**
     * Get account balance for a specific period
     */
    private function getAccountBalanceForPeriod($account, $startDate, $endDate)
    {
        $debits = JournalEntryItem::where('account_id', $account->id)
            ->whereHas('journalEntry', function($q) use ($startDate, $endDate) {
                $q->whereBetween('date', [$startDate, $endDate])
                  ->where('status', JournalEntry::STATUS_POSTED);
            })
            ->sum('debit');

        $credits = JournalEntryItem::where('account_id', $account->id)
            ->whereHas('journalEntry', function($q) use ($startDate, $endDate) {
                $q->whereBetween('date', [$startDate, $endDate])
                  ->where('status', JournalEntry::STATUS_POSTED);
            })
            ->sum('credit');

        if ($account->normal_balance === 'Dr') {
            return $debits - $credits;
        } else {
            return $credits - $debits;
        }
    }

    /**
     * Get chart data
     */
    private function getChartData($hotelId)
    {
        $months = [];
        $revenueData = [];
        $expenseData = [];

        for ($i = 5; $i >= 0; $i--) {
            $date = now()->subMonths($i);
            $months[] = $date->format('M Y');

            $startDate = $date->copy()->startOfMonth();
            $endDate = $date->copy()->endOfMonth();

            // Revenue
            $revenueAccounts = Account::forHotel($hotelId)
                ->where('normal_balance', 'Cr')
                ->whereIn('report_section', ['REVENUE', 'REVENUE_DEPT'])
                ->get();

            $monthlyRevenue = 0;
            foreach ($revenueAccounts as $account) {
                $monthlyRevenue += $this->getAccountBalanceForPeriod($account, $startDate, $endDate);
            }
            $revenueData[] = $monthlyRevenue;

            // Expenses
            $expenseAccounts = Account::forHotel($hotelId)
                ->where('normal_balance', 'Dr')
                ->whereIn('report_section', ['COGS', 'COGS_DEPT', 'OPEX'])
                ->get();

            $monthlyExpenses = 0;
            foreach ($expenseAccounts as $account) {
                $monthlyExpenses += $this->getAccountBalanceForPeriod($account, $startDate, $endDate);
            }
            $expenseData[] = $monthlyExpenses;
        }

        return [
            'months' => $months,
            'revenue' => $revenueData,
            'expenses' => $expenseData,
        ];
    }

    /**
     * Get system alerts
     */
    private function getSystemAlerts($hotelId)
    {
        $alerts = [];

        // Unbalanced journal entries
        $unbalancedEntries = JournalEntry::forHotel($hotelId)
            ->where('status', '!=', JournalEntry::STATUS_POSTED)
            ->get()
            ->filter(function($entry) {
                return !$entry->is_balanced;
            });

        if ($unbalancedEntries->count() > 0) {
            $alerts[] = [
                'type' => 'warning',
                'title' => 'Unbalanced Entries',
                'message' => "You have {$unbalancedEntries->count()} unbalanced journal entries.",
            ];
        }

        // Draft entries
        $draftEntries = JournalEntry::forHotel($hotelId)->draft()->count();
        if ($draftEntries > 0) {
            $alerts[] = [
                'type' => 'info',
                'title' => 'Draft Entries',
                'message' => "You have {$draftEntries} draft journal entries ready for review.",
            ];
        }

        // Negative cash balances
        $negativeCash = Account::forHotel($hotelId)
            ->where('code', 'LIKE', '112%')
            ->get()
            ->filter(function ($account) {
                return $account->balance < 0;
            })
            ->count();

        if ($negativeCash > 0) {
            $alerts[] = [
                'type' => 'danger',
                'title' => 'Negative Cash',
                'message' => "{$negativeCash} cash/bank accounts have negative balances.",
            ];
        }

        return $alerts;
    }

    /**
     * Get module sync status
     */
    private function getModuleSyncStatus($hotelId)
    {
        $status = [
            'pos' => [
                'pending_count' => 0,
                'last_sync' => null,
            ],
            'services' => [
                'pending_count' => 0,
                'last_sync' => null,
            ],
            'rooms' => [
                'pending_count' => 0,
                'last_sync' => null,
            ],
            'purchase_orders' => [
                'pending_count' => 0,
                'last_sync' => null,
            ],
            'expenses' => [
                'pending_count' => 0,
                'last_sync' => null,
            ],
            'payments' => [
                'pending_count' => 0,
                'last_sync' => null,
            ],
        ];

        try {
            // POS Orders
            if (Schema::hasTable('orders')) {
                $status['pos'] = [
                    'pending_count' => Order::where('hotel_id', $hotelId)
                        ->where('status', 'completed')
                        ->where('accounting_synced', false)
                        ->count(),
                    'last_sync' => Order::where('hotel_id', $hotelId)
                        ->where('accounting_synced', true)
                        ->max('accounting_sync_at'),
                ];
            }
        } catch (\Exception $e) {
            Log::warning('Error getting POS sync status: ' . $e->getMessage());
        }

        try {
            // Service Requests
            if (Schema::hasTable('service_requests') && Schema::hasTable('services')) {
                $pendingCount = DB::table('service_requests')
                    ->join('services', 'service_requests.service_id', '=', 'services.id')
                    ->where('services.hotel_id', $hotelId)
                    ->where('service_requests.status', 'completed')
                    ->where('service_requests.accounting_synced', false)
                    ->count();

                $lastSync = DB::table('service_requests')
                    ->join('services', 'service_requests.service_id', '=', 'services.id')
                    ->where('services.hotel_id', $hotelId)
                    ->where('service_requests.accounting_synced', true)
                    ->max('service_requests.accounting_sync_at');

                $status['services'] = [
                    'pending_count' => $pendingCount,
                    'last_sync' => $lastSync,
                ];
            }
        } catch (\Exception $e) {
            Log::warning('Error getting services sync status: ' . $e->getMessage());
        }

        try {
            // Reservations
            if (Schema::hasTable('reservations')) {
                $status['rooms'] = [
                    'pending_count' => Reservation::where('hotel_id', $hotelId)
                        ->where('status', 'checked_out')
                        ->where('accounting_synced', false)
                        ->count(),
                    'last_sync' => Reservation::where('hotel_id', $hotelId)
                        ->where('accounting_synced', true)
                        ->max('accounting_sync_at'),
                ];
            }
        } catch (\Exception $e) {
            Log::warning('Error getting rooms sync status: ' . $e->getMessage());
        }

        try {
            // Purchase Orders
            if (Schema::hasTable('inventory_orders')) {
                $status['purchase_orders'] = [
                    'pending_count' => InventoryOrder::where('hotel_id', $hotelId)
                        ->where('status', 'received')
                        ->where('accounting_synced', false)
                        ->count(),
                    'last_sync' => InventoryOrder::where('hotel_id', $hotelId)
                        ->where('accounting_synced', true)
                        ->max('accounting_sync_at'),
                ];
            }
        } catch (\Exception $e) {
            Log::warning('Error getting purchase orders sync status: ' . $e->getMessage());
        }

        try {
            // Expenses
            if (Schema::hasTable('expenses')) {
                $status['expenses'] = [
                    'pending_count' => Expense::where('hotel_id', $hotelId)
                        ->where('status', 'approved')
                        ->where('accounting_synced', false)
                        ->count(),
                    'last_sync' => Expense::where('hotel_id', $hotelId)
                        ->where('accounting_synced', true)
                        ->max('accounting_synced_at'),
                ];
            }
        } catch (\Exception $e) {
            Log::warning('Error getting expenses sync status: ' . $e->getMessage());
        }

        try {
            // Payments (Frontdesk revenue, etc)
            if (Schema::hasTable('payments')) {
                $status['payments'] = [
                    'pending_count' => \App\Models\Payment::where('hotel_id', $hotelId)
                        ->where('status', 'completed')
                        ->where('accounting_synced', false)
                        ->count(),
                    'last_sync' => \App\Models\Payment::where('hotel_id', $hotelId)
                        ->where('accounting_synced', true)
                        ->max('accounting_sync_at'),
                ];
            }
        } catch (\Exception $e) {
            Log::warning('Error getting payments sync status: ' . $e->getMessage());
        }

        return $status;
    }

    /**
     * Get journal templates
     */
    private function getJournalTemplates()
    {
        return [
            'JE-001' => 'Walk-in Cash Check-in',
            'JE-002' => 'Walk-in Credit Check-in',
            'JE-003' => 'OTA Booking',
            'JE-004' => 'Corporate Rate',
            'JE-005' => 'Advance Deposit',
            'JE-006' => 'Deposit Revenue Recognition',
            'JE-007' => 'Cash Settlement',
            'JE-008' => 'Mobile Money Settlement',
            'JE-009' => 'Bank Transfer Settlement',
            'JE-010' => 'Early Check-in Upsell',
            'JE-011' => 'Late Check-out Upsell',
            'JE-012' => 'Restaurant Cash Sale',
            'JE-013' => 'Restaurant Room Charge',
            'JE-014' => 'Room Service',
            'JE-015' => 'Minibar Replenishment',
            'JE-016' => 'Minibar Sale',
            'JE-017' => 'F&B Stock Purchase',
            'JE-018' => 'F&B Stock Issue',
            'JE-019' => 'Event Deposit',
            'JE-020' => 'Event Invoice',
            'JE-021' => 'Payroll Accrual',
            'JE-022' => 'Payroll Deductions',
            'JE-023' => 'Net Salary Payment',
            'JE-024' => 'Statutory Remittance',
            'JE-025' => 'Supplier Purchase',
            'JE-026' => 'Supplier Payment',
            'JE-027' => 'Petty Cash Purchase',
            'JE-028' => 'Petty Cash Replenishment',
            'JE-029' => 'VAT Return',
            'JE-030' => 'Monthly Depreciation',
            'JE-031' => 'Bank Charges',
            'JE-032' => 'Loan Interest Accrual',
            'JE-033' => 'Loan Repayment',
            'JE-034' => 'FX Gain',
            'JE-035' => 'FX Loss',
            'JE-036' => 'Utility Bill',
            'JE-037' => 'Rent Payment',
            'JE-038' => 'Prepaid Rent',
            'JE-039' => 'Prepaid Rent Release',
            'JE-040' => 'Fixed Asset Purchase (Cash)',
            'JE-041' => 'Fixed Asset Purchase (Credit)',
            'JE-042' => 'Fixed Asset Disposal',
            'JE-043' => 'Commission Income',
            'JE-044' => 'Tour Operations',
            'JE-045' => 'Corporate Tax Provision',
            'JE-046' => 'Corporate Tax Payment',
            'JE-047' => 'Laundry Service',
            'JE-048' => 'Spa Service',
            'JE-049' => 'Bad Debt Provision',
            'JE-050' => 'Bad Debt Write-off',
            'JE-051' => 'Utility Accrual',
            'JE-052' => 'Utility Accrual Reversal',
            'JE-053' => 'Leave Pay Accrual',
        ];
    }

    /**
     * Group accounts by P&L line
     */
    private function groupByPlLine($accounts)
    {
        $grouped = [];

        foreach ($accounts as $item) {
            $plLine = $item['account']->pl_line ?? 'Other';
            if (!isset($grouped[$plLine])) {
                $grouped[$plLine] = [
                    'total' => 0,
                    'accounts' => [],
                ];
            }
            $grouped[$plLine]['total'] += $item['balance'];
            $grouped[$plLine]['accounts'][] = $item;
        }

        return $grouped;
    }
}