<?php

namespace App\Http\Controllers\Accounts;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\TempAccountImport;
use App\Models\City;
use App\Models\State;
use App\Models\Country;
use App\Models\LicenseType;
use App\Models\AccountingGroup;
use Maatwebsite\Excel\Facades\Excel;
use Yajra\DataTables\Facades\DataTables;
use App\Exports\AccountsSample;
use App\Imports\AccountExcelImport;
use App\Models\Account;
use App\Models\AccountContact;
use App\Models\AccountAddress;
use App\Models\AccountLicense;
use App\Models\CompanyAddress;
use App\Models\AccountingType;
use App\Models\MaritalStatus;
use App\Models\BloodGroup;
use App\Models\Gender;
use App\Models\AccountBank;
use App\Models\AccountTitle;
use App\Models\MasterType;

class TempAccountImportController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $data = TempAccountImport::query();

        if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');

            $data = $data->when(!empty($search_query), function ($query) use ($search_query) {
                        return $query->where('account_name', 'like', '%'.$search_query.'%');
                    })
                    ->paginate($sort_by);
            return view('temp-accounts-import.table', compact('data'));
        } else {
             $data = $data->paginate(10);

            return view('temp-accounts-import.index', compact('data'));
        }
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        return view('temp-accounts-import.create');
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        $records  = [];
        $data     = Excel::toArray(new AccountExcelImport, $request->file('file'));
        $data     = isset($data[0]) ? $data[0] : [];
        
        $heading = [];

        if (isset($data[0])) {
            $heading = $data[0];
            unset($data[0]);
        }

        if (!empty($data)) {
            // Truncate table.
            TempAccountImport::truncate();
            $licenses = LicenseType::where('is_mandatory', '1')->groupBy('priority')->get();
            $phoneNo = '';
            foreach ($data as $key => $record) {
                if($record[array_search('Account Name', $heading)] == '') {
                    continue;
                }

                $mfg_date = '';
                $exp_date = '';
                $country_code = Country::select('gsm_code','name')->where('name','like',$record[array_search('Country', $heading)])->first();

                if($record[array_search('Phone No', $heading)])
                {
                    $phoneNo = $country_code->gsm_code .$record[array_search('Phone No', $heading)];
                }

                $records[$key]['account_name']      = $record[array_search('Account Name', $heading)];
                $records[$key]['customer_id']       = $record[array_search('Customer ID', $heading)];
                $records[$key]['phone']             = $phoneNo;
                $records[$key]['account_group']     = $record[array_search('Account Group', $heading)];
                $records[$key]['address']           = $record[array_search('Address Line 1', $heading)];
                $records[$key]['address_2']         = $record[array_search('Address Line 2', $heading)];
                $records[$key]['city']              = $record[array_search('City', $heading)];
                $records[$key]['state']             = $record[array_search('State', $heading)];
                $records[$key]['country']           = $record[array_search('Country', $heading)];
                $records[$key]['pin']               = $record[array_search('Pin No', $heading)];
                $records[$key]['account_type']      = $record[array_search('Account Type', $heading)];
                $records[$key]['whatsapp']          = $record[array_search('Whatsapp no', $heading)];
                $records[$key]['email']             = $record[array_search('Email', $heading)];
                $records[$key]['contact_person']    = $record[array_search('Contact Person', $heading)];
                $records[$key]['print_name']        = $record[array_search('Print Name', $heading)];
                $records[$key]['marital_status']    = $record[array_search('Marital Status', $heading)];
                $records[$key]['gender']            = $record[array_search('Gender', $heading)];
                $records[$key]['blood_group']       = $record[array_search('Blood Group', $heading)];
                $records[$key]['bank_account_name'] = $record[array_search('Bank account name', $heading)];
                $records[$key]['account_no']        = $record[array_search('Account no', $heading)];
                $records[$key]['ifsc_code']         = $record[array_search('IFSC code', $heading)];
                $records[$key]['bank_name']         = $record[array_search('Bank name', $heading)];
                $i = 20;

                foreach ($licenses as $license_key => $value) {
                    $records[$key]['license_'. ($license_key + 1)] = $record[$i];

                    $i++;
                }
            }
            TempAccountImport::insert($records);

            return response()->json([
                'success' => true,
                'message' => 'File has been imported successfully.',
                'data'    => []
            ]);
        } else {
            return response()->json([
                'success' => false,
                'message' => 'Accounts data not found!',
                'data'    => []
            ]);
        }
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        $genders            = Gender::select(['id','name'])->get();
        $maritalStatus      = MaritalStatus::select(['id','name'])->get();
        $bloodGroup         = BloodGroup::select(['id','group'])->get();
        $account_types      = MasterType::select(['id','type_name','type_code'])->get();
        $accounting_groups  = AccountingGroup::select(['id','name','code'])->whereNull('deleted_at')->get();
        $country            = Country::select(['id','name'])->where('status', '1')->get();

        $data = TempAccountImport::select(
                'temp_account_imports.*',
                'cities.id as city_id',
                'states.id as state_id',
                'countries.id as country_id',
            )
            ->leftjoin('cities','cities.name', 'temp_account_imports.city')
            ->leftjoin('states','states.name', 'temp_account_imports.state')
            ->leftjoin('countries','countries.name', 'temp_account_imports.country')
            ->where('temp_account_imports.id', $id)
            ->first();

        $licenses        = LicenseType::where('is_mandatory', '1')->get();
        $company_address = \Session::get('company_data')['companies_addresses'];

        $countryCode = $company_address->country_code ?? 'us';

        return view('temp-accounts-import.edit', compact(
            'data',
            'licenses',
            'countryCode',
            'genders',
            'maritalStatus',
            'bloodGroup',
            'account_types',
            'accounting_groups',
            'country',
        ));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        $validator = \Validator::make($request->all(), [
            'account_name'  => 'required',
            // 'phone'         => 'required',
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }


        $data = $request->except([
            '_token',
            '_method',
            'state',
            'city',
            'country',
            'account_type_id',
            'accounting_group_id'
        ]);
        $state                 =  State::select(['name'])->where('id',$request->state)->first();
        $city                  =  City::select(['name'])->where('id',$request->city)->first();
        $country               =  Country::select(['name'])->where('id',$request->country_id)->first();
        $data['state']         = $state->name ?? '';
        $data['city']          = $city->name ?? '';
        $data['country']       = $country->name ?? '';
        $data['account_type']  = $request->account_type_id ?? '';
        $data['account_group'] = $request->accounting_group_id ?? '';

        $products = TempAccountImport::find($id);

        if (!$products) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found!',
                    'data'    => []
                ]);
            }
        }

        $products->update($data);

        return response()->json([
            'success' => true,
            'message' => 'Record updated successfully.',
            'data'    => []
        ]);
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy(Request $request)
    {
        $products = TempAccountImport::find($request->id);

        if (!$products) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'Record not found!',
                    'data'    => []
                ]);
            }
        }

        $products->delete();

        return response()->json([
            'success' => true,
            'message' => 'Record deleted successfully.',
            'data'    => []
        ]);
    }

    public function exportSample()
    {
        $filename = "account-sample_" . date("Y_m_d_i_H") . ".xlsx";

        return Excel::download(new AccountsSample, $filename);
    }

    public function importAccounts()
    {
        $data      = TempAccountImport::all();
        $duplicate = $unique = $invalid = 0;
        if (count($data) > 0) {
            $licenses = LicenseType::where('is_mandatory', '1')->get();

            foreach ($data as $key => $record) {

                $account_group_id = $this->accountGroupExists($record->account_group);
                $account_type_id  = $this->accountTypeExists($record->account_type);
               
                $exists = Account::leftJoin('account_contacts', 'account_contacts.account_id', '=', 'accounts.id')
                ->where(
                    function($query) use ($record) {
                        $query->where('accounts.name', $record->account_name);
                        if(isset($record->phone)){
                            $query->where('account_contacts.phone_no', $record->phone);
                        }
                        if(isset($account_group_id)){
                            $query->where('accounts.account_group_id', $account_group_id);
                        }
                        if(isset($account_type_id)){
                            $query->where('accounts.account_type_id', $account_type_id);
                        }
                        
                    }
                );
                
                if(isset($record->customer_id)){
                    $exists = $exists->orWhere('accounts.unique_register_no', $record->customer_id);
                }
                
                $exists =$exists->first();

                if (empty($exists)) {
                    $city_id          = $this->cityExists($record->city);
                    
                    $state_id         = $this->stateExists($record->state);
                    
                    $country_id       = $this->countryExists($record->country);
                    //dd($country_id);
                   
                    $marital_status   = $this->accountMaritalStatusExists($record->marital_status);
                    $blood_group_id   = $this->bloodGroupExists($record->blood_group);
                    $gender           = $this->genderExists($record->gender);

                    if ($account_group_id == '' || $account_type_id == '') {
                        $invalid += 1;
                        continue;
                    }
                    if($record->account_type=="Doctor"){
                        if(isset($record->gender))
                        {
                            $accountId = AccountTitle::select('id')->where('code','Dr.')->where('gender_id',$gender)->first();
                            if($record->gender=="Male"){
                                $account_title_id= $accountId->id??'';
                            }
                            else
                            {
                                $account_title_id= $accountId->id??'';
                            }
                        }else{
                            $account_title_id="";
                        }
                       
                    }
                    else{
                        $account_title_id = '';
                    }
                    $account = [
                        'name'                => $record->account_name,
                        'unique_register_no'  => $record->customer_id,
                        'print_name'          => $record->print_name,
                        'accounting_group_id' => $account_group_id,
                        'account_type_id'     => $account_type_id,
                        'created_by'          => \Auth::id(),
                        'account_title_id'    => $account_title_id??''
                    ];

                    $account = Account::create($account);

                    $accounting_groups = MasterType::find($account_type_id);
                    $count  = 1;
                    $prefix = "AC";

                    if($accounting_groups) {
                        $count  = $accounting_groups->count != '' ? $accounting_groups->count + 1 : 1;
                        $prefix = $accounting_groups->prefix != '' ? $accounting_groups->prefix : "AC";

                        $accounting_groups->update(['count' => $count]);
                    }

                    $code = $this->generateCode($count, $prefix);

                    $account->update(['code' => $code]);

                    $address = AccountAddress::insert([
                        'account_id'    => $account->id,
                        'country_id'    => $country_id,
                        'state_id'      => $state_id,
                        'city_id'       => $city_id,
                        'address_line1' => $record->address,
                        'address_line2' => $record->address_2,
                        'is_default'    => '1'
                    ]);

                    $contact = AccountContact::insert([
                        'account_id'        => $account->id,
                        'phone_no'          => $record->phone,
                        'whatsapp_no'       => $record->whatsapp,
                        'email'             => $record->email ?? '',
                        'name'              => $record->contact_person ?? '',
                        'country_id'        => $country_id,
                        'state_id'          => $state_id,
                        'city_id'           => $city_id,
                        'marital_status'    => $marital_status,
                        'blood_group_id'    => $blood_group_id,
                        'account_gender_id' => $gender,
                    ]);

                    foreach ($licenses as $type => $value) {
                        $license = 'license_' . ($type + 1);

                        AccountLicense::create([
                            'account_id'      => $account->id,
                            'license_number'  => $license,
                            'license_type_id' => $value->id,
                            'valid_from'      => '',
                            'valid_upto'      => '',
                        ]);
                    }

                    if ($record->bank_account_name != '' &&
                        $record->account_no != ''
                    ) {
                        $bank = [
                            'account_name' => $record->bank_account_name,
                            'account_no'   => $record->account_no,
                            'ifsc_code'    => $record->ifsc_code,
                            'bank_name'    => $record->bank_name,
                            'account_id'   => $account->id,
                            'is_default'   => '1',
                            'status'       => '1',
                        ];

                        AccountBank::create($bank);
                    }

                    $unique += 1;

                    TempAccountImport::where('id', $record->id)->delete();
                } else {
                    $duplicate += 1;
                }
            }

            return response()->json([
                'success' => true,
                'message' => $unique . ' Accounts imported. ' . $duplicate . ' duplicates and ' . $invalid . ' invalid records skiped.',
                'data'    => []
            ]);
        } else {
            return response()->json([
                'success' => false,
                'message' => 'Accounts data not found!',
                'data'    => []
            ]);
        }
    }

    public function cityExists($name)
    {
        $data = City::where('name', $name)->first();

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

    public function stateExists($name)
    {
        $data = State::where('name', $name)->first();

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

    public function countryExists($name)
    {
        $data = Country::where('name', $name)->first();

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

    public function accountGroupExists($name)
    {
        $data = AccountingGroup::where('code', $name)->orWhere('name', $name)->first();

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

    public function accountTypeExists($name)
    {
        $data = MasterType::where('type_code', $name)->orWhere('type_name', $name)->first();

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

    public function accountMaritalStatusExists($name)
    {
        $data = MaritalStatus::where('name', $name)->first();

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

    public function bloodGroupExists($name)
    {
        $data = BloodGroup::where('group', $name)->first();

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

    public function genderExists($name)
    {
        $data = Gender::where('name', $name)->first();

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

    public function accountImportCreate()
    {
        return view('temp-accounts-import.import-create');
    }

    public function clearData()
    {
        TempAccountImport::truncate();

        return response()->json([
            'success' => true,
            'message' => 'Temp data cleared successfully.'
        ]);
    }
}
