<?php

namespace App\Exports;

use DateTime;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use App\Models\Account;
use App\Models\LicenseType;
use App\Models\AccountLicense;

class AccountsExport implements FromCollection,WithHeadings
{
    public function dataProcessing()
    {
        $request  = new Request(request()->all());
        $primary_account = $request->primary_accounts == 'on' ? 1 : 0;
        
        $LicenseType = LicenseType::select('id','name')->where('is_mandatory', '1')->groupBy('priority')->get();

        $accounts = Account::select([
                'accounts.id',
                'accounts.name',
                'accounts.code',
                'accounts.print_name',
                'accounts.unique_register_no',
                'accounting_groups.name as group_name',
                'account_addresses.address_line1 as address',
                'account_addresses.address_line2 as address2',
                'account_addresses.post_code',
                'countries.name as country',
                'states.name as state',
                'cities.name as city',
                'account_contacts.phone_no',
                'account_contacts.email',
                'account_contacts.whatsapp_no',
                'account_contacts.name as contact',
                'account_types.type_name',
                'marital_statuses.name as marital_status',
                'genders.name as gender',
                'blood_groups.group as blood_group',
                'account_banks.account_name',
                'account_banks.bank_name',
                'account_banks.account_no',
                'account_banks.ifsc_code',
            ])
            ->leftjoin('account_contacts','account_contacts.account_id','accounts.id')
            ->leftjoin('accounting_groups','accounting_groups.id','accounts.accounting_group_id')
            ->leftjoin('account_types','account_types.id','accounts.account_type_id')
            ->leftjoin('account_addresses','account_addresses.account_id','accounts.id')
            ->leftjoin('countries', 'countries.id', 'account_addresses.country_id')
            ->leftjoin('states', 'states.id', 'account_addresses.state_id')
            ->leftjoin('cities', 'cities.id', 'account_addresses.city_id')
            ->leftjoin('marital_statuses', 'marital_statuses.id', 'account_contacts.marital_status')
            ->leftjoin('genders', 'genders.id', 'account_contacts.account_gender_id')
            ->leftjoin('blood_groups', 'blood_groups.id', 'account_contacts.blood_group_id')
            ->leftJoin('account_banks', function($join) {
                $join->on('account_banks.account_id', '=', 'accounts.id')
                     ->where('account_banks.is_default', '=', '1');
            })
            ->when($primary_account == 0, function($query) use ($request) {
                $query->where('accounts.primary_account','0');
            })
            ->when(!empty($request->account_type), function($query) use ($request) {
                $query->where('accounts.account_type_id', $request->account_type);
            })
            ->when(!empty($request->account_group), function($query) use ($request) {
                $query->where('accounts.accounting_group_id', $request->account_group);
            })
            ->get();

        return ['license_types' => $LicenseType, 'accounts' => $accounts];
    }

    public function collection()
    {
        $data = $this->dataProcessing();
        $rows = [];

        foreach ($data['accounts'] as $key => $record) {
            $rows[$key]['account_name']      = $record->name;
            $rows[$key]['print_name']        = $record->print_name;
            $rows[$key]['customer_id']       = $record->unique_register_no;
            $rows[$key]['phone_no']          = $record->phone_no;
            $rows[$key]['whatsapp']          = $record->whatsapp_no;
            $rows[$key]['email']             = $record->email;
            $rows[$key]['contact_person']    = $record->contact;
            $rows[$key]['account_group']     = $record->group_name;
            $rows[$key]['account_type']      = $record->type_name;
            $rows[$key]['marital_status']    = $record->marital_status;
            $rows[$key]['blood_group']       = $record->blood_group;
            $rows[$key]['gender']            = $record->gender;
            $rows[$key]['address']           = $record->address;
            $rows[$key]['address_line2']     = $record->address2;
            $rows[$key]['city']              = $record->city;
            $rows[$key]['state']             = $record->state;
            $rows[$key]['country']           = $record->country;
            $rows[$key]['pin_no']            = $record->post_code;
            $rows[$key]['bank_account_name'] = $record->account_name;
            $rows[$key]['account_no']        = $record->account_no;
            $rows[$key]['ifsc_code']         = $record->ifsc_code;
            $rows[$key]['bank_name']         = $record->bank_name;

	        foreach ($data['license_types'] as $license_key => $value) {
				$slug              = $this->slug($value->name);
				$rows[$key][$slug] = $this->getLicense($record->id, $value->id);
	        }
        }

        return collect($rows);
    }

    public function headings(): array
    {
        $data = $this->dataProcessing();

        $headings = [
            'Account Name',
            'Print Name',
            'Customer ID',
            'Phone No',
            'Whatsapp no',
            'Email',
            'Contact Person',
            'Account Group',
            'Account Type',
            'Marital Status',
            'Blood Group',
            'Gender',
            'Address Line 1',
            'Address Line 2',
            'City',
            'State',
            'Country',
            'Pin No',
            'Bank account name',
            'Account no',
            'IFSC code',
            'Bank name',
        ];

        foreach ($data['license_types'] as $key => $value) {
        	$headings[] = $value->name;
        }

        return $headings;
    }

    public function slug($string) {
    	$string = strtolower($string);
    	$string = str_replace(' ', '_', $string);
    	return $string;
    }

    public function getLicense($id, $license_type_id) {
        $data = AccountLicense::select([
                'license_number',
            ])
            ->where('account_id', $id)
            ->where('license_type_id', $license_type_id)
            ->whereNull('deleted_at')
            ->first();

        return $data->license_number ?? '';
    }
}
