<?php

namespace App\Http\Controllers\Api\Reports;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Carbon\Carbon;
use DB;

class BalanceSheetController extends Controller
{
    //

    public function getBalanceSheet(Request $request)
    {
        // ✅ Default: current month
        $fromDate = $request['fromDate'] ?? Carbon::now()->startOfMonth()->toDateString();
        $toDate   = $request['toDate'] ?? Carbon::now()->endOfMonth()->toDateString();

        $report = DB::table('financial_statement_heads as f')
            ->select(
                'f.head_name',
                DB::raw('COALESCE(SUM(t.credit - t.debit), 0) as total')
            )
            ->leftJoin('accounts as a', DB::raw("FIND_IN_SET(a.id, f.account_ids)"), ">", DB::raw("0"))
            ->leftJoin('transactions as t', function($join) use ($fromDate, $toDate) {
                $join->on('t.account_id', '=', 'a.id')
                    ->whereBetween('t.transaction_date', [$fromDate, $toDate]);
            })
            ->where('f.is_balancesheet', 1)
            ->groupBy('f.head_name')
            ->orderBy('f.head_name')
            ->get();

        return [
            'from_date' => $fromDate,
            'to_date'   => $toDate,
            'report'    => $report
        ];
    }


    //before profit and loss amount code perfect
    // public function getBalanceSheetSummaryReport(Request $request)
    // {
    //     $startDate = $request->input('start_date') ?: Carbon::now()->startOfMonth()->format('Y-m-d');
    //     $endDate   = $request->input('end_date') ?: Carbon::now()->endOfMonth()->format('Y-m-d');

    //     // Step 1: Fetch all balance sheet heads
    //     $heads = DB::table('financial_statement_heads')
    //         ->where('is_deleted', 0)
    //         ->where('is_balancesheet', 1)
    //         ->orderBy('head_type')
    //         // ->orderBy('head_name')
    //         ->get()
    //         ->groupBy('head_type');

    //     $buildTree = function($parentId, $allHeads, $startDate, $endDate) use (&$buildTree) {
    //         $children = $allHeads->where('sub_head_id', $parentId)->values();
    //         $data = [];

    //         foreach ($children as $child) {
    //             // Decode account IDs (JSON or comma separated)
    //             $accountIds = json_decode($child->account_ids, true);
    //             if (empty($accountIds)) {
    //                 $accountIds = array_filter(explode(',', $child->account_ids));
    //             }

    //             // Fetch accounts totals
    //             $accounts = [];
    //             $headTotal = 0;
    //             if (!empty($accountIds)) {
    //                 $accounts = DB::table('accounts as a')
    //                     ->leftJoin('transactions as t', function($join) use ($startDate, $endDate) {
    //                         $join->on('t.account_id', '=', 'a.id')
    //                             ->whereBetween('t.transaction_date', [$startDate, $endDate]);
    //                     })
    //                     ->select(
    //                         'a.id as account_id',
    //                         'a.account_name',
    //                         'a.account_code',
    //                         DB::raw('CAST(COALESCE(SUM(t.credit - t.debit), 0) AS DECIMAL(15,2)) as total')
    //                     )
    //                     ->whereIn('a.id', $accountIds)
    //                     ->groupBy('a.id', 'a.account_name', 'a.account_code')
    //                     ->get();

    //                 $headTotal = $accounts->sum('total');
    //             }

    //             // Recursive build for subheads
    //             $childNodes = $buildTree($child->id, $allHeads, $startDate, $endDate);
    //             $childTotal = collect($childNodes)->sum('head_total');

    //             $total = $headTotal + $childTotal;

    //             $data[] = [
    //                 'head_id'     => $child->id,
    //                 'head_name'   => $child->head_name,
    //                 'accounts'    => $accounts,
    //                 'children'    => $childNodes,
    //                 'head_total'  => $total
    //             ];
    //         }

    //         return $data;
    //     };

    //     // Step 2: Build tree for Assets and Liabilities
    //     $assets = $buildTree(null, $heads->get('Asset', collect()), $startDate, $endDate);
    //     $liabilities = $buildTree(null, $heads->get('Liability', collect()), $startDate, $endDate);

    //     // Step 3: Totals
    //     $totalAssets = collect($assets)->sum('head_total');
    //     $totalLiabilities = collect($liabilities)->sum('head_total');

    //     // Profit and loss amount for balance sheet
    //     $profit = $this->getProfit($startDate, $endDate);

