<?php

namespace App\Services\Admin;

use App\Models\Lead;
use App\Models\LeadCall;
use App\Models\LeadNote;
use App\Models\LeadAttachment;
use App\Models\LeadTasksMeeting;
use Illuminate\Support\Facades\DB;

class LeadService{

    public function getAllLeads(){
        return Lead::with('kyc','user', 'product.category')->latest()->get();
    }

    public function totalLead(){
        return Lead::query()
        ->select('lead_status', \DB::raw('COUNT(lead_status) as count'))
        ->groupBy('lead_status')
        ->get();
    }

    public function newTotalLead(){
        return Lead::where('lead_status','New')->count();
    }

    public function ongoingTotalLead(){
        return Lead::where('lead_status','Ongoing')->count();
    }

    public function completedTotalLead(){
        return Lead::where('lead_status','Completed')->count();
    }

    public function oldTotalLead(){
        return Lead::where('lead_status','Old')->count();
    }

    public function lostTotalLead(){
        return Lead::where('lead_status','Lost')->count();
    }

    public function newLeads(){
        return Lead::where('lead_status','New')->get();
    }

    public function ongoingLeads(){
        return Lead::with('product')->where('lead_status','Ongoing')->get();
        // return Lead::with('product')->get();
    }

    public function completedLeads(){
        return Lead::where('lead_status','Completed')->get();
    }

    public function oldLeads(){
        return Lead::where('lead_status','Old')->get();
    }

    public function lostLeads(){
        return Lead::where('lead_status','Lost')->get();
    }

    public function getAllProducts(){
        return Lead::query()
            ->select(DB::raw('coalesce(sdi.product_name, p2p.scheme, fre.property_name_and_location,id.company_name,aif.fund_name,caga.project_name,hyf.security_name,lbf.company,sdi.product_name,vd.company_name,bd.issuer,fd.fund_name,exchange.name,re.property_name) as product_name'))
            ->leftJoin('securitized_debt_instruments as sdi', 'leads.products_id', 'sdi.products_id')
            ->leftJoin('fractional_real_estates as fre', 'leads.products_id', 'fre.products_id')
            ->leftJoin('peer_to_peer_lendings as p2p', 'leads.products_id', 'p2p.products_id')
            ->leftJoin('invoice_discountings as id', 'leads.products_id', 'id.products_id')
            ->leftJoin('alternative_investment_funds as aif', 'leads.products_id', 'aif.products_id')
            ->leftJoin('clean_and_green_assets as caga', 'leads.products_id', 'caga.products_id')
            ->leftJoin('high_yield_finances as hyf', 'leads.products_id', 'hyf.products_id')
            ->leftJoin('lease_based_financings as lbf', 'leads.products_id', 'lbf.products_id')
            ->leftJoin('venture_debts as vd', 'leads.products_id', 'vd.products_id')
            ->leftJoin('bonds as bd', 'leads.products_id', 'bd.products_id')
            ->leftJoin('funds as fd', 'leads.products_id', 'fd.products_id')
            ->leftJoin('stock_funds_real_estate_exchanges as exchange', 'leads.products_id', 'exchange.products_id')
            ->leftJoin('real_estates as re', 'leads.products_id', 're.products_id')
            ->orderByDesc('leads.created_at')
            ->get();
    }

    public function store($request){
        return  Lead::create($request->validated());
    }

    public function show($id){
        return Lead::with('user', 'owner', 'notes.admin', 'attachment.admin', 'tasks_meetings.admin', 'calls.admin', 'product.category','leadSource')->FindOrFail($id);
    }

    public function edit($id){
        return Lead::with('user','notes.admin', 'attachment.admin', 'tasks_meetings.admin', 'calls.admin')->FindOrFail($id);
    }

    public function update($request){
        return Lead::where('id', $request->lead_id)->update($request->validated());
    }

    public function storeNote($request){
        return LeadNote::updateOrCreate(['leads_id'=>$request->leads_id],['notes'=>$request->notes,'created_by'=>\Auth::user()->id]);
    }

    public function storeAttachments($request){
        $path;
        if($request->filename)
        {
            $fileName = $request->lead_id.'-'.time().'.'.$request->filename->getClientOriginalExtension();
            $path = $request->filename->storeAs('files/manage_leads/attachment_file', $fileName);
        }
        if($request->url)
        {
            $path = $request->url;
        }
        return LeadAttachment::create([
                'leads_id'=>$request->lead_id,
                'type'=>$request->type,
                'path'=>$path,
                'created_by'=>\Auth::user()->id,
                ]);
        // return LeadAttachment::create($request->validated());   
    }

    public function storeTasks($request){
        return LeadTasksMeeting::create($request->validated());
    }

    public function storeMeetings($request){
        return LeadTasksMeeting::create($request->validated());
    }

    public function convertActivity($request){
        // $validator = Validator::make($request->post(), [
        //     'task_id' => 'required',
        //     'table' => 'required'
        // ], [
        //     'required' => 'The :attribute field must be required'
        // ]);
        // dd($request);
        if ($request->table != 'LeadCall') {
            $convertClosed = LeadTasksMeeting::where('id', $request->task_id)->update([
                'status' => '1',
            ]);
        } else {
            $convertClosed = LeadCall::where('id', $request->task_id)->update([
                'status' => '1',
            ]);
        }
        return $convertClosed;
    }

    public function sortNotes($request){
        $orderBy = $request->value == 'recent' ? 'DESC' : 'ASC';
        $notes = LeadNote::where('leads_id', $request->lead_id)->orderBy('created_at', $orderBy)->get();
        $data = '';
        foreach ($notes as $note) {
            $data .= "<li class='list-group-item'>{$note->notes} <br>By {$note->admin->name} At {$note->created_at->format('H:i:s d/m/y')}
            </li>";
        }
        return $data;
    }

    public function download($file){
        // dd($file);
        // return $file;
        return \Storage::download($file);
        // return response()->download(storage_path($file));
    }
}