<?php

namespace App\Services;

use App\Models\User;
use DB;

class ContactCareGiverProfessionService {

    public function GetContact($id = null) {
        $contacts = DB::table('contact_caregivers_professionist as ccp')
                ->select(DB::raw("ucg.email as caregiver_email,"
                                . "up.email as professionist_email,"
                                . "up.path_url as path_url,"

                                . "ucgd.tel_referent as caregiver_tel,"
                                . "upd.tel_referent as professionist_tel,"
                                . "up.slug as professionist_slug,"

                                . 'IF(upd.business_name IS NOT NULL,1,0) as business,'
                                . 'IF(upd.business_name IS NOT NULL, upd.business_name , CONCAT(up.name," ",up.surname)) as name,'
                                . "ccp.created_at,ccp.caregiver_id as idc,ccp.professionist_id as idp,DATE_FORMAT(ccp.created_at, '%d/%m/%Y %H:%i:%s') as created_custom"))
                ->leftjoin('users as ucg', function($join) {
                    $join->on('ucg.id', 'ccp.caregiver_id');
                })
                ->leftjoin('users as up', function($join) {
                    $join->on('up.id', 'ccp.professionist_id');
                })
                ->leftjoin('user_details as ucgd', function($join) {
                    $join->on('ucgd.user_id', 'ucg.id');
                })
                ->leftjoin('user_details as upd', function($join) {
                    $join->on('upd.user_id', 'up.id');
                })
                ->leftjoin('medical_centers as mc', function($join) {
            $join->on('mc.id', 'upd.medical_center_id');
        })->groupBy('ccp.id')->orderby('ccp.created_at','DESC');

        if ($id) {
            $contacts = $contacts->where('ccp.caregiver_id', $id);
        }
        return $contacts;
    }

}