<?php

namespace App\Http\Controllers;

use App\DataTables\VisitorBatchDataTable;
use App\Models\AssignDivisionAdmin;
use App\Models\AssignDivisionTeamMember;
use App\Models\EmailBatch;
use App\Models\EmailBatchLog;
use App\Models\EmailDraft;
use Illuminate\Http\Request;
use App\DataTables\VisitorsDataTable;
use App\Models\Visitor;
use App\Models\EventImage;
use App\Models\Sendpush;
use App\Models\Event;
use DB;
use Illuminate\Support\Facades\Session;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\VisitorlistExport;
use App\Imports\Importvisitorlist;
use Alert;
use App\Models\Action;
use App\Models\Product;
use App\Models\User;
use App\Models\Visit_data_images;
use App\Models\VisitData;
use App\Models\VisitorProduct;
use App\Models\Template;
use App\Models\WhatsappLog;

use ZipArchive;
use Carbon\Carbon;
use PDF;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\View;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Log;
use App\DataTables\EventsVisitorsDataTable;
use App\Exports\VisitorlistByEventExport;
use Illuminate\Support\Facades\Http;
use App\Models\Category;
use App\DataTables\VisitorBatchLogDataTable;

use App\DataTables\ClientVisitorsDataTable;
use App\Exports\CliectVisitorlistExport;
use App\Exports\GoogleContactExport;
use App\Models\SourceOfLead;
use Exception;

