
<?php


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

class DepartmentWiseVoucherReportController extends Controller
{

    public function getDepartmentReport(Request $request)
    {
        try {
            
            $validated = $request->validate([
                'date_from' => 'nullable|date_format:Y-m-d',
                'date_to' => 'nullable|date_format:Y-m-d',
                'pay_mode' => 'nullable|string|max:50',
                'income_type' => 'nullable|in:Y,N',
                'status_code' => 'nullable|integer',
                'min_amount' => 'nullable|numeric',
                'max_amount' => 'nullable|numeric',
                'department_id' => 'nullable|integer',
            ]);

            $reportData = $this->getDepartmentWiseData($validated);

            $departmentTotals = $this->getDepartmentTotals($validated);

            $grandTotals = $this->getGrandTotals($departmentTotals);

            $response = $this->formatReportResponse($reportData, $departmentTotals, $grandTotals);

            return response()->json([
                'success' => true,
                'message' => 'Department wise report fetched successfully',
                'data' => $response,
                'filters' => $validated
            ], 200);

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

 
    private function getDepartmentWiseData($filters)
    {
        $query = DB::table('chl_voucher as c')
            ->select([
                'c.Dpt_Id',
                'c.Ch_Date',
                'c.Py_Mode',
                'c.Is_income',
                'c.Amount',
                'c.Status',
                DB::raw("COALESCE(d.Dpt_Name, 'Uncategorized') as department_name"),
                DB::raw("DATE_FORMAT(c.Ch_Date, '%Y-%m-%d') as date_val"),
                DB::raw("CONCAT(COALESCE(c.Py_Mode, 'N/A'), ' - ', IF(c.Is_income = 'Y', 'Income', 'Expense')) as particular"),
                DB::raw("IF(c.Is_income = 'Y', c.Amount, -c.Amount) as amount"),
                DB::raw("COALESCE(a.St_Name,
                    CASE
                        WHEN c.Status = 1 THEN 'Completed'
                        WHEN c.Status = 0 THEN 'Pending'
                        ELSE 'Draft'
                    END
                ) as entry_status")
            ])
            ->leftJoin('dept_master as d', 'c.Dpt_Id', '=', 'd.Dpt_Id')
            ->leftJoin('action_master as a', 'c.Status', '=', 'a.Act_Id')
            ->whereNull('c.deleted_at')
            ->whereNotNull('c.Ch_Date');

        
        $this->applyFilters($query, $filters);

        
        $query->orderBy('department_name')
              ->orderBy('c.Ch_Date', 'DESC');

        $results = $query->get();

        
        $serialNo = 1;
        $formattedResults = [];

        foreach ($results as $result) {
            $formattedResults[] = [
                'sr_no' => $serialNo++,
                'department' => $result->department_name,
                'date' => $result->date_val,
                'particular' => $result->particular,
                'amount' => $result->amount,
                'status' => $result->entry_status,
                'type' => 'detail'
            ];
        }

        return $formattedResults;
    }

 
    private function getDepartmentTotals($filters)
    {
        $query = DB::table('chl_voucher as c')
            ->select([
                DB::raw("COALESCE(d.Dpt_Name, 'Uncategorized') as department_name"),
                DB::raw("SUM(IF(c.Is_income = 'Y', c.Amount, 0)) as total_income"),
                DB::raw("SUM(IF(c.Is_income = 'Y', 0, c.Amount)) as total_expense"),
                DB::raw("SUM(IF(c.Is_income = 'Y', c.Amount, -c.Amount)) as net_balance"),
                DB::raw("COUNT(IF(c.Is_income = 'Y', 1, NULL)) as income_count"),
                DB::raw("COUNT(IF(c.Is_income != 'Y', 1, NULL)) as expense_count")
            ])
            ->leftJoin('dept_master as d', 'c.Dpt_Id', '=', 'd.Dpt_Id')
            ->whereNull('c.deleted_at')
            ->whereNotNull('c.Ch_Date')
            ->groupBy('department_name');

        
        $this->applyFilters($query, $filters);

        $results = $query->get();
        $formattedTotals = [];

        foreach ($results as $result) {
            $formattedTotals[] = [
                'department' => $result->department_name,
                'total_income' => (float) $result->total_income,
                'total_expense' => (float) $result->total_expense,
                'net_balance' => (float) $result->net_balance,
                'income_count' => (int) $result->income_count,
                'expense_count' => (int) $result->expense_count,
                'total_records' => (int) $result->income_count + (int) $result->expense_count
            ];
        }

        return $formattedTotals;
    }

 
    private function getGrandTotals($departmentTotals)
    {
        $grandTotals = [
            'grand_total_income' => 0,
            'grand_total_expense' => 0,
            'grand_net_balance' => 0,
            'total_records' => 0
        ];

        foreach ($departmentTotals as $dept) {
            $grandTotals['grand_total_income'] += $dept['total_income'];
            $grandTotals['grand_total_expense'] += $dept['total_expense'];
            $grandTotals['grand_net_balance'] += $dept['net_balance'];
            $grandTotals['total_records'] += $dept['total_records'];
        }

        return $grandTotals;
    }

  
    private function applyFilters(&$query, $filters)
    {
        if (!empty($filters['date_from'])) {
            $query->whereDate('c.Ch_Date', '>=', $filters['date_from']);
        }

        if (!empty($filters['date_to'])) {
            $query->whereDate('c.Ch_Date', '<=', $filters['date_to']);
        }

        if (!empty($filters['pay_mode'])) {
            $query->where('c.Py_Mode', $filters['pay_mode']);
        }

        if (!empty($filters['income_type'])) {
            $query->where('c.Is_income', $filters['income_type']);
        }

        if (!empty($filters['status_code'])) {
            $query->where('c.Status', $filters['status_code']);
        }

        if (!empty($filters['min_amount'])) {
            $query->whereRaw('ABS(c.Amount) >= ?', [$filters['min_amount']]);
        }

        if (!empty($filters['max_amount'])) {
            $query->whereRaw('ABS(c.Amount) <= ?', [$filters['max_amount']]);
        }

        if (!empty($filters['department_id'])) {
            $query->where('c.Dpt_Id', $filters['department_id']);
        }
    }

 
    private function formatReportResponse($reportData, $departmentTotals, $grandTotals)
    {
        $response = [
            'details' => $reportData,
            'department_summary' => [],
            'grand_summary' => [
                'total_income' => $grandTotals['grand_total_income'],
                'total_expense' => $grandTotals['grand_total_expense'],
                'net_balance' => $grandTotals['grand_net_balance'],
                'total_records' => $grandTotals['total_records']
            ]
        ];

        
        foreach ($departmentTotals as $dept) {
            $response['department_summary'][] = [
                'department' => $dept['department'],
                'summary' => [
                    'total_income' => $dept['total_income'],
                    'total_expense' => $dept['total_expense'],
                    'net_balance' => $dept['net_balance'],
                    'total_records' => $dept['total_records']
                ],
                'breakdown' => [
                    'income_count' => $dept['income_count'],
                    'expense_count' => $dept['expense_count']
                ]
            ];
        }

        return $response;
    }

 
    public function exportCSV(Request $request)
    {
        try {
            $validated = $request->validate([
                'date_from' => 'nullable|date_format:Y-m-d',
                'date_to' => 'nullable|date_format:Y-m-d',
                'pay_mode' => 'nullable|string|max:50',
                'income_type' => 'nullable|in:Y,N',
                'status_code' => 'nullable|integer',
                'department_id' => 'nullable|integer',
            ]);

            $reportData = $this->getDepartmentWiseData($validated);
            $departmentTotals = $this->getDepartmentTotals($validated);
            $grandTotals = $this->getGrandTotals($departmentTotals);

            $fileName = 'department_report_' . date('Y-m-d_His') . '.csv';

            $callback = function() use ($reportData, $departmentTotals, $grandTotals) {
                $file = fopen('php://output', 'w');

                fputcsv($file, ['DEPARTMENT WISE FINANCIAL REPORT']);
                fputcsv($file, ['Generated on: ' . date('Y-m-d H:i:s')]);
                fputcsv($file, []);

                fputcsv($file, ['Sr. No.', 'Department', 'Date', 'Particular', 'Amount', 'Status']);

                foreach ($reportData as $row) {
                    fputcsv($file, [
                        $row['sr_no'],
                        $row['department'],
                        $row['date'],
                        $row['particular'],
                        $row['amount'],
                        $row['status']
                    ]);
                }

                fputcsv($file, []);
                fputcsv($file, ['DEPARTMENT SUMMARY']);

                foreach ($departmentTotals as $dept) {
                    fputcsv($file, [$dept['department'] . ' - Total Income', '', '', '', $dept['total_income']]);
                    fputcsv($file, [$dept['department'] . ' - Total Expense', '', '', '', $dept['total_expense']]);
                    fputcsv($file, [$dept['department'] . ' - Net Balance', '', '', '', $dept['net_balance']]);
                    fputcsv($file, [$dept['department'] . ' - Total Records', '', '', '', $dept['total_records']]);
                    fputcsv($file, []);
                }

                fputcsv($file, ['GRAND TOTALS']);
                fputcsv($file, ['Total Income', '', '', '', $grandTotals['grand_total_income']]);
                fputcsv($file, ['Total Expense', '', '', '', $grandTotals['grand_total_expense']]);
                fputcsv($file, ['Net Balance', '', '', '', $grandTotals['grand_net_balance']]);
                fputcsv($file, ['Total Records', '', '', '', $grandTotals['total_records']]);

                fclose($file);
            };

            return response()->stream($callback, 200, [
                'Content-Type' => 'text/csv',
                'Content-Disposition' => 'attachment; filename="' . $fileName . '"',
            ]);

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

 
    public function getDepartmentsList()
    {
        try {
            $departments = DB::table('dept_master')
                ->select('Dpt_Id', 'Dpt_Name')
                ->whereNull('deleted_at')
                ->orderBy('Dpt_Name')
                ->get();

            return response()->json([
                'success' => true,
                'data' => $departments
            ], 200);

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