<?php

namespace App\Http\Controllers;

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

class DepSchemewiseReportsController extends Controller
{
    /**
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */


    public function expenditureHeadWise(Request $request)
    {
        try {
            $query = DB::table('budget_head as bh')
                ->leftJoin('budget_entries as be', function($join) use ($request) {
                    $join->on('bh.Bh_Id', '=', 'be.Bh_Id')
                         ->where('be.Is_Latest', '=', 1)
                         ->where('be.Status', '=', 'D');

                    if ($request->filled('financial_year')) {
                        $join->where('be.Fin_Year', '=', $request->financial_year);
                    }
                })
                ->leftJoin('chl_voucher as cv', function($join) use ($request) {
                    $join->on('bh.Bh_Id', '=', 'cv.Bh_Id')
                         ->where('cv.Is_income', '=', 'N')
                         ->whereNull('cv.deleted_at');

                    if ($request->filled('from_date')) {
                        $join->whereDate('cv.created_at', '>=', $request->from_date);
                    }
                    if ($request->filled('to_date')) {
                        $join->whereDate('cv.created_at', '<=', $request->to_date);
                    }
                })
                ->where('bh.Gr_Exp', '=', 'Y')
                ->where('bh.is_del', '=', 'N')
                ->groupBy('bh.Bh_Id', 'bh.Gr_Nm', 'bh.Gr_Cd')
                ->select(
                    'bh.Gr_Nm as expenditure_head',
                    'bh.Gr_Cd as head_code',
                    DB::raw('SUM(be.Amount) as budget_amount'),
                    DB::raw('SUM(cv.Net_Amount) as actual_expenditure'),
                    DB::raw('SUM(be.Amount) - SUM(cv.Net_Amount) as variance'),
                    DB::raw('ROUND(COALESCE(SUM(cv.Net_Amount) / NULLIF(SUM(be.Amount), 0) * 100, 0), 2) as utilization_percentage')
                )
                ->orderBy('bh.Gr_Nm', 'asc');

            if ($request->filled('department_id')) {
                $query->whereExists(function($subquery) use ($request) {
                    $subquery->select(DB::raw(1))
                        ->from('budget_entries as be2')
                        ->whereColumn('be2.Bh_Id', 'bh.Bh_Id')
                        ->where('be2.Dpt_Id', '=', $request->department_id);
                });
            }

            $results = $query->get();

            return response()->json([
                'success' => true,
                'data' => $results,
                'filters' => $request->all(),
                'total_records' => $results->count()
            ], 200);

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

    /**
     * Report 2: Department-wise Expenditure Report
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse

     */

    public function departmentWiseExpenditure(Request $request)
    {
        try {
            $query = DB::table('dept_master as dm')
                ->leftJoin('budget_entries as be', function($join) use ($request) {
                    $join->on('dm.Dpt_id', '=', 'be.Dpt_Id')
                         ->where('be.Is_Latest', '=', 1)
                         ->where('be.Status', '=', 'D');

                    if ($request->filled('financial_year')) {
                        $join->where('be.Fin_Year', '=', $request->financial_year);
                    }
                })
                ->leftJoin('chl_voucher as cv', function($join) use ($request) {
                    $join->on('dm.Dpt_id', '=', 'cv.Dpt_Id')
                         ->where('cv.Is_income', '=', 'N')
                         ->whereNull('cv.deleted_at');

                    if ($request->filled('from_date')) {
                        $join->whereDate('cv.created_at', '>=', $request->from_date);
                    }
                    if ($request->filled('to_date')) {
                        $join->whereDate('cv.created_at', '<=', $request->to_date);
                    }
                })
                ->where('dm.isvisible', '=', 1)
                ->groupBy('dm.Dpt_id', 'dm.Dpt_Name')
                ->select(
                    'dm.Dpt_Name as department_name',
                    'dm.Dpt_id',
                    DB::raw('SUM(be.Amount) as total_budget'),
                    DB::raw('SUM(cv.Net_Amount) as total_expenditure'),
                    DB::raw('SUM(be.Amount) - SUM(cv.Net_Amount) as budget_balance'),
                    DB::raw('ROUND(COALESCE(SUM(cv.Net_Amount) / NULLIF(SUM(be.Amount), 0) * 100, 0), 2) as utilization_percentage')
                )
                ->havingRaw('SUM(be.Amount) > 0 OR SUM(cv.Net_Amount) > 0')
                ->orderBy('dm.Dpt_Name', 'asc');

            if ($request->filled('department_id')) {
                $query->where('dm.Dpt_id', '=', $request->department_id);
            }

            $results = $query->get();

            if ($request->filled('min_utilization')) {
                $results = $results->filter(function($item) use ($request) {
                    return $item->utilization_percentage >= $request->min_utilization;
                });
            }
            if ($request->filled('max_utilization')) {
                $results = $results->filter(function($item) use ($request) {
                    return $item->utilization_percentage <= $request->max_utilization;
                });
            }

            return response()->json([
                'success' => true,
                'data' => $results->values(),
                'filters' => $request->all(),
                'total_records' => $results->count()
            ], 200);

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

    /**
     * Report 3: Scheme-wise Expenditure Report

     * @param Request $request
     * @return \Illuminate\Http\JsonResponse

     */
    public function schemeWiseExpenditure(Request $request)
    {
        try {
            $query = DB::table('budget_head as bh')
                ->leftJoin('budget_entries as be', function($join) use ($request) {
                    $join->on('bh.Bh_Id', '=', 'be.Bh_Id')
                         ->where('be.Is_Latest', '=', 1)
                         ->where('be.Status', '=', 'D');

                    if ($request->filled('financial_year')) {
                        $join->where('be.Fin_Year', '=', $request->financial_year);
                    }
                    if ($request->filled('department_id')) {
                        $join->where('be.Dpt_Id', '=', $request->department_id);
                    }
                })
                ->leftJoin('chl_voucher as cv', function($join) use ($request) {
                    $join->on('bh.Bh_Id', '=', 'cv.Bh_Id')
                         ->where('cv.Is_income', '=', 'N')
                         ->whereNull('cv.deleted_at');

                    if ($request->filled('from_date')) {
                        $join->whereDate('cv.created_at', '>=', $request->from_date);
                    }
                    if ($request->filled('to_date')) {
                        $join->whereDate('cv.created_at', '<=', $request->to_date);
                    }
                })
                ->leftJoin('dept_master as d', 'be.Dpt_Id', '=', 'd.Dpt_id')
                ->where('bh.Gr_Exp', '=', 'Y')
                ->where('bh.is_del', '=', 'N')
                ->groupBy('bh.Bh_Id', 'bh.Gr_Nm', 'bh.Gr_Cd', 'bh.Gr_Exp', 'd.Dpt_Name')
                ->select(
                    'bh.Gr_Nm as scheme_name',
                    'bh.Gr_Cd as scheme_code',
                    'bh.Gr_Exp as scheme_type',
                    'd.Dpt_Name as department_name',
                    DB::raw('SUM(be.Amount) as budget_allocation'),
                    DB::raw('SUM(cv.Net_Amount) as expenditure'),
                    DB::raw('SUM(be.Amount) - SUM(cv.Net_Amount) as unutilized_amount'),
                    DB::raw('ROUND(COALESCE(SUM(cv.Net_Amount) / NULLIF(SUM(be.Amount), 0) * 100, 0), 2) as utilization_percentage')
                )
                ->orderBy('bh.Gr_Nm', 'asc');

            if ($request->filled('scheme_type')) {
                $query->where('bh.Gr_Exp', '=', $request->scheme_type);
            }

            $results = $query->get();

            return response()->json([
                'success' => true,
                'data' => $results,
                'filters' => $request->all(),
                'total_records' => $results->count()
            ], 200);

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

    /**
     * Report 4: Project-wise Expenditure Report

     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
  
     */
    public function projectWiseExpenditure(Request $request)
    {
        try {
            $query = DB::table('chl_voucher as cv')
                ->leftJoin('dept_master as d', 'cv.Dpt_Id', '=', 'd.Dpt_id')
                ->leftJoin('budget_head as bh', 'cv.Bh_Id', '=', 'bh.Bh_Id')
                ->where('cv.Is_income', '=', 'N')
                ->whereNull('cv.deleted_at')
                ->whereNotNull('cv.FileNo')
                ->where('cv.FileNo', '!=', '')
                ->select(
                    'cv.FileNo as project_no',
                    'cv.SL_Nm as vendor_name',
                    'cv.Remarks as project_description',
                    'd.Dpt_Name as department_name',
                    'bh.Gr_Nm as budget_head',
                    'cv.Ch_Date as work_order_date',
                    'cv.Total_Amount as project_cost',
                    'cv.Net_Amount as paid_amount',
                    DB::raw('cv.Total_Amount - cv.Net_Amount as pending_amount'),
                    DB::raw('ROUND(COALESCE(cv.Net_Amount / NULLIF(cv.Total_Amount, 0) * 100, 0), 2) as payment_percentage'),
                    DB::raw("CASE
                        WHEN cv.Status = 12 THEN 'Draft'
                        WHEN cv.Status = 13 THEN 'Verified'
                        WHEN cv.Status = 14 THEN 'Audit Check'
                        WHEN cv.Status = 15 THEN 'Approved'
                        WHEN cv.Status = 24 THEN 'Account Verified'
                        WHEN cv.Status = 25 THEN 'Account HOD Approved'
                        WHEN cv.Status = 30 THEN 'Query'
                        ELSE 'Other'
                    END as payment_status")
                )
                ->orderBy('cv.created_at', 'desc');

            if ($request->filled('from_date')) {
                $query->whereDate('cv.Ch_Date', '>=', $request->from_date);
            }
            if ($request->filled('to_date')) {
                $query->whereDate('cv.Ch_Date', '<=', $request->to_date);
            }

            if ($request->filled('department_id')) {
                $query->where('cv.Dpt_Id', '=', $request->department_id);
            }

            if ($request->filled('status')) {
                $query->where('cv.Status', '=', $request->status);
            }

            if ($request->filled('min_amount')) {
                $query->where('cv.Total_Amount', '>=', $request->min_amount);
            }
            if ($request->filled('max_amount')) {
                $query->where('cv.Total_Amount', '<=', $request->max_amount);
            }

            $results = $query->get();

            return response()->json([
                'success' => true,
                'data' => $results,
                'filters' => $request->all(),
                'total_records' => $results->count(),
                'summary' => [
                    'total_project_cost' => $results->sum('project_cost'),
                    'total_paid_amount' => $results->sum('paid_amount'),
                    'total_pending_amount' => $results->sum('pending_amount')
                ]
            ], 200);

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

    /**
     * Report 5: Work Order-wise Expenditure Report

     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
  
     */
    public function workOrderWiseExpenditure(Request $request)
    {
        try {
            $query = DB::table('chl_voucher as cv')
                ->leftJoin('dept_master as d', 'cv.Dpt_Id', '=', 'd.Dpt_id')
                ->leftJoin('budget_head as bh', 'cv.Bh_Id', '=', 'bh.Bh_Id')
                ->where('cv.Is_income', '=', 'N')
                ->whereNull('cv.deleted_at')
                ->whereNotNull('cv.FileNo')
                ->where('cv.FileNo', '!=', '')
                ->select(
                    'cv.FileNo as work_order_number',
                    'cv.SL_Nm as contractor_name',
                    'd.Dpt_Name as department_name',
                    'bh.Gr_Nm as work_category',
                    'cv.Ch_Date as work_order_date',
                    'cv.Ch_Start_Date as work_start_date',
                    'cv.Ch_End_Date as work_end_date',
                    'cv.Total_Amount as work_order_value',
                    'cv.Net_Amount as amount_paid',
                    DB::raw('cv.Total_Amount - cv.Net_Amount as balance_payable'),
                    'cv.Penalty_Amount as penalty_imposed',
                    'cv.Py_Mode as payment_mode',
                    'cv.Chq_No as cheque_number',
                    'cv.Chq_Date as cheque_date',
                    DB::raw("DATE_FORMAT(cv.created_at, '%d/%m/%Y') as entry_date"),
                    DB::raw("CASE
                        WHEN cv.Status = 12 THEN 'Pending Verification'
                        WHEN cv.Status = 13 THEN 'Verified'
                        WHEN cv.Status = 14 THEN 'Under Audit'
                        WHEN cv.Status = 15 THEN 'Approved for Payment'
                        WHEN cv.Status = 24 THEN 'Account Verified'
                        WHEN cv.Status = 25 THEN 'Payment Processed'
                        WHEN cv.Status = 30 THEN 'Query Raised'
                        ELSE 'In Process'
                    END as current_status"),
                    'cv.Status_Remark as status_remarks',
                    'cv.IFSC_Code as bank_ifsc',
                    'cv.Acc_No as bank_account_no'
                )
                ->orderBy('cv.created_at', 'desc');

            if ($request->filled('from_date')) {
                $query->whereDate('cv.Ch_Date', '>=', $request->from_date);
            }
            if ($request->filled('to_date')) {
                $query->whereDate('cv.Ch_Date', '<=', $request->to_date);
            }

            if ($request->filled('department_id')) {
                $query->where('cv.Dpt_Id', '=', $request->department_id);
            }

            if ($request->filled('status')) {
                $query->where('cv.Status', '=', $request->status);
            }

            if ($request->filled('contractor_name')) {
                $query->where('cv.SL_Nm', 'LIKE', '%' . $request->contractor_name . '%');
            }

            if ($request->filled('work_order_number')) {
                $query->where('cv.FileNo', 'LIKE', '%' . $request->work_order_number . '%');
            }

            $results = $query->get();

            return response()->json([
                'success' => true,
                'data' => $results,
                'filters' => $request->all(),
                'total_records' => $results->count(),
                'summary' => [
                    'total_work_order_value' => $results->sum('work_order_value'),
                    'total_amount_paid' => $results->sum('amount_paid'),
                    'total_balance_payable' => $results->sum('balance_payable'),
                    'total_penalty_imposed' => $results->sum('penalty_imposed')
                ]
            ], 200);

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

    /**
     * @return \Illuminate\Http\JsonResponse
     */
    
    public function getFilterOptions()
    {
        try {
            $departments = DB::table('dept_master')
                ->where('isvisible', 1)
                ->select('Dpt_id as id', 'Dpt_Name as name')
                ->orderBy('Dpt_Name')
                ->get();

            $financialYears = DB::table('fyear_master')
                ->select('FY_Id as id', 'FY_Name as name', 'FY_Code as code')
                ->orderBy('FY_Id', 'desc')
                ->get();

            $statusOptions = [
                ['value' => 12, 'label' => 'Draft'],
                ['value' => 13, 'label' => 'Verified'],
                ['value' => 14, 'label' => 'Audit Check'],
                ['value' => 15, 'label' => 'Approved'],
                ['value' => 24, 'label' => 'Account Verified'],
                ['value' => 25, 'label' => 'Account HOD Approved'],
                ['value' => 30, 'label' => 'Query']
            ];

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

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