<?php

namespace App\Exports;

use App\Models\OBF;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use DB;
use Carbon\Carbon;

class ExportRefundReport implements FromCollection, WithHeadings, WithStyles, ShouldAutoSize
{
    protected $slug;

    function __construct($slug) {
        $this->slug = $slug;
    }
    
    public function collection()
    {
        $data = $this->getQuery()->get();

        if ($data->isEmpty()) {
            throw new \ErrorException('No Data Found!');
        }

        $sr = 1;

        return $data->map(function ($row) use (&$sr) {
            return [
                $sr++, // No

                $row->customer_name,
                $row->branch,
                $row->sales_person_name,
                $row->team_leader,
                $row->model,
                $row->car_varient_name,
                $row->on_road_price,

                $row->total_received_amount,
                $row->due_amount,

                $row->liquidation_amount,
                $row->booking_date ? Carbon::parse($row->booking_date)->format('d-m-Y h:i A') : '',

                $row->booking_receipt_no,
                $row->payment_type,

                $row->booking_refund_amount,
                $row->cancellation_reason_kec,
                $row->cancellation_reason_gm,

                $row->Accepted_date_time_kec ? Carbon::parse($row->Accepted_date_time_kec)->format('d-m-Y h:i A') : '',
                $row->Accepted_date_time_tl ? Carbon::parse($row->Accepted_date_time_tl)->format('d-m-Y h:i A') : '',
                $row->Accepted_date_time_sm ? Carbon::parse($row->Accepted_date_time_sm)->format('d-m-Y h:i A') : '',
                $row->Accepted_date_time_gm ? Carbon::parse($row->Accepted_date_time_gm)->format('d-m-Y h:i A') : '',
                $row->Accepted_date_time_bh ? Carbon::parse($row->Accepted_date_time_bh)->format('d-m-Y h:i A') : '',
                $row->Accepted_date_time_account ? Carbon::parse($row->Accepted_date_time_account)->format('d-m-Y h:i A') : '',

                $row->cheque_amount,
                $row->cheque_number,
                $row->cheque_date ? Carbon::parse($row->cheque_date)->format('d-m-Y') : '',
                $row->bank_account_number,

                $row->account_remark,
                $this->formatApprovalStatus($row->approval_status),
            ];
        });
    }

