<?php

namespace App\Http\Controllers;

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

class BudgetFormateReportController extends Controller
{
    
    public function getDepartmentWiseBudget(Request $request)
    {
        try {
            $finYear = $request->input('fin_year', 3);
            $status = $request->input('status');
            $isLatest = $request->input('is_latest', 1);

            $query = DB::table('budget_entries as b')
                ->leftJoin('dept_master as d', 'b.Dpt_Id', '=', 'd.Dpt_Id')
                ->select(
                    'b.Dpt_Id',
                    DB::raw("COALESCE(d.Dpt_Name, 'Unknown') as Department_Name"),
                    DB::raw("COUNT(b.Budg_Id) as No_Of_Budget_Heads"),
                    DB::raw("SUM(b.Amount) as Total_Budget_Amount"),
                    DB::raw("FORMAT(SUM(b.Amount), 2) as Formatted_Amount"),
                    'b.Status'
                )
                ->where('b.Is_Latest', $isLatest)
                ->whereNull('b.deleted_at')
                ->where('b.Fin_Year', $finYear);

            if ($status) {
                $query->where('b.Status', $status);
            }

            $results = $query->groupBy('b.Dpt_Id', 'b.Status', 'd.Dpt_Name')
                ->orderByRaw('SUM(b.Amount) DESC')
                ->get();

            
            $totalBudget = $results->sum('Total_Budget_Amount');

            return response()->json([
                'success' => true,
                'data' => [
                    'filters' => [
                        'financial_year' => $finYear,
                        'status' => $status ?? 'All',
                        'is_latest' => $isLatest == 1
                    ],
                    'summary' => [
                        'total_departments' => $results->count(),
                        'total_budget_heads' => $results->sum('No_Of_Budget_Heads'),
                        'grand_total_budget' => $totalBudget,
                        'grand_total_formatted' => '₹ ' . number_format($totalBudget, 2),
                        'grand_total_in_cr' => '₹ ' . round($totalBudget / 10000000, 2) . ' Cr'
                    ],
                    'department_wise_budget' => $results
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch department wise budget',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

   
    public function getHeadWiseBudget(Request $request)
    {
        try {
            $finYear = $request->input('fin_year', 3);
            $dptId = $request->input('dpt_id');
            $minAmount = $request->input('min_amount');
            $maxAmount = $request->input('max_amount');
            $isLatest = $request->input('is_latest', 1);

            $query = DB::table('budget_entries as b')
                ->leftJoin('budget_head as bh', 'b.Bh_Id', '=', 'bh.Bh_Id')
                ->select(
                    'b.Bh_Id',
                    DB::raw("COALESCE(bh.Gr_Cd, 'N/A') as Head_Code"),
                    DB::raw("COALESCE(bh.Gr_Nm, 'Unknown') as Head_Name"),
                    'b.Dpt_Id',
                    DB::raw("SUM(b.Amount) as Budget_Amount"),
                    DB::raw("FORMAT(SUM(b.Amount), 2) as Formatted_Amount"),
                    DB::raw("CONCAT(ROUND(SUM(b.Amount)/10000000, 2), ' Cr') as Amount_In_Crore"),
                    DB::raw("CASE
                        WHEN bh.Gr_Exp = 'Y' THEN 'Expenditure'
                        WHEN bh.Gr_Exp = 'N' THEN 'Revenue'
                        ELSE 'Other'
                    END as Budget_Type")
                )
                ->where('b.Is_Latest', $isLatest)
                ->whereNull('b.deleted_at')
                ->where('b.Fin_Year', $finYear);

            if ($dptId) {
                $query->where('b.Dpt_Id', $dptId);
            }

            $results = $query->groupBy('b.Bh_Id', 'bh.Gr_Cd', 'bh.Gr_Nm', 'b.Dpt_Id', 'bh.Gr_Exp')
                ->having('Budget_Amount', '>', 0);

            if ($minAmount) {
                $results->having('Budget_Amount', '>=', $minAmount);
            }
            if ($maxAmount) {
                $results->having('Budget_Amount', '<=', $maxAmount);
            }

            $results = $results->orderByRaw('SUM(b.Amount) DESC')
                ->get();

            
            $totalBudget = $results->sum('Budget_Amount');
            $revenueBudget = $results->where('Budget_Type', 'Revenue')->sum('Budget_Amount');
            $expenditureBudget = $results->where('Budget_Type', 'Expenditure')->sum('Budget_Amount');

            return response()->json([
                'success' => true,
                'data' => [
                    'filters' => [
                        'financial_year' => $finYear,
                        'department_id' => $dptId ?? 'All',
                        'min_amount' => $minAmount ?? 'No limit',
                        'max_amount' => $maxAmount ?? 'No limit'
                    ],
                    'summary' => [
                        'total_budget_heads' => $results->count(),
                        'total_budget' => $totalBudget,
                        'total_budget_formatted' => '₹ ' . number_format($totalBudget, 2),
                        'total_budget_in_cr' => '₹ ' . round($totalBudget / 10000000, 2) . ' Cr',
                        'revenue_budget' => '₹ ' . number_format($revenueBudget, 2),
                        'expenditure_budget' => '₹ ' . number_format($expenditureBudget, 2)
                    ],
                    'head_wise_budget' => $results
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch head wise budget',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

    public function getRevisedBudgetReport(Request $request)
    {
        try {
            $finYear = $request->input('fin_year', 3);
            $status = $request->input('status');
            $refId = $request->input('ref_id');
            $isLatest = $request->input('is_latest');

            $query = DB::table('budget_entries as b')
                ->leftJoin('dept_master as d', 'b.Dpt_Id', '=', 'd.Dpt_Id')
                ->leftJoin('budget_head as bh', 'b.Bh_Id', '=', 'bh.Bh_Id')
                ->select(
                    'b.Budg_Id',
                    'b.Dpt_Id',
                    DB::raw("COALESCE(d.Dpt_Name, 'Unknown') as Department_Name"),
                    'b.Bh_Id',
                    DB::raw("COALESCE(bh.Gr_Nm, 'Unknown') as Head_Name"),
                    DB::raw("FORMAT(b.Amount, 2) as Current_Amount"),
                    'b.Amount as Sort_Amount',
                    'b.Status',
                    'b.Ref_Id as Parent_Entry_Id',
                    'b.Is_Latest',
                    DB::raw("CASE
                        WHEN b.Ref_Id = 0 THEN 'Original Budget'
                        ELSE CONCAT('Revision #', b.Ref_Id)
                    END as Version_Type"),
                    DB::raw("DATE_FORMAT(b.created_at, '%d-%m-%Y %H:%i:%s') as Revision_Date"),
                    'b.created_at as Sort_Date',
                    DB::raw("CONCAT('v', (
                        SELECT COUNT(*) + 1
                        FROM budget_entries b2
                        WHERE b2.Ref_Id = b.Budg_Id OR b2.Budg_Id = b.Ref_Id
                    )) as Revision_Number")
                )
                ->whereNull('b.deleted_at')
                ->where('b.Fin_Year', $finYear);

            if ($status) {
                $query->whereIn('b.Status', explode(',', $status));
            } else {
                $query->whereIn('b.Status', ['R', 'RR', 'D']);
            }

            if ($refId) {
                $query->where('b.Ref_Id', $refId);
            }

            if ($isLatest !== null) {
                $query->where('b.Is_Latest', $isLatest);
            }

            $results = $query->orderBy('b.Ref_Id')
                ->orderBy('b.Budg_Id')
                ->get();

            $revisionChains = [];
            foreach ($results->where('Parent_Entry_Id', 0) as $original) {
                $chain = [$original];
                $currentId = $original->Budg_Id;
                while ($next = $results->firstWhere('Parent_Entry_Id', $currentId)) {
                    $chain[] = $next;
                    $currentId = $next->Budg_Id;
                }
                if (count($chain) > 1) {
                    $revisionChains[$original->Budg_Id] = $chain;
                }
            }

            return response()->json([
                'success' => true,
                'data' => [
                    'filters' => [
                        'financial_year' => $finYear,
                        'status' => $status ?? 'R,RR,D',
                        'ref_id' => $refId ?? 'All'
                    ],
                    'summary' => [
                        'total_revised_entries' => $results->count(),
                        'original_budgets' => $results->where('Parent_Entry_Id', 0)->count(),
                        'revisions' => $results->where('Parent_Entry_Id', '>', 0)->count(),
                        'total_revision_amount' => $results->where('Parent_Entry_Id', '>', 0)->sum('Sort_Amount')
                    ],
                    'revision_chains' => $revisionChains,
                    'all_revised_entries' => $results
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to fetch revised budget report',
                'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
            ], 500);
        }
    }

public function getStatusWiseBudget(Request $request)
{
    try {
        $finYear = $request->input('fin_year', 3);
        $isLatest = $request->input('is_latest', 1);
        $specificStatus = $request->input('status');

        
        $summaryQuery = DB::table('budget_entries as b')
            ->select(
                'b.Status',
                DB::raw("COUNT(b.Budg_Id) as Total_Entries"),
                DB::raw("SUM(b.Amount) as Total_Amount"),
                DB::raw("FORMAT(SUM(b.Amount), 2) as Formatted_Amount"),
                DB::raw("CONCAT(ROUND(SUM(b.Amount)/10000000, 2), ' Cr') as Amount_In_Crore"),
                DB::raw("COUNT(DISTINCT b.Dpt_Id) as Departments_Involved"),
                DB::raw("ROUND(SUM(b.Amount) / NULLIF((SELECT SUM(Amount) FROM budget_entries WHERE Is_Latest = 1 AND Fin_Year = {$finYear} AND deleted_at IS NULL), 0) * 100, 2) as Percentage_Share")
            )
            ->where('b.Is_Latest', $isLatest)
            ->whereNull('b.deleted_at')
            ->where('b.Fin_Year', $finYear);

        if ($specificStatus) {
            $summaryQuery->where('b.Status', $specificStatus);
        }

        $statusSummary = $summaryQuery->groupBy('b.Status')
            ->orderByRaw('SUM(b.Amount) DESC')
            ->get();

        
        $statusLabels = [
            'D' => 'Draft',
            'R' => 'Revised',
            'P' => 'Approved',
            'F' => 'Frozen',
            'A' => 'Active',
            'I' => 'Inactive',
            'RR' => 'Re-Revised',
            'RJ' => 'Rejected',
            'J' => 'Justification Pending'
        ];

        $icons = [
            'D' => '📄', 'R' => '🔄', 'P' => '✅', 'F' => '❄️',
            'A' => '▶️', 'I' => '⏸️', 'RR' => '🔁', 'RJ' => '🚫', 'J' => '📝'
        ];

        $resultData = [];

        foreach ($statusSummary as $summary) {
            
            $budgetHeadsQuery = DB::table('budget_entries as b')
                ->leftJoin('budget_head as bh', 'b.Bh_Id', '=', 'bh.Bh_Id')
                ->leftJoin('dept_master as d', 'b.Dpt_Id', '=', 'd.Dpt_Id')
                ->select(
                    'b.Budg_Id',
                    'b.Bh_Id',
                    DB::raw("COALESCE(bh.Gr_Cd, 'N/A') as Head_Code"),
                    DB::raw("COALESCE(bh.Gr_Nm, 'Unknown') as Head_Name"),
                    'b.Dpt_Id',
                    DB::raw("COALESCE(d.Dpt_Name, 'Unknown') as Department_Name"),
                    'b.Amount',
                    DB::raw("FORMAT(b.Amount, 2) as Formatted_Amount"),
                    DB::raw("CONCAT(ROUND(b.Amount/10000000, 2), ' Cr') as Amount_In_Crore"),
                    'b.Status',
                    'b.Ref_Id',
                    'b.created_at'
                )
                ->where('b.Is_Latest', $isLatest)
                ->whereNull('b.deleted_at')
                ->where('b.Fin_Year', $finYear)
                ->where('b.Status', $summary->Status);

            $budgetHeads = $budgetHeadsQuery->orderBy('b.Amount', 'DESC')
                ->get();

            
            $resultData[] = [
                'status' => $summary->Status,
                'status_label' => $statusLabels[$summary->Status] ?? 'Other',
                'status_icon' => $icons[$summary->Status] ?? '❓',
                'summary' => [
                    'total_entries' => $summary->Total_Entries,
                    'total_amount' => $summary->Total_Amount,
                    'formatted_amount' => $summary->Formatted_Amount,
                    'amount_in_crore' => $summary->Amount_In_Crore,
                    'departments_involved' => $summary->Departments_Involved,
                    'percentage_share' => $summary->Percentage_Share
                ],
                'budget_heads' => $budgetHeads
            ];
        }

        
        $totalBudget = $statusSummary->sum('Total_Amount');
        $totalEntries = $statusSummary->sum('Total_Entries');

        return response()->json([
            'success' => true,
            'data' => [
                'filters' => [
                    'financial_year' => $finYear,
                    'is_latest' => $isLatest == 1,
                    'specific_status' => $specificStatus ?? 'All'
                ],
                'summary' => [
                    'total_entries_all_status' => $totalEntries,
                    'total_budget_all_status' => $totalBudget,
                    'total_budget_formatted' => '₹ ' . number_format($totalBudget, 2),
                    'total_budget_in_cr' => '₹ ' . round($totalBudget / 10000000, 2) . ' Cr',
                    'status_count' => $statusSummary->count()
                ],
                'status_wise_breakdown' => $resultData
            ]
        ], 200);

    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Failed to fetch status wise budget',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
        ], 500);
    }
}
}