    //     return [
    //         'from_date' => $startDate,
    //         'to_date'   => $endDate,
    //         'assets'    => $assets,
    //         'liabilities' => $liabilities,
    //         'total_assets' => round($totalAssets, 2),
    //         'total_liabilities' => round($totalLiabilities, 2),
    //         'profit' => $profit
    //     ];
    // }

    public function getBalanceSheetSummaryReport(Request $request)
{
    $startDate = $request->input('start_date') ?: Carbon::now()->startOfMonth()->format('Y-m-d');
    $endDate   = $request->input('end_date') ?: Carbon::now()->endOfMonth()->format('Y-m-d');

    $heads = DB::table('financial_statement_heads')
        ->where('is_deleted', 0)
        ->where('is_balancesheet', 1)
        ->orderBy('head_type')
        ->get()
        ->groupBy('head_type');

    $buildTree = function($parentId, $allHeads, $startDate, $endDate) use (&$buildTree) {
        $children = $allHeads->where('sub_head_id', $parentId)->values();
        $data = [];

        foreach ($children as $child) {
            $accountIds = json_decode($child->account_ids, true);
            if (empty($accountIds)) {
                $accountIds = array_filter(explode(',', $child->account_ids));
            }

            $accounts = [];
            $headTotal = 0;
            if (!empty($accountIds)) {
                $accounts = DB::table('accounts as a')
                    ->leftJoin('transactions as t', function($join) use ($startDate, $endDate) {
                        $join->on('t.account_id', '=', 'a.id')
                            ->whereBetween('t.transaction_date', [$startDate, $endDate]);
                    })
                    ->select(
                        'a.id as account_id',
                        'a.account_name',
                        'a.account_code',
                        DB::raw('CAST(COALESCE(SUM(t.credit - t.debit), 0) AS DECIMAL(15,2)) as total')
                    )
                    ->whereIn('a.id', $accountIds)
                    ->groupBy('a.id', 'a.account_name', 'a.account_code')
                    ->get();

                $headTotal = $accounts->sum('total');
            }

            $childNodes = $buildTree($child->id, $allHeads, $startDate, $endDate);
            $childTotal = collect($childNodes)->sum('head_total');
            $total = $headTotal + $childTotal;

            $data[] = [
                'head_id'     => $child->id,
                'head_name'   => $child->head_name,
                'accounts'    => $accounts,
                'children'    => $childNodes,
                'head_total'  => $total
            ];
        }

        return $data;
    };

    // Assets and Liabilities trees
    $assets = $buildTree(null, $heads->get('Asset', collect()), $startDate, $endDate);
    $liabilities = $buildTree(null, $heads->get('Liability', collect()), $startDate, $endDate);

    // Profit (Net)
    $profit = $this->getProfit($startDate, $endDate);

    // --- Inject profit into Liabilities side ---
    $retainedEarnings = [
        'head_id' => null,
        'head_name' => 'Retained Earnings',
        'accounts' => [],
        'children' => [],
        'head_total' => $profit
    ];

    // Find "Shareholder Funds" head if exists, else add new section
    $shareholderFunds = collect($liabilities)->firstWhere('head_name', 'Shareholder Funds');

    if ($shareholderFunds) {
        // Append retained earnings under Shareholder Funds
        $shareholderFunds['children'][] = $retainedEarnings;
        $shareholderFunds['head_total'] += $profit;

        // Replace in liabilities array
        foreach ($liabilities as &$liab) {
            if ($liab['head_name'] === 'Shareholder Funds') {
                $liab = $shareholderFunds;
            }
        }
    } else {
        // Add as new top-level item under Liabilities
        $liabilities[] = [
            'head_id' => null,
            'head_name' => 'Shareholder Funds',
            'accounts' => [],
            'children' => [$retainedEarnings],
            'head_total' => $profit
        ];
    }

    // Totals
    $totalAssets = collect($assets)->sum('head_total');
    $totalLiabilities = collect($liabilities)->sum('head_total');

    return [
        'from_date' => $startDate,
        'to_date'   => $endDate,
        'assets'    => $assets,
        'liabilities' => $liabilities,
        'total_assets' => round($totalAssets, 2),
        'total_liabilities' => round($totalLiabilities, 2),
        'profit' => $profit
    ];
}