class NewVisitorController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
        $this->_visitor = new Visitor();
    }

    public function newvisitor(Request $request)
    {
        
        $region = 'all';
        $category_id = "all";
        $sub_category_id = "all";
        $is_international = "all";
        $start_date = Carbon::now()->subYear()->format('Y-m-d');
        //$start_date = Carbon::now()->addDay('-15')->format('Y-m-d');
        $end_date = Carbon::now()->addDay()->format('Y-m-d');
        $country_id = "all";
        $follow_up_time = '';
        $state_id = "all";
        $city_id = "all";
        $action = "all";
        $sourceoflead = "all";
        $event_id = "all";
        $CustomerID = "all";

        $actionList = Action::orderBy('id', 'asc')->get();
        $categories = DB::table('category')->select('name', 'id')->orderBy('name', 'asc')->get();
        $regions = DB::table('region')->select('name', 'id')->orderBy('name', 'asc')->get();
        $sourceofleadList = SourceOfLead::orderBy('id', 'asc')->get();
        $events = DB::table('events')->select('event_name', 'id')->orderBy('event_name', 'asc')->get();

        $users = DB::table('users')->where('is_delete', '=', 'false');
        $authAdmin = auth()->user();
        if ($authAdmin->type == 'sub-admin') {
            $divisionIds = AssignDivisionAdmin::where('admin_id', $authAdmin->id)->pluck('division_id')->toArray();
            $divisionTeamMemberIds = AssignDivisionTeamMember::whereIn('division_id', $divisionIds)->pluck('customer_id')->toArray();
            $users = $users->whereIn('CustomerID', $divisionTeamMemberIds);
        }
        $users = $users->select('CustomerName', 'CustomerID')->orderBy('CustomerName', 'asc')->get();
        $otherEmailDrafts = EmailDraft::get();
        $username = DB::table('users')->where('users.is_delete', '=', 'false')->get();
        $user = json_decode($username);

        if (isset($request->event_id)) {
            $event_id = $request->event_id;
        }
        if (isset($request->CustomerID)) {
            $CustomerID = $request->CustomerID;
        }
        if (isset($request->category_id)) {
            $category_id = $request->category_id;
        }
        if (isset($request->sub_category_id)) {
            $sub_category_id = $request->sub_category_id;
        }

        if (isset($request->country_id)) {
            $country_id = $request->country_id;
        }
        if (isset($request->state_id)) {
            $state_id = $request->state_id;
        }
        if (isset($request->city_id)) {
            $city_id = $request->city_id;
        }
        if (isset($request->is_international)) {
            $is_international = $request->is_international;
        }
        if (isset($request->region)) {
            $region = $request->region;
        }
        if (isset($request->start_date)) {
            $start_date = $request->start_date;
        }
        if (isset($request->end_date)) {
            $end_date = $request->end_date;
        }
        if (isset($request->action)) {
            $action = $request->action;
        }

        if (isset($request->follow_up_time)) {
            $follow_up_time = $request->follow_up_time;
        }

        if (isset($request->sourceoflead)) {
            $sourceoflead = $request->sourceoflead;
        }

        foreach ($actionList as $obj) {

            $query = Visitor::select('events.event_name', 'users.CustomerName', 'visitors.*','permissions.send_email as email_permission')
            ->join('events', 'events.id', 'visitors.event_id')
            ->leftJoin('users', 'users.CustomerID', 'visitors.customer_id')
            ->leftJoin('permissions', 'permissions.fk_customer_id', 'visitors.customer_id')
            ->where('visitors.action', $obj->title);

            if ($event_id != "all") {
                $query = $query->where(function ($query) use ($event_id) {
                    $query->where('event_id', $event_id)
                        ->orWhereRaw('FIND_IN_SET(?, event_ids)', [$event_id]);
                });
            }

            if ($CustomerID != "all") {
                $query = $query->where('CustomerID', $CustomerID);
            } else {
                $authAdmin = auth()->user();
                if ($authAdmin->type == 'sub-admin') {
                    $divisionIds = AssignDivisionAdmin::where('admin_id', $authAdmin->id)->pluck('division_id')->toArray();
                    $divisionTeamMemberIds = AssignDivisionTeamMember::whereIn('division_id', $divisionIds)->pluck('customer_id')->toArray();
                    $query = $query->whereIn('CustomerID', $divisionTeamMemberIds);
                }
            }

            if ($category_id != "all" && $category_id != null) {
                $categoryName = DB::table('category')->where('id', $category_id)->select('name', 'id')->first()->name;
                $query = $query->where(function ($query) use ($categoryName) {
                    $query->whereRaw('FIND_IN_SET(?, visitors.category)', [$categoryName]);
                });
            }

            if ($sub_category_id != "all" && $sub_category_id != null) {
                $subCategoryName = DB::table('sub_categories')->where('id', $sub_category_id)->select('name', 'id')->first()->name;
                $query = $query->where(function ($query) use ($subCategoryName) {
                    $query->whereRaw('FIND_IN_SET(?, visitors.sub_category)', [$subCategoryName]);
                });
            }

            if ($state_id != "all" && $state_id != null) {
                $query = $query->where('visitors.state', '=', $state_id);
            }

            if ($city_id != "all" && $city_id != null) {
                $query = $query->where('visitors.city', '=', $city_id);
            }

            if ($is_international != "all" && $is_international != null) {
                $query->where(function ($query) use ($is_international) {
                    $query->where('visitors.is_international', $is_international)
                        ->orWhere('visitors.country_code', '!=','IN');
                });

            }

            /*if ($action != "all" && $action != null) {
                $query = $query->where('visitors.action', $action);
            }*/

            if ($country_id != "all" && $country_id != null) {
                $countryISOCode = DB::table('countries')->where('name', $country_id)->select('iso_code', 'name', 'id')->first()->iso_code;
                $query = $query->where('visitors.country_code', '=', $countryISOCode);
            }

            if ($region != "all" && $region != null) {
                $regionCountries = DB::table('region_country')->where('fk_region_id', $region)->get();
                if ($regionCountries) {
                    $regionCountryIds = $regionCountries->pluck('fk_country_id')->toArray();
                    $countries = DB::table('countries')
                        ->whereIn('id', $regionCountryIds)->select('iso_code', 'name', 'id')->get();
                    if ($countries) {
                        $countryISOCodes = $countries->pluck('iso_code')->toArray();
                        $query = $query->whereIn('visitors.country_code', $countryISOCodes);
                    }
                }
            }

            if ($start_date != null && $end_date != null) {
                $startDateFormatted = date('Y-m-d', strtotime($start_date));
                $endDateFormatted = date('Y-m-d', strtotime($end_date));
                $query = $query->where(function ($query) use ($startDateFormatted, $endDateFormatted) {
                    $query->where('visitors.visited_date', '>=', $startDateFormatted)
                        ->where('visitors.visited_date', '<=', $endDateFormatted);
                });
            }

            
            if ($sourceoflead != "all" && $sourceoflead != null) {
                $query = $query->where('visitors.source_of_lead', $sourceoflead);
            }

            $query = $query->orderBy('events.event_name', 'asc')->get();
            $obj->totalcount = count($query);
            $obj->visitorlist = $query;
            
        }

        return view('newvisitor.list', compact('actionList','categories','regions','sourceofleadList','events','users','otherEmailDrafts','username','region','category_id','sub_category_id','is_international','start_date','end_date','country_id','follow_up_time','state_id','city_id','action','sourceoflead','event_id','CustomerID'));
    }
   
}
