<?php

namespace App\Exports;

use DB;
use App\Models\Branch;
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;

class ExportBookingDetails implements FromCollection, WithHeadings,WithStyles ,ShouldAutoSize{
    protected $ids;
    function __construct($ids) {
        $this->ids = $ids;
    }

    public function collection(){
        $model = new Branch();
        $bookingReceipt = DB::table('cash_receipt')
            ->selectRaw('obf_id, SUM(amount) as booking_amount')
            ->where('receipt_type', 'booking_receipt')
            ->where('status', '!=', 'rejected')
            ->groupBy('obf_id');
        
        $dpReceipt = DB::table('cash_receipt')
            ->selectRaw('obf_id, SUM(amount) as dp_amount')
            ->where('receipt_type', 'downpayment_receipt')
            ->where('status', '!=', 'rejected')
            ->groupBy('obf_id');
        
        $netDisbusment = DB::table('cash_receipt')
            ->selectRaw('obf_id, SUM(amount) as net_disbusment')
            ->where('receipt_type', 'disbusment_receipt')
            ->where('status', '!=', 'rejected')
            ->groupBy('obf_id');
        
        $exchangeAmount = DB::table('exchange_purchase')
            ->selectRaw('obf_id, total_exchange_amount')
            ->where('status', 'active')
            ->groupBy('obf_id');
        
        // Main query
        $data = DB::table('obf')
            ->leftJoin('branches', 'branches.id', '=', 'obf.branch_id')
            ->leftJoin('orders', 'orders.obf_id', '=', 'obf.id') // remove if not used
            ->leftJoin('fule_type', 'obf.fuel_type', '=', 'fule_type.id')
            ->leftJoin('products', 'obf.product_id', '=', 'products.id')
            ->leftJoin('inventory', 'products.id', '=', 'inventory.name')
            ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
            ->leftJoin('users', 'users.id', '=', 'obf.sales_person_id')
            ->leftJoin('Team_master', 'Team_master.id', '=', 'users.team_name')
            ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
            ->leftJoin('exteriorcolor', 'exteriorcolor.id', '=', 'products.exterior_color')
            ->leftJoin('interiorcolor', 'interiorcolor.id', '=', 'products.interior_color')
            ->leftJoin('obf_finance', 'obf_finance.obf_id', '=', 'obf.id')
        
            // Join subqueries for sums
            ->leftJoinSub($bookingReceipt, 'br', function ($join) {
                $join->on('br.obf_id', '=', 'obf.id');
            })
            ->leftJoinSub($dpReceipt, 'dr', function ($join) {
                $join->on('dr.obf_id', '=', 'obf.id');
            })
            ->leftJoinSub($netDisbusment, 'nr', function ($join) {
                $join->on('nr.obf_id', '=', 'obf.id');
            })
            ->leftJoinSub($exchangeAmount, 'ex', function ($join) {
                $join->on('ex.obf_id', '=', 'obf.id');
            })
        
            ->select(
                DB::raw("DATE_FORMAT(obf.booking_date, '%d-%m-%Y %H:%i:%s') as booking_date"),
                'branches.name as branch_name',
                'Team_master.team_name',
                DB::raw("CONCAT(users.first_name, ' ', users.last_name) as sales_person_name"),
                'obf.customer_name',
                'car_model.name as product',
                'car_varient.name as car_varient_name',
                'fule_type.title as fuel_type_name',
                'exteriorcolor.name as exterior_color',
                'interiorcolor.name as interior_color',
                'inventory.manufacturing_year',
                'inventory.vin_number',
                'obf.on_road_price',
                DB::raw('
                    COALESCE(obf_finance.finance_discount, 0) +
                    COALESCE(obf.way_off, 0) +
                    COALESCE(obf.foc_accessories, 0) +
                    COALESCE(obf.offer_ib_amount, 0) +
                    COALESCE(obf.offer_kin_amount, 0) as less_discount
                '),
                DB::raw('
                    obf.on_road_price - (
                        COALESCE(obf_finance.finance_discount, 0) +
                        COALESCE(obf.way_off, 0) +
                        COALESCE(obf.foc_accessories, 0) +
                        COALESCE(obf.offer_ib_amount, 0) +
                        COALESCE(obf.offer_kin_amount, 0)
                    ) as after_discount_on_road_price
                '),
                'br.booking_amount',
                'dr.dp_amount',
                'nr.net_disbusment',
                'ex.total_exchange_amount',
                DB::raw('
                    COALESCE(br.booking_amount, 0) +
                    COALESCE(dr.dp_amount, 0) +
                    COALESCE(nr.net_disbusment, 0) +
                    COALESCE(ex.total_exchange_amount, 0) as total
                '),
                DB::raw('
                    (
                        obf.on_road_price - (
                            COALESCE(obf_finance.finance_discount, 0) +
                            COALESCE(obf.way_off, 0) +
                            COALESCE(obf.foc_accessories, 0) +
                            COALESCE(obf.offer_ib_amount, 0) +
                            COALESCE(obf.offer_kin_amount, 0)
                        )
                    ) - (
                        COALESCE(br.booking_amount, 0) +
                        COALESCE(dr.dp_amount, 0) +
                        COALESCE(nr.net_disbusment, 0) +
                        COALESCE(ex.total_exchange_amount, 0)
                    ) as due_amount
                ')
            )
        
            ->whereIn('obf.id', $this->ids)
            ->groupBy('obf.id')
            ->get();
    
        if($data == null){
            throw new \ErrorException('No data found');
        }else{
            return $data;
        }
    }

    public function headings() :array{
        return ['Booking Date','Branch','Team Leader','KEC Name','Customer Name','Model','Varient','Fuel Type','Exterior Color','Interior Color','MFG. Year','VIN No.','On Road Price (A)','Less Discount (B)','After Disc On Road Price (A-B = C)','Booking Amount','DP Amount','Fin. Disbusement Amt','Exchange','Total (D)','Net Receivable Amt. (C - D)'];
    }

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