    // get profit for the balance sheet
    public function getProfit($startDate, $endDate)
    {
        $heads = DB::table('financial_statement_heads')->where('is_deleted', '0')
            ->where('is_profit_loss', '1')
            ->get();

        $result = [];

        $incomeTotal   = 0;
        $expenseTotal  = 0;
        $cogsTotal     = 0;

        foreach ($heads as $head) {
             // ✅ Decode JSON string [14,36] → [14, 36]
            $accountIds = json_decode($head->account_ids, true);

            if (empty($accountIds)) {
                continue; // skip if no accounts mapped
            }

            $accounts = DB::table('transactions as t')
                ->join('accounts as a', 't.account_id', '=', 'a.id')
                ->select(
                    't.account_id',
                    'a.account_name',
                    'a.account_code',
                    // DB::raw('SUM(t.credit - t.debit) as total')
                    DB::raw('CAST(SUM(t.credit - t.debit) AS DECIMAL(15,2)) as total')
                )
                ->whereIn('t.account_id', $accountIds)
                ->whereBetween('t.transaction_date', [$startDate, $endDate])
                ->groupBy('t.account_id', 'a.account_name', 'a.account_code')
                ->get();

            $headTotal = $accounts->sum('total');

            // --- Classify head type ---
            $headName = strtolower($head->head_name);

            if (strpos($headName, 'income') !== false) {
                $incomeTotal += $headTotal;
            } elseif (strpos($headName, 'cogs') !== false || strpos($headName, 'cost of goods') !== false) {
                $cogsTotal += $headTotal;
            } else {
                $expenseTotal += $headTotal;
            }
        }
        // ✅ Derived Totals
        $grossProfit = $incomeTotal + $cogsTotal; // cogsTotal is usually negative
        $netProfit   = $incomeTotal + $cogsTotal + $expenseTotal;

        return $netProfit;
    }


    // public function getBalanceSheetSummaryReport(Request $request)
    // {
    //     // ✅ Default: current month
    //     $startDate = $request->input('start_date') ?: Carbon::now()->startOfMonth()->format('Y-m-d');
    //     $endDate   = $request->input('end_date') ?: Carbon::now()->endOfMonth()->format('Y-m-d');

    //     // ✅ Fetch only balance sheet heads (Assets + Liabilities)
    //     $heads = DB::table('financial_statement_heads')
    //         ->where('is_deleted', 0)
    //         ->where('is_balancesheet', 1)
    //         ->orderBy('head_type') // Example: 'Asset' or 'Liability'
    //         ->orderBy('head_name')
    //         ->get();

    //     $assets = [];
    //     $liabilities = [];

    //     foreach ($heads as $head) {
    //         $accountIds = json_decode($head->account_ids, true);

    //         if (empty($accountIds)) {
    //             // Still include head with zero total and no accounts
    //             $headData = [
    //                 'head_name'  => $head->head_name,
    //                 'accounts'   => [],
    //                 'head_total' => 0,
    //             ];
    //         } else {
    //             // ✅ Get all mapped accounts (even if no transactions)
    //             $accounts = DB::table('accounts as a')
    //                 ->leftJoin('transactions as t', function($join) use ($startDate, $endDate) {
    //                     $join->on('t.account_id', '=', 'a.id')
    //                         ->whereBetween('t.transaction_date', [$startDate, $endDate]);
    //                 })
    //                 ->select(
    //                     'a.id as account_id',
    //                     'a.account_name',
    //                     'a.account_code',
    //                     DB::raw('CAST(COALESCE(SUM(t.credit - t.debit), 0) AS DECIMAL(15,2)) as total')
    //                 )
    //                 ->whereIn('a.id', $accountIds)
    //                 ->groupBy('a.id', 'a.account_name', 'a.account_code')
    //                 ->get();

    //             $headTotal = $accounts->sum('total');

    //             $headData = [
    //                 'head_name'  => $head->head_name,
    //                 'accounts'   => $accounts,
    //                 'head_total' => $headTotal,
    //             ];
    //         }

    //         // ✅ Separate into Assets and Liabilities
    //         if (strtolower($head->head_type) === 'asset') {
    //             $assets[] = $headData;
    //         } elseif (strtolower($head->head_type) === 'liability') {
    //             $liabilities[] = $headData;
    //         }
    //     }

    //     // ✅ Totals for both sides
    //     $totalAssets = collect($assets)->sum('head_total');
    //     $totalLiabilities = collect($liabilities)->sum('head_total');

    //     return [
    //         'from_date'       => $startDate,
    //         'to_date'         => $endDate,
    //         'assets'          => $assets,
    //         'liabilities'     => $liabilities,
    //         'total_assets'    => round($totalAssets, 2),
    //         'total_liabilities' => round($totalLiabilities, 2),
    //     ];
    // }

}