    private function getQuery()
    {
        return OBF::select(
            'obf.id',
            'obf.customer_name',
            'branches.name as branch',
            DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
            'Team_master.team_name as team_leader',
            'car_model.name AS model',
            'car_varient.name AS car_varient_name',
            'obf.on_road_price',

            'cr.total_received_amount',
            DB::raw('obf.on_road_price - COALESCE(cr.total_received_amount, 0) AS due_amount'),

            'cr.booking_receipt_no',
            'cr.payment_type',

            'liquidation.liquidation_amount',
            'obf.booking_date',

            'obf.refund_amount as booking_refund_amount',
            'obf.refund_remark as cancellation_reason_kec',

            'lg.gm_remark as cancellation_reason_gm',

            'obf.updated_at as Accepted_date_time_kec',

            'lg.tl_date as Accepted_date_time_tl',
            'lg.sm_date as Accepted_date_time_sm',
            'lg.gm_date as Accepted_date_time_gm',
            'lg.bh_date as Accepted_date_time_bh',
            'lg.accounts_date as Accepted_date_time_account',

            'liquidation_cash_receipt.liquidation_amount as cheque_amount',
            'liquidation_cash_receipt.transaction_no as cheque_number',
            'liquidation_cash_receipt.created_at as cheque_date',
            'bank_account.name as bank_account_number',

            'lg.account_remark as account_remark',
            'liquidation.approval_status'
        )

        ->leftJoin('liquidation','liquidation.obf_id','=','obf.id')
        ->leftJoin('branches','branches.id','=','obf.branch_id')
        ->leftJoin('users','users.id','=','obf.sales_person_id')
        ->leftJoin('users as refund_by','refund_by.id','=','obf.created_by')
        ->leftJoin('Team_master','Team_master.id','=','users.team_name')
        ->leftJoin('products' ,'obf.product_id' ,'products.id')
        ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
        ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')

        ->leftJoin(DB::raw("
            (SELECT 
                cr.obf_id,
                SUM(cr.amount) as total_received_amount,

                GROUP_CONCAT(
                    CONCAT(
                        cr.id,
                        CASE 
                            WHEN b.name LIKE '%Rajkot%' THEN 'RJT'
                            WHEN b.name LIKE '%Jamnagar%' THEN 'Jam'
                            WHEN b.name LIKE '%Junagadh%' THEN 'Juna'
                            WHEN b.name LIKE '%Morbi%' THEN 'Mor'
                            WHEN b.name LIKE '%_K%' THEN 'IBK'
                            WHEN b.name LIKE '%_M%' THEN 'IBM'
                            ELSE ''
                        END,
                        DATE_FORMAT(NOW(), '%m'),
                        DATE_FORMAT(NOW(), '%Y')
                    ) SEPARATOR ',\n'
                ) as booking_receipt_no,

                GROUP_CONCAT(cr.receipt_type SEPARATOR ',\n') as payment_type

            FROM cash_receipt cr
            LEFT JOIN obf o ON o.id = cr.obf_id
            LEFT JOIN branches b ON b.id = o.branch_id
            GROUP BY cr.obf_id
        ) as cr"), 'cr.obf_id', '=', 'obf.id')

        ->leftJoin(DB::raw("
            (SELECT 
                module_id,

                MAX(CASE WHEN status = 'liquidation_tl_accepted' THEN created_at END) as tl_date,
                MAX(CASE WHEN status = 'liquidation_sm_accepted' THEN created_at END) as sm_date,
                MAX(CASE WHEN status = 'liquidation_gm_accepted' THEN created_at END) as gm_date,
                MAX(CASE WHEN status = 'liquidation_bh_accepted' THEN created_at END) as bh_date,
                MAX(CASE WHEN status = 'liquidation_accountant_accepted' THEN created_at END) as accounts_date,

                MAX(CASE WHEN status = 'liquidation_gm_accepted' THEN remark END) as gm_remark,
                MAX(CASE WHEN status = 'liquidation_accountant_accepted' THEN remark END) as account_remark

            FROM logs
            WHERE module_nm = 'liquidation_approval'
            GROUP BY module_id
        ) as lg"), 'lg.module_id', '=', 'obf.id')

        ->leftJoin('liquidation_cash_receipt','liquidation_cash_receipt.liquidation_id','=','liquidation.id')
        ->leftJoin('bank_account','bank_account.id','=','liquidation_cash_receipt.bank_account_id')

        ->whereIn('liquidation.liquidation_type',['booking_cancel','excess_amount'])
        ->whereIn('liquidation.approval_status', [
            'liquidation_bh_accepted',
            'liquidation_accountant_accepted',
            'cheque_ready'
        ])

        ->groupBy('obf.id')
        ->orderBy('obf.id','desc');
    }
    
    private function formatApprovalStatus($status)
    {
        $statuses = [
            'cheque_ready' => 'Cheque Ready',
            'liquidation_accountant_accepted' => 'Liquidation Accountant Accepted',
            'liquidation_bh_accepted' => 'Liquidation BH Accepted',
        ];
    
        return $statuses[$status] ?? $status; // fallback if new status comes
    }

    public function headings(): array
    {
        return [
            "No","Customer Name","Branch","KEC Name","Team Leader","Model","Varient",
            "On Road Price","Total Received Amount","Due Amount","Liquidation Amount",
            "Booking Date & Time","Booking Receipt No.","Payment Type / Mode",
            "Booking Refund Amt","Reason for Cancellation - KEC","Reason for Cancellation - GM",
            "KEC Accepted Date & Time","TL Accepted Date & Time","SM Accepted Date & Time",
            "GM Accepted Date & Time","BH Accepted Date & Time","Account Accepted Date & Time",
            "Cheque Amount","Cheque Number","Cheque Date","Bank Account Number",
            "Account Remark","Approval Status"
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}