<?php

namespace App\Exports;

use App\Models\OBF;
use App\Models\User;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Carbon\Carbon;

class ExportCustomerOutstandingReport implements FromCollection, WithHeadings, WithMapping, ShouldAutoSize
{
    protected $slug;

    public function __construct($slug = 'all')
    {
        $this->slug = $slug;
    }

    public function collection()
    {
        $permission_get = '';
        $data_v = auth()->user()->roles->pluck('data_permission');
        if (!empty($data_v)) {
            $permission_get = $data_v[0];
        }

        $cashSummary = DB::table('cash_receipt')
            ->select(
                'obf_id',
                DB::raw('SUM(CASE WHEN receipt_type="booking_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS booking_amount'),
                DB::raw('SUM(CASE WHEN receipt_type="downpayment_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS dp_amount'),
                DB::raw('SUM(CASE WHEN receipt_type="disbusment_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS net_disbusment'),
                DB::raw('SUM(CASE WHEN receipt_type="access_sort_receipt" AND status!="rejected" THEN amount ELSE 0 END) AS access_sort_amount'),
                DB::raw('SUM(CASE WHEN status!="rejected" THEN amount ELSE 0 END) AS received_amount')
            )
            ->groupBy('obf_id');

        $exchangeSummary = DB::table('exchange_purchase')
            ->select('obf_id', DB::raw('total_exchange_amount'))
            ->whereIn('status', ['active', 'sell']);

        $data = OBF::select(
                'branches.name as branch_name',
                DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
                'Team_master.team_name',
                'obf.customer_name',
                'car_model.name as model',
                'car_varient.name as variant',
                DB::raw('(SELECT vin_number FROM inventory WHERE inventory.id = obf.inventory_id LIMIT 1) as vin_number'),
                DB::raw("COALESCE(MAX(orders.invoice_no), '---') as invoice_no"),
                DB::raw("COALESCE(MAX(orders.invoice_date), NULL) as invoice_date"),
                'obf.on_road_price',
                'obf_finance.finance_discount',
                'obf.way_off',
                'obf.foc_accessories',
                'obf.offer_ib_amount',
                'obf.offer_kin_amount',
                DB::raw('cash_summary.booking_amount'),
                DB::raw('cash_summary.dp_amount'),
                DB::raw('cash_summary.net_disbusment'),
                DB::raw('exchange.total_exchange_amount'),
                DB::raw('cash_summary.access_sort_amount'),
                'orders.status'
            )
            ->leftJoin('products', 'obf.product_id', '=', 'products.id')
            ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
            ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
            ->leftJoin('branches', 'branches.id', '=', 'obf.branch_id')
            ->leftJoin('users', 'users.id', '=', 'obf.sales_person_id')
            ->leftJoin('Team_master', 'Team_master.id', '=', 'users.team_name')
            ->leftJoin('orders', 'orders.obf_id', '=', 'obf.id')
            ->leftJoin('obf_finance', 'obf_finance.obf_id', '=', 'obf.id')
            ->leftJoinSub($cashSummary, 'cash_summary', 'cash_summary.obf_id', '=', 'obf.id')
            ->leftJoinSub($exchangeSummary, 'exchange', 'exchange.obf_id', '=', 'obf.id')
            ->where('obf.status', '!=', 'delete')
            ->whereNotNull('orders.invoice_no')
            ->groupBy('obf.id', 'cash_summary.booking_amount', 'cash_summary.dp_amount', 'cash_summary.net_disbusment', 'exchange.total_exchange_amount', 'cash_summary.access_sort_amount');

        // Permissions
        if ($permission_get === 'ONLY_MY_DATA') {
            $data = $data->whereRaw('FIND_IN_SET(obf.created_by, "' . auth()->id() . '")');
        }

        if (!empty(auth()->user()->branch)) {
            $data = $data->whereRaw('FIND_IN_SET(obf.branch_id, "' . auth()->user()->branch . '")');
        }

        if ($permission_get === 'MY_TEAM_DATA' && auth()->user()->team_name !== null) {
            $teamIds = User::where('team_name', auth()->user()->team_name)->pluck('id');
            $data = $data->whereIn('obf.created_by', $teamIds);
        }

        $data->orderByDesc('obf.id');

        return $data->get();
    }

    public function headings(): array
    {
        return [
            'Location',
            'KEC',
            'Team Leader',
            'Customer Name (Bill To)',
            'Model',
            'Variant',
            'VIN Number',
            'Invoice No.',
            'Invoice Date',
            'On Road Price (A)',
            'Discount (G(-))',
            'On Road after dis (A-B=C)',
            'Booking Amount (B(+))',
            'DP Amount Received (C(+))',
            'Net Disbursement Amount (D(+))',
            'Exchange Amount (E(+))',
            'Total Received Amount (B+C+D+E=F)',
            'Access / Sort Amount',
            'Due Amount (A-H=I)',
            'OBF Status'
        ];
    }

    public function map($data): array
    {
        $discount = ($data->finance_discount ?? 0)
            + ($data->way_off ?? 0)
            + ($data->foc_accessories ?? 0)
            + ($data->offer_ib_amount ?? 0)
            + ($data->offer_kin_amount ?? 0);

        $onRoadAfterDis = $data->on_road_price - $discount;

        $booking = $data->booking_amount ?? 0;
        $dp = $data->dp_amount ?? 0;
        $disbursement = $data->net_disbusment ?? 0;
        $exchange = $data->total_exchange_amount ?? 0;
        $access = $data->access_sort_amount ?? 0;

        $totalReceived = $booking + $dp + $disbursement + $exchange;
        $due = $data->on_road_price - ($discount + $totalReceived);
        $due = ($totalReceived < $data->on_road_price)
            ? $due - $access
            : $due + $access;

        return [
            $data->branch_name ?? '--',
            $data->sales_person_name ?? '---',
            $data->team_name ?? '--',
            $data->customer_name ?? '--',
            $data->model ?? '--',
            $data->variant ?? '--',
            $data->vin_number ?? '--',
            $data->invoice_no ?? '--',
            $data->invoice_date ? Carbon::parse($data->invoice_date)->format('d-m-Y') : '--',
            round($data->on_road_price, 2),
            round($discount, 2),
            round($onRoadAfterDis, 2),
            round($booking, 2),
            round($dp, 2),
            round($disbursement, 2),
            round($exchange, 2),
            round($totalReceived, 2),
            round($access, 2),
            round($due, 2),
            ucfirst(str_replace('_', ' ', $data->status))
        ];
    }
}
