<?php

namespace App\Http\Controllers\User;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class DashboardController extends Controller
{
    public function index()
    {
        $user = auth()->user();
        $userId = $user->id;
        $today = now()->toDateString();
        $year = now()->year;

        // =========================
        // Active Subscription
        // =========================
        $subscription = method_exists($user, 'activeSubscription')
            ? $user->activeSubscription()
            : null;

        // =========================
        // Safe Date Columns
        // =========================
        $expenseDateColumn      = Schema::hasColumn('expenses', 'posted_at') ? 'posted_at' : 'created_at';
        $customerDateColumn     = Schema::hasColumn('customer_transactions', 'posted_at') ? 'posted_at' : 'created_at';
        $supplierDateColumn     = Schema::hasColumn('supplier_transactions', 'posted_at') ? 'posted_at' : 'created_at';
        $accountDateColumn      = Schema::hasColumn('account_ledgers', 'posted_at') ? 'posted_at' : 'created_at';
        $bonusDateColumn        = Schema::hasColumn('employee_bonuses', 'bonus_date') ? 'bonus_date' : 'created_at';
        $advanceDateColumn      = Schema::hasColumn('employee_advances', 'advance_date') ? 'advance_date' : 'created_at';
        $loanPaymentDateColumn  = Schema::hasTable('loan_payments') && Schema::hasColumn('loan_payments', 'paid_at')
            ? 'paid_at'
            : 'created_at';
        $payrollDateColumn      = Schema::hasTable('payrolls') && Schema::hasColumn('payrolls', 'payroll_date')
            ? 'payroll_date'
            : 'created_at';

        // =========================
        // Accounts + Available Balance
        // balance = opening + debit - credit
        // =========================
        $accounts = DB::table('user_accounts as ua')
            ->where('ua.user_id', $userId)
            ->where('ua.status', 'active')
            ->leftJoin('account_ledgers as al', function ($join) use ($userId) {
                $join->on('al.user_account_id', '=', 'ua.id')
                    ->where('al.user_id', '=', $userId);
            })
            ->groupBy(
                'ua.id',
                'ua.name',
                'ua.account_number',
                'ua.type',
                'ua.opening_balance',
                'ua.status'
            )
            ->selectRaw("
                ua.id,
                ua.name,
                ua.account_number,
                ua.type,
                ua.opening_balance,
                ua.status,
                (COALESCE(ua.opening_balance,0) + COALESCE(SUM(al.debit),0) - COALESCE(SUM(al.credit),0)) as balance
            ")
            ->orderBy('ua.name')
            ->get();

        $totalBalance = round((float) $accounts->sum('balance'), 2);

        // =========================
        // Customer Receivable
        // due = debit - credit
        // =========================
        $totalCustomerBills = (float) DB::table('customer_transactions')
            ->where('user_id', $userId)
            ->sum('debit');

        $totalCustomerPayments = (float) DB::table('customer_transactions')
            ->where('user_id', $userId)
            ->sum('credit');

        $customerDue = round(max(0, $totalCustomerBills - $totalCustomerPayments), 2);

        // =========================
        // Employee Outstanding Advance
        // =========================
        $employeeAdvanceDue = 0.0;
        if (Schema::hasTable('employee_advances')) {
            $advanceQuery = DB::table('employee_advances')
                ->where('user_id', $userId);

            if (Schema::hasColumn('employee_advances', 'remaining_amount')) {
                $advanceQuery->where('remaining_amount', '>', 0);
                $employeeAdvanceDue = (float) $advanceQuery->sum('remaining_amount');
            }
        }
        $employeeAdvanceDue = round(max(0, $employeeAdvanceDue), 2);

        $totalReceivable = round($customerDue + $employeeAdvanceDue, 2);

        // =========================
        // Supplier Payable
        // due = debit - credit
        // =========================
        $totalSupplierPurchases = (float) DB::table('supplier_transactions')
            ->where('user_id', $userId)
            ->sum('debit');

        $totalSupplierPayments = (float) DB::table('supplier_transactions')
            ->where('user_id', $userId)
            ->sum('credit');

        $supplierDue = round(max(0, $totalSupplierPurchases - $totalSupplierPayments), 2);

        // =========================
        // Loan Payable
        // =========================
        $loanDue = 0.0;
        if (Schema::hasTable('account_ledgers')) {
            $loanReceived = (float) DB::table('account_ledgers')
                ->where('user_id', $userId)
                ->whereIn('type', ['loan_in', 'loan_receive'])
                ->sum('debit');

            $principalPaid = 0.0;
            if (Schema::hasTable('loan_payments') && Schema::hasColumn('loan_payments', 'principal_paid')) {
                $principalPaid = (float) DB::table('loan_payments')
                    ->where('user_id', $userId)
                    ->sum('principal_paid');
            }

            $loanDue = max(0, $loanReceived - $principalPaid);
        }
        $loanDue = round($loanDue, 2);

        // =========================
        // Salary Due
        // =========================
        $salaryDue = 0.0;
        if (Schema::hasTable('payroll_items') && Schema::hasColumn('payroll_items', 'due_amount')) {
            $salaryDue = (float) DB::table('payroll_items')
                ->where('user_id', $userId)
                ->sum('due_amount');
        }
        $salaryDue = round(max(0, $salaryDue), 2);

        // =========================
        // Unpaid Bonus
        // =========================
        $bonusDue = 0.0;
        if (Schema::hasTable('employee_bonuses')) {
            $bonusQuery = DB::table('employee_bonuses')
                ->where('user_id', $userId);

            if (Schema::hasColumn('employee_bonuses', 'status')) {
                $bonusQuery->where('status', 'unpaid');
            } elseif (Schema::hasColumn('employee_bonuses', 'paid_at')) {
                $bonusQuery->whereNull('paid_at');
            }

            $bonusDue = (float) $bonusQuery->sum('amount');
        }
        $bonusDue = round(max(0, $bonusDue), 2);

        $totalLiabilities = round(
            $supplierDue + $loanDue + $salaryDue + $bonusDue,
            2
        );

        // =========================
        // Loan Finance Cost
        // interest + fee only
        // =========================
        $loanFinanceCost = 0.0;
        if (
            Schema::hasTable('loan_payments') &&
            Schema::hasColumn('loan_payments', 'interest_paid') &&
            Schema::hasColumn('loan_payments', 'fee_paid')
        ) {
            $loanFinanceCost = (float) DB::table('loan_payments')
                ->where('user_id', $userId)
                ->selectRaw('COALESCE(SUM(interest_paid),0) + COALESCE(SUM(fee_paid),0) as total')
                ->value('total');
        }
        $loanFinanceCost = round($loanFinanceCost, 2);

        // =========================
        // Total Expense (All Expense in One Place)
        // general + purchase + salary + bonus + loan finance cost
        // =========================
        $generalExpense = (float) DB::table('expenses')
            ->where('user_id', $userId)
            ->sum('amount');

        $purchaseExpense = (float) DB::table('supplier_transactions')
            ->where('user_id', $userId)
            ->where('type', 'purchase')
            ->sum('debit');

        $salaryExpense = 0.0;
        if (Schema::hasTable('payroll_items') && Schema::hasColumn('payroll_items', 'gross_salary')) {
            $salaryExpense = (float) DB::table('payroll_items')
                ->where('user_id', $userId)
                ->sum('gross_salary');
        }

        $bonusExpense = 0.0;
        if (Schema::hasTable('employee_bonuses')) {
            $bonusExpense = (float) DB::table('employee_bonuses')
                ->where('user_id', $userId)
                ->sum('amount');
        }

        $totalExpense = round(
            $generalExpense + $purchaseExpense + $salaryExpense + $bonusExpense + $loanFinanceCost,
            2
        );

        // =========================
        // Today's Income
        // customer bill = income
        // =========================
        $todayIncome = (float) DB::table('customer_transactions')
            ->where('user_id', $userId)
            ->where('type', 'bill')
            ->whereDate($customerDateColumn, $today)
            ->sum('debit');

        $todayIncome = round($todayIncome, 2);

        // =========================
        // Today's Loan Finance Cost
        // =========================
        $todayLoanFinanceCost = 0.0;
        if (
            Schema::hasTable('loan_payments') &&
            Schema::hasColumn('loan_payments', 'interest_paid') &&
            Schema::hasColumn('loan_payments', 'fee_paid')
        ) {
            $todayLoanFinanceCost = (float) DB::table('loan_payments')
                ->where('user_id', $userId)
                ->whereDate($loanPaymentDateColumn, $today)
                ->selectRaw('COALESCE(SUM(interest_paid),0) + COALESCE(SUM(fee_paid),0) as total')
                ->value('total');
        }
        $todayLoanFinanceCost = round($todayLoanFinanceCost, 2);

        // =========================
        // Today's Expense
        // general + purchase + salary + bonus + loan finance cost
        // =========================
        $todayGeneralExpense = (float) DB::table('expenses')
            ->where('user_id', $userId)
            ->whereDate($expenseDateColumn, $today)
            ->sum('amount');

        $todayPurchaseExpense = (float) DB::table('supplier_transactions')
            ->where('user_id', $userId)
            ->where('type', 'purchase')
            ->whereDate($supplierDateColumn, $today)
            ->sum('debit');

        $todaySalaryExpense = 0.0;
        if (Schema::hasTable('payroll_items') && Schema::hasTable('payrolls')) {
            $todaySalaryExpense = (float) DB::table('payroll_items')
                ->join('payrolls', 'payrolls.id', '=', 'payroll_items.payroll_id')
                ->where('payroll_items.user_id', $userId)
                ->where('payrolls.user_id', $userId)
                ->whereDate("payrolls.$payrollDateColumn", $today)
                ->sum('payroll_items.gross_salary');
        }

        $todayBonusExpense = 0.0;
        if (Schema::hasTable('employee_bonuses')) {
            $todayBonusExpense = (float) DB::table('employee_bonuses')
                ->where('user_id', $userId)
                ->whereDate($bonusDateColumn, $today)
                ->sum('amount');
        }

        $todayExpense = round(
            $todayGeneralExpense + $todayPurchaseExpense + $todaySalaryExpense + $todayBonusExpense + $todayLoanFinanceCost,
            2
        );

        // =========================
        // Monthly Expense Chart
        // keep chart same style, but all expense combined
        // =========================
        $generalMonthly = DB::table('expenses')
            ->where('user_id', $userId)
            ->whereYear($expenseDateColumn, $year)
            ->selectRaw("MONTH($expenseDateColumn) as month, SUM(amount) as total")
            ->groupBy('month')
            ->pluck('total', 'month');

        $purchaseMonthly = DB::table('supplier_transactions')
            ->where('user_id', $userId)
            ->where('type', 'purchase')
            ->whereYear($supplierDateColumn, $year)
            ->selectRaw("MONTH($supplierDateColumn) as month, SUM(debit) as total")
            ->groupBy('month')
            ->pluck('total', 'month');

        $salaryMonthly = collect();
        if (Schema::hasTable('payroll_items') && Schema::hasTable('payrolls')) {
            $salaryMonthly = DB::table('payroll_items')
                ->join('payrolls', 'payrolls.id', '=', 'payroll_items.payroll_id')
                ->where('payroll_items.user_id', $userId)
                ->where('payrolls.user_id', $userId)
                ->whereYear("payrolls.$payrollDateColumn", $year)
                ->selectRaw("MONTH(payrolls.$payrollDateColumn) as month, SUM(payroll_items.gross_salary) as total")
                ->groupBy('month')
                ->pluck('total', 'month');
        }

        $bonusMonthly = collect();
        if (Schema::hasTable('employee_bonuses')) {
            $bonusMonthly = DB::table('employee_bonuses')
                ->where('user_id', $userId)
                ->whereYear($bonusDateColumn, $year)
                ->selectRaw("MONTH($bonusDateColumn) as month, SUM(amount) as total")
                ->groupBy('month')
                ->pluck('total', 'month');
        }

        $loanFinanceMonthly = collect();
        if (
            Schema::hasTable('loan_payments') &&
            Schema::hasColumn('loan_payments', 'interest_paid') &&
            Schema::hasColumn('loan_payments', 'fee_paid')
        ) {
            $loanFinanceMonthly = DB::table('loan_payments')
                ->where('user_id', $userId)
                ->whereYear($loanPaymentDateColumn, $year)
                ->selectRaw("MONTH($loanPaymentDateColumn) as month, (COALESCE(SUM(interest_paid),0) + COALESCE(SUM(fee_paid),0)) as total")
                ->groupBy('month')
                ->pluck('total', 'month');
        }

        $chartData = [];
        for ($i = 1; $i <= 12; $i++) {
            $chartData[] = round(
                (float) ($generalMonthly[$i] ?? 0)
                + (float) ($purchaseMonthly[$i] ?? 0)
                + (float) ($salaryMonthly[$i] ?? 0)
                + (float) ($bonusMonthly[$i] ?? 0)
                + (float) ($loanFinanceMonthly[$i] ?? 0),
                2
            );
        }

        // =========================
        // Account Distribution Chart
        // =========================
        $accountLabels = $accounts->pluck('name')->values();
        $accountBalances = $accounts->map(function ($a) {
            return round((float) $a->balance, 2);
        })->values();

        return view('user.dashboard', compact(
            'subscription',

            'totalBalance',
            'totalExpense',

            'todayIncome',
            'todayExpense',

            'totalReceivable',
            'totalLiabilities',

            'totalCustomerBills',
            'totalCustomerPayments',
            'customerDue',

            'totalSupplierPurchases',
            'totalSupplierPayments',
            'supplierDue',

            'employeeAdvanceDue',
            'salaryDue',
            'bonusDue',
            'loanDue',

            'loanFinanceCost',

            'chartData',
            'accountLabels',
            'accountBalances',
            'accounts'
        ));
    }
}