<?php

namespace App\Http\Controllers;

use App\Helpers\CurrencyHelper;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Services\ValidationService;
use App\Services\ApiService;
use App\Services\DateFilterService;

use function PHPUnit\Framework\isNull;

class UserController extends Controller
{
    public function __construct(private readonly ValidationService $validationService, private readonly ApiService $apiService, private readonly CurrencyHelper $currencyHelper)
    {
    }

public
function login(Request $request)
{
    $data = array();
    $email = $request->input('email');

    if ($email) {

        // echo $email;
        $user = DB::connection('azuresql')->table('contacts')->selectRaw("*")->where('moon_email', $email)->get();
        // echo '<pre>';print_r($user);die;

        if ($user->isEmpty()) {
            $status = "Error";
            $message = "We couldn't find the account associated with that email";

        } else {
            $status = "Success";
            $message = "User found !";
            $data = $user;
        }

    } else {
        $status = "Error";
        $message = "Please provide email";
    }

    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => $data
    );

    return response()->json($res);
}


public
function register(Request $request)
{
    $status = "Success";
    $message = "We are in Register";

    $res = array(
        "status" => $status,
        "message" => $message
    );

    return response()->json($res);
}


public
function influencerperformance(Request $request)
{
    $status = "Success";
    $message = "";
    $data = array();

    $record_id = $request->input('contact_id');
    $currency = 'AED'; //default
    $currency = $request->input('currency');
    $datefrom = $request->input('datefrom');
    $dateto = $request->input('dateto');

    if (empty($record_id)) {
        $res = array(
            "status" => $status,
            "message" => 'Record id is required',
            "data" => []
        );
        return response()->json($res);
    }

    if ($datefrom <= '2024-05-31' || $dateto <= '2024-05-31') {
        $contacts = DB::connection('azuresql')->table('contacts')->where('contacts.record_id', $record_id)->first();
        if ($contacts) {
            $user = DB::connection('mongodb')->table('users')->select('influencer_id', 'api_token')->where('email', $contacts->moon_email)->first();
        }
        if (empty($user)) {
            $res = array(
                "status" => $status,
                "message" => 'User not found',
                "data" => []
            );
            return response()->json($res);
        }
    }

    if ($datefrom <= '2024-05-31' && $dateto <= '2024-05-31') {
        $response = DateFilterService::dateFilter($user, $datefrom, $dateto);

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => $response
        );
        return response()->json($res);
    } elseif ($datefrom <= '2024-05-31' && $dateto >= '2024-06-01') {
        $result = DateFilterService::dateFilter($user, $datefrom, '2024-05-31');
        $datefrom = '2024-06-01';
    }

    $c = $this->currencyHelper->getCurrencyRateLists(true);
    $query = DB::connection('azuresql')
        ->table('coupon_performance_bi')
        ->leftJoin('coupon_history', function ($join) {
            $join->on('coupon_history.coupon_code', '=', 'coupon_performance_bi.coupon_code')
                ->on('coupon_history.contact_record_id', '=', 'coupon_performance_bi.contact_record_id')
                ->on('coupon_history.client_id', '=', 'coupon_performance_bi.client_id')
                ->whereColumn('coupon_performance_bi.order_date', '>=', DB::raw('coupon_history.from_date'))
                ->whereColumn('coupon_performance_bi.order_date', '<=', DB::raw('COALESCE(coupon_history.to_date, GETDATE())'));
        })
        ->selectRaw("SUM(coupon_performance_bi.net_payout)/" . $c['AED'] . " as total_net_payout,SUM(coupon_performance_bi.net_payout) as total_net_payout_aed,SUM(coupon_performance_bi.revenue_net)/" . $c['AED'] . " as total_net_revenue,SUM(coupon_performance_bi.revenue_net) as total_net_revenue_aed, SUM(coupon_performance_bi.net_orders_calc) as conversion_count, COUNT(DISTINCT coupon_performance_bi.coupon_code) AS total_coupons, COUNT(DISTINCT coupon_performance_bi.client_id) AS total_advertiser")
        ->where('coupon_performance_bi.contact_record_id', $record_id)
        ->where('coupon_history.coupon_status', 'Active');

    /** Filters */
    // if(!empty($currency)) {
    //     $query->where('coupon_performance_bi.currency', $currency);
    // }

    if (!empty($datefrom) && !empty($dateto)) {
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    } else {
        $datefrom = date("Y-m-1");
        $dateto = date("Y-m-t");
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    }

    $data = $query->first();

    $final_data = array();

    $final_data['total_net_payout'] = ($data->total_net_payout == null || $data->total_net_payout == '') ? ('0') : ("$data->total_net_payout");
    $final_data['total_net_payout_aed'] = ($data->total_net_payout_aed == null || $data->total_net_payout_aed == '') ? ('0') : ("$data->total_net_payout_aed");
    $final_data['total_net_revenue'] = ($data->total_net_revenue == null || $data->total_net_revenue == '') ? ('0') : ("$data->total_net_revenue");
    $final_data['total_net_revenue_aed'] = ($data->total_net_revenue_aed == null || $data->total_net_revenue_aed == '') ? ('0') : ("$data->total_net_revenue_aed");
    $final_data['coupons'] = ($data->total_coupons == null || $data->total_coupons == 0) ? ('0') : ("$data->total_coupons");
    $final_data['total_advertiser'] = ($data->total_advertiser == null || $data->total_advertiser == 0) ? ('0') : ("$data->total_advertiser");
    $final_data['total_conversion'] = ($data->conversion_count == null || $data->conversion_count == 0) ? ('0') : ("$data->conversion_count");
    $final_data['new_advertiser'] = '0';
    $final_data['new_advertiser_arrow'] = 'Down';

    $data = $final_data;
    if (isset($data) && !empty($data) && isset($result) && !empty($result)) {
        $data['total_net_payout'] = "" . $data['total_net_payout'] + $result['total_net_payout'] . "";
        $data['total_net_payout_aed'] = "" . $data['total_net_payout_aed'] + CurrencyHelper::convertToAed('USD', $result['total_net_payout']) . "";
        $data['total_net_revenue'] = "" . $data['total_net_revenue'] + $result['total_net_revenue'] . "";
        $data['total_net_revenue_aed'] = "" . $data['total_net_revenue_aed'] + CurrencyHelper::convertToAed('USD', $result['total_net_revenue']) . "";
        $data['coupons'] = "" . $data['coupons'] + $result['coupons'] . "";
        $data['total_advertiser'] = "" . $data['total_advertiser'] + $result['total_advertiser'] . "";
        $data['total_conversion'] = "" . $data['total_conversion'] + $result['total_conversion'] . "";
        $data['new_advertiser'] = "" . $data['new_advertiser'] + $result['new_advertiser'] . "";
        $data['new_advertiser_arrow'] = "" . $data['new_advertiser_arrow'] . "";
    }
    $status = 'Success';
    $message = 'Data fetched successfully !';


    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => $data
    );

    return response()->json($res);
}

public
function conversions(Request $request)
{

    $status = "Success";
    $message = "";
    $data = array();

    $record_id = $request->input('contact_id');
    // $currency = $request->input('currency');
    $datefrom = $request->input('datefrom');
    $dateto = $request->input('dateto');
    $coupon_codes = $request->input('coupon_codes');
    $client_ids = $request->input('client_ids');

    if (empty($record_id)) {
        $res = array(
            "status" => $status,
            "message" => 'Record id is required',
            "data" => []
        );
        return response()->json($res);
    }
    if ($datefrom <= '2024-05-31' || $dateto <= '2024-05-31') {
        $contact = DB::connection('azuresql')->table('contacts')->select('id', 'moon_email')->where('record_id', $record_id)->first();
        if ($contact) {
            $user = DB::connection('mongodb')->table('users')->select('influencer_id', 'api_token')->where('email', $contact->moon_email)->first();
        }
        if (empty($user)) {
            $res = array(
                "status" => $status,
                "message" => 'User not found',
                "data" => []
            );
            return response()->json($res);
        }
    }
    if ($datefrom <= '2024-05-31' && $dateto <= '2024-05-31') {
        $response = DateFilterService::dateFilterConversion($user, $datefrom, $dateto);

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => $response
        );
        return response()->json($res);
    } elseif ($datefrom <= '2024-05-31' && $dateto >= '2024-06-01') {
        $bothResponse = DateFilterService::dateFilterConversion($user, $datefrom, '2024-05-31');
        $datefrom = '2024-06-01';
    }

    $c = $this->currencyHelper->getCurrencyRateLists(true);
    $query = DB::connection('azuresql')
        ->table('coupon_performance_bi')
        ->leftJoin('coupon_history', function ($join) {
            $join->on('coupon_history.coupon_code', '=', 'coupon_performance_bi.coupon_code')
                ->on('coupon_history.contact_record_id', '=', 'coupon_performance_bi.contact_record_id')
                ->on('coupon_history.client_id', '=', 'coupon_performance_bi.client_id')
                ->where('coupon_performance_bi.order_date', '>=', DB::raw("coupon_history.from_date"))
                ->where('coupon_performance_bi.order_date', '<=', DB::raw("COALESCE(coupon_history.to_date, GETDATE())"));
        })->join('clients', 'coupon_performance_bi.client_id', '=', 'clients.id')
        ->selectRaw(
            DB::raw('sum(coupon_performance_bi.gross_revenue_aed) as gross_revenue_aed,
                        sum(COALESCE(coupon_performance_bi.net_revenue_calc, 0)) as net_revenue_aed,
                        sum(COALESCE(coupon_performance_bi.net_revenue_calc, 0))/' . $c['AED'] . ' as net_revenue,
                        coupon_performance_bi.coupon_code,
                        sum(coupon_performance_bi.order_gross) as total_gross_conversion,
                        sum(COALESCE(coupon_performance_bi.net_orders_calc, 0)) as conversions,
                        sum(coupon_performance_bi.gross_payout) as gross_payout,
                        sum(COALESCE(coupon_performance_bi.net_payout, 0))/' . $c['AED'] . ' as net_payout,
                        sum(COALESCE(coupon_performance_bi.net_payout, 0)) as net_payout_aed,
                        coupon_performance_bi.client_id,
                        coupon_performance_bi.client_name,
                        coupon_performance_bi.currency,
                        coupon_performance_bi.cpo_or_cps,
                        clients.logo'
            )
        )
        ->where('coupon_performance_bi.contact_record_id', $record_id)
        ->groupBy('coupon_performance_bi.coupon_code', 'coupon_performance_bi.client_id', 'coupon_performance_bi.client_name', 'coupon_performance_bi.currency', 'coupon_performance_bi.cpo_or_cps', 'clients.logo')
        ->where('coupon_history.coupon_status', 'Active');

    /** Filters */


    if (!empty($datefrom) && !empty($dateto)) {
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    } else {
        $datefrom = date("Y-m-1");
        $dateto = date("Y-m-t");
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    }

    if (!empty($coupon_codes)) {
        $query->whereIn('coupon_performance_bi.coupon_code', $coupon_codes);
    }

    if (!empty($client_ids)) {
        $query->whereIn('coupon_performance_bi.client_id', $client_ids);
    }

    // Hide Ounass GY coupon series from 1st July 2024
    $query->whereNot(function ($query) {
        $query->where('coupon_performance_bi.client_name', 'Ounass')
            ->where('coupon_performance_bi.coupon_code', 'LIKE', 'GY%')
            ->where('coupon_performance_bi.order_date', '>=', '2024-07-01');
    });

    $query->where(function ($query) {
        $query->where(function ($query) {
            $query->where('coupon_performance_bi.client_name', '<>', 'Sephora')
                ->orWhere(function ($query) {
                    $query->where('coupon_performance_bi.client_name', 'Sephora')
                        ->whereNotBetween('coupon_performance_bi.order_date', ['2024-06-20', '2024-06-30']);
                });
        })->orWhere(function ($query) {
            $query->where('coupon_performance_bi.client_name', 'Sephora')
                ->where('coupon_performance_bi.order_date', '>=', '2024-07-01')
                ->where(function ($query) {
                    $query->where('coupon_performance_bi.coupon_code', 'LIKE', 'MX%')
                        ->orWhere('coupon_performance_bi.coupon_code', 'LIKE', 'CX%');
                });
        });
    });

    $data = $query->get()->toArray();
    if (isset($data) && !empty($data) && isset($bothResponse) && !empty($bothResponse)) {
        $oldRes = array_column($bothResponse, 'coupon_code');
        foreach ($data as $key => $value) {
            $oldReskey = array_search($value->coupon_code, $oldRes);
            if ($oldReskey !== false && isset($bothResponse[$oldReskey])) {
                $conversionData = $bothResponse[$oldReskey];
                $data[$key]->net_revenue_aed = "" . $value->net_revenue_aed + CurrencyHelper::convertToAed('USD', $conversionData['net_revenue']) . "";
                $data[$key]->net_revenue = "" . $value->net_revenue + $conversionData['net_revenue'] . "";
                $data[$key]->coupon_code = "" . $value->coupon_code . "";
                $data[$key]->total_gross_conversion = "" . $value->total_gross_conversion + $conversionData['total_gross_conversion'] . "";
                $data[$key]->conversions = "" . $value->conversions + $conversionData['conversions'] . "";
                $data[$key]->net_payout = "" . $value->net_payout + $conversionData['net_payout'] . "";
                $data[$key]->net_payout_aed = "" . $value->net_payout_aed + CurrencyHelper::convertToAed('USD', $conversionData['net_payout']) . "";
                unset($bothResponse[$oldReskey]);
            }
        }
        $data = array_merge((array)$data, $bothResponse);
    }
    $status = 'Success';
    $message = 'Data fetched successfully !';

    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => $data
    );

    return response()->json($res);
}

public function conversions2(Request $request)
{
    try{

        $startTime = microtime(true);
//            sleep(10);
        $status = "Success";
        $message = "";
        $data = array();

        $record_id = $request->input('contact_id');
        // $currency = $request->input('currency');
        $datefrom = $request->input('datefrom');
        $dateto = $request->input('dateto');
        $coupon_codes = $request->input('coupon_codes');
        $client_ids = $request->input('client_ids');

        if (empty($record_id)) {
            $res = array(
                "status" => $status,
                "message" => 'Record id is required',
                "data" => []
            );
            return response()->json($res);
        }
        if ($datefrom <= '2024-05-31' || $dateto <= '2024-05-31') {
            $contact = DB::connection('azuresql')->table('contacts')->select('id', 'moon_email')->where('record_id', $record_id)->first();
            if ($contact) {
                $user = DB::connection('mongodb')->table('users')->select('influencer_id', 'api_token','hubspot_contact_id')->where('email', $contact->moon_email)->first();
            }
            if (empty($user)) {
                $res = array(
                    "status" => $status,
                    "message" => 'User not found',
                    "data" => []
                );
                return response()->json($res);
            }
        }
        if ($datefrom <= '2024-05-31' && $dateto <= '2024-05-31') {
            $response = DateFilterService::dateFilterConversion2($user, $datefrom, $dateto);

            $res = array(
                "status" => $status,
                "message" => $message,
                "data" => $response
            );
            return response()->json($res);
        } elseif ($datefrom <= '2024-05-31' && $dateto >= '2024-06-01') {
            $bothResponse = DateFilterService::dateFilterConversion2($user, $datefrom, '2024-05-31');
            $datefrom = '2024-06-01';
        }

        $c = $this->currencyHelper->getCurrencyRateLists(true);
        $query = DB::connection('azuresql')
            ->table('moon.moon_coupon_performance_bi')
//                ->leftJoin('moon.moon_coupon_history', function ($join) {
//                    $join->on('moon.moon_coupon_history.coupon_code', '=', 'moon.moon_coupon_performance_bi.coupon_code')
//                        ->on('moon.moon_coupon_history.contact_record_id', '=', 'moon.moon_coupon_performance_bi.contact_record_id')
//                        ->on('moon.moon_coupon_history.client_id', '=', 'moon.moon_coupon_performance_bi.client_id')
//                        ->where('moon.moon_coupon_performance_bi.order_date', '>=', DB::raw("moon.moon_coupon_history.from_date"))
//                        ->where('moon.moon_coupon_performance_bi.order_date', '<=', DB::raw("COALESCE(moon.moon_coupon_history.to_date, GETDATE())"));
//                })
            ->join('moon.moon_clients', 'moon.moon_coupon_performance_bi.client_id', '=', 'moon.moon_clients.id')
            ->selectRaw(
                DB::raw('sum(COALESCE([moon].[moon_coupon_performance_bi].net_revenue_calc, 0))/' . $c['AED'] . ' as net_revenue,
                                    [moon].[moon_coupon_performance_bi].coupon_code,
                                    sum(COALESCE([moon].[moon_coupon_performance_bi].net_orders_calc, 0)) as conversions,
                                    sum(COALESCE([moon].[moon_coupon_performance_bi].net_payout, 0))/' . $c['AED'] . ' as net_payout,
                                    sum(COALESCE([moon].[moon_coupon_performance_bi].net_payout, 0)) as net_payout_aed,
                                    [moon].[moon_coupon_performance_bi].client_name,
                                    [moon].[moon_clients].logo'
                )
            )
            ->where('moon.moon_coupon_performance_bi.contact_record_id', $record_id)
            ->groupBy('moon.moon_coupon_performance_bi.coupon_code', 'moon.moon_coupon_performance_bi.client_id', 'moon.moon_coupon_performance_bi.client_name', 'moon.moon_coupon_performance_bi.currency', 'moon.moon_coupon_performance_bi.cpo_or_cps', 'moon.moon_clients.logo')
            ->where('moon.moon_coupon_performance_bi.coupon_status', 'Active');

        /** Filters */


        if (!empty($datefrom) && !empty($dateto)) {
            $query->whereBetween('moon.moon_coupon_performance_bi.order_date', [$datefrom, $dateto]);
        } else {
            $datefrom = date("Y-m-1");
            $dateto = date("Y-m-t");
            $query->whereBetween('moon.moon_coupon_performance_bi.order_date', [$datefrom, $dateto]);
        }

        if (!empty($coupon_codes)) {
            $query->whereIn('moon.moon_coupon_performance_bi.coupon_code', $coupon_codes);
        }

        if (!empty($client_ids)) {
            $query->whereIn('moon.moon_coupon_performance_bi.client_id', $client_ids);
        }

        // Hide Ounass GY coupon series from 1st July 2024
        $query->whereNot(function ($query) {
            $query->where('moon.moon_coupon_performance_bi.client_name', 'Ounass')
                ->where('moon.moon_coupon_performance_bi.coupon_code', 'LIKE', 'GY%')
                ->where('moon.moon_coupon_performance_bi.order_date', '>=', '2024-07-01');
        });

        $query->where(function ($query) {
            $query->where(function ($query) {
                $query->where('moon.moon_coupon_performance_bi.client_name', '<>', 'Sephora')
                    ->orWhere(function ($query) {
                        $query->where('moon.moon_coupon_performance_bi.client_name', 'Sephora')
                            ->whereNotBetween('moon.moon_coupon_performance_bi.order_date', ['2024-06-20', '2024-06-30']);
                    });
            })->orWhere(function ($query) {
                $query->where('moon.moon_coupon_performance_bi.client_name', 'Sephora')
                    ->where('moon.moon_coupon_performance_bi.order_date', '>=', '2024-07-01')
                    ->where(function ($query) {
                        $query->where('moon.moon_coupon_performance_bi.coupon_code', 'LIKE', 'MX%')
                            ->orWhere('moon.moon_coupon_performance_bi.coupon_code', 'LIKE', 'CX%');
                    });
            });
        });

        $data = $query->get()->toArray();
        if (isset($data) && !empty($data) && isset($bothResponse) && !empty($bothResponse)) {
            $oldRes = array_column($bothResponse, 'coupon_code');
            foreach ($data as $key => $value) {
                $oldReskey = array_search($value->coupon_code, $oldRes);
                if ($oldReskey !== false && isset($bothResponse[$oldReskey])) {
                    $conversionData = $bothResponse[$oldReskey];
                    $data[$key]->net_revenue = "" . $value->net_revenue + $conversionData['net_revenue'] . "";
                    $data[$key]->coupon_code = "" . $value->coupon_code . "";
                    $data[$key]->conversions = "" . $value->conversions + $conversionData['conversions'] . "";
                    $data[$key]->net_payout = "" . $value->net_payout + $conversionData['net_payout'] . "";
                    $data[$key]->net_payout_aed = "" . $value->net_payout_aed + CurrencyHelper::convertToAed('USD', $conversionData['net_payout']) . "";
                    unset($bothResponse[$oldReskey]);
                }
            }
            $data = array_merge((array)$data, $bothResponse);
        }
        $totalconversions = $totalnetpayout = $totalnetpayoutaed  = $totalnetpayoutsar = $totalnetrevenue = 0;
        $clientlist = [];
        $finaldata = [];
        if (isset($data) && !empty($data)) {
            foreach ($data as $value) {
                if ($value->conversions > 0 && $value->conversions < 1) {
                    $value->conversions = ceil($value->conversions);
                } else {
                    $value->conversions = round($value->conversions);
                }
                $value->net_payout = round($value->net_payout);
                $value->net_payout_sar = round($value->net_payout * $c['SAR']);
                $value->net_payout_aed = round($value->net_payout_aed);
                $value->net_revenue = round($value->net_revenue);
                $value->net_revenue_aed = round($value->net_revenue * $c['AED']);
                $value->net_revenue_sar = round($value->net_revenue * $c['SAR']);
                $totalconversions = $totalconversions + $value->conversions;
                $totalnetpayout = $totalnetpayout + $value->net_payout;
                $totalnetpayoutaed = $totalnetpayoutaed + $value->net_payout_aed;
                $totalnetpayoutsar = $totalnetpayoutsar + $value->net_payout_sar;
                $totalnetrevenue = $totalnetrevenue + $value->net_revenue;
                array_push($finaldata, $value);
                if (!in_array($value->client_name, $clientlist)) {
                    array_push($clientlist, $value->client_name);
                }
            }
        }
        $status = 'Success';
        $message = 'Data fetched successfully !';

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => [
                "conversions" => $finaldata,
                "total_conversion" => $totalconversions,
                "total_net_payout" => round($totalnetpayout),
                "total_net_payout_aed" => round($totalnetpayoutaed),
                "total_net_payout_sar" => round($totalnetpayoutsar),
                "total_net_revenue" => $totalnetrevenue,
                "total_advertiser" => count($clientlist),
            ]
        );
        $endTime = microtime(true);
        $executionTime = $endTime - $startTime;

        if ($executionTime > 9) {
            return response()->json([
                "status" => "Error",
                "message" => 'Oops! Looks like there was a network issue. Please check your internet connection and try to reload the page',
                "data" => []
            ], 504); // 408 Request Timeout status code
        }

        return response()->json($res);
    }
    catch (\Exception $e) {
        $res = array(
            "status" => "Error",
            "message" => 'Something went wrong.',
            "data" => []
        );

        return response()->json($res);
    }
}


public function statisticsGraph(Request $request)
{
    try{
        $status = "Success";
        $message = "";
        $data = $finaldata = array();

        $record_id = $request->input('contact_id');
        // $currency = $request->input('currency');
        $datefrom = date('Y-m-d', strtotime('first day of january this year'));
        $dateto = date('Y-m-d', strtotime('last day of december this year'));

        if (empty($record_id)) {
            $res = array(
                "status" => $status,
                "message" => 'Record id is required',
                "data" => []
            );
            return response()->json($res);
        }
        if ($datefrom <= '2024-05-31' && $dateto >= '2024-06-01') {
            $query1 = DB::connection('azuresql')
                ->table('moon.statistics')
                ->selectRaw(
                    DB::raw('[moon].[statistics].hubspot_contact_id as contact_record_id,
                                    LEFT(CONVERT(VARCHAR, [moon].[statistics].order_date, 23), 7) AS month,
                                    sum(COALESCE([moon].[statistics].conversions, 0)) as conversions,
                                    sum(COALESCE([moon].[statistics].payout, 0)) as net_payout,
                                    count(DISTINCT(client_id)) as advertiser'
                    )
                )
                ->where('moon.statistics.hubspot_contact_id', $record_id)
                ->groupBy('moon.statistics.hubspot_contact_id', DB::raw('LEFT(CONVERT(VARCHAR,[moon].[statistics].[order_date], 23), 7)'));

            $query1->whereBetween('moon.statistics.order_date', [$datefrom, '2024-05-31']);
            $query1->orderBy('month','ASC');
            $bothResponse = $query1->get()->toArray();
            $datefrom = '2024-06-01';
        }

        $query = DB::connection('azuresql')
            ->table('moon.moon_coupon_performance_bi')
            ->selectRaw(
                DB::raw('[moon].[moon_coupon_performance_bi].contact_record_id,
                                    LEFT(CONVERT(VARCHAR, [moon].[moon_coupon_performance_bi].order_date, 23), 7) AS month,
                                    sum(COALESCE([moon].[moon_coupon_performance_bi].net_orders_calc, 0)) as conversions,
                                    sum(COALESCE([moon].[moon_coupon_performance_bi].net_payout, 0)) as net_payout,
                                    count(DISTINCT(client_id)) as advertiser'
                )
            )
            ->where('moon.moon_coupon_performance_bi.contact_record_id', $record_id)
            ->groupBy('moon.moon_coupon_performance_bi.contact_record_id', DB::raw('LEFT(CONVERT(VARCHAR, moon.moon_coupon_performance_bi.order_date, 23), 7)'))
            ->where('moon.moon_coupon_performance_bi.coupon_status', 'Active');

        /** Filters */


        if (!empty($datefrom) && !empty($dateto)) {
            $query->whereBetween('moon.moon_coupon_performance_bi.order_date', [$datefrom, $dateto]);
        }

        // Hide Ounass GY coupon series from 1st July 2024
        $query->whereNot(function ($query) {
            $query->where('moon.moon_coupon_performance_bi.client_name', 'Ounass')
                ->where('moon.moon_coupon_performance_bi.coupon_code', 'LIKE', 'GY%')
                ->where('moon.moon_coupon_performance_bi.order_date', '>=', '2024-07-01');
        });

        $query->where(function ($query) {
            $query->where(function ($query) {
                $query->where('moon.moon_coupon_performance_bi.client_name', '<>', 'Sephora')
                    ->orWhere(function ($query) {
                        $query->where('moon.moon_coupon_performance_bi.client_name', 'Sephora')
                            ->whereNotBetween('moon.moon_coupon_performance_bi.order_date', ['2024-06-20', '2024-06-30']);
                    });
            })->orWhere(function ($query) {
                $query->where('moon.moon_coupon_performance_bi.client_name', 'Sephora')
                    ->where('moon.moon_coupon_performance_bi.order_date', '>=', '2024-07-01')
                    ->where(function ($query) {
                        $query->where('moon.moon_coupon_performance_bi.coupon_code', 'LIKE', 'MX%')
                            ->orWhere('moon.moon_coupon_performance_bi.coupon_code', 'LIKE', 'CX%');
                    });
            });
        });
        $query->orderBy('month','ASC');
//        $data = $query->toRawSql();
        $data = $query->get()->toArray();
//
        if (isset($data) && !empty($data) && isset($bothResponse) && !empty($bothResponse)) {
            $data = array_merge($bothResponse,$data);
        }
//dd($data);
        if (isset($data) && !empty($data)) {
            foreach ($data as $value) {
                if ($value->conversions > 0 && $value->conversions < 1) {
                    $value->conversions = ceil($value->conversions);
                } else {
                    $value->conversions = round($value->conversions);
                }
                $value->net_payout_aed = round(CurrencyHelper::convertToAed('USD',$value->net_payout));
                $value->net_payout = round($value->net_payout);
                $value->month = date('M', strtotime($value->month));
                array_push($finaldata, $value);
            }
        }
        $status = 'Success';
        $message = 'Data fetched successfully !';

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => [
                "conversions" => $finaldata,
                "max_value" => 10000
            ]
        );

        return response()->json($res);
    }
    catch (\Exception $e) {
        $res = array(
            "status" => "Error",
            "message" => 'Something went wrong.',
            "data" => []
        );

        return response()->json($res);
    }
}


public function topPayout(Request $request)
{
    try{
        $status = "Success";
        $message = "";
        $data = $finaldata = array();

        $record_id = $request->input('contact_id');
        // $currency = $request->input('currency');
        $datefrom = $request->input('datefrom');
        $dateto = $request->input('dateto');

        if (empty($record_id)) {
            $res = array(
                "status" => $status,
                "message" => 'Record id is required',
                "data" => []
            );
            return response()->json($res);
        }
        if ($datefrom <= '2024-05-31' && $dateto <= '2024-06-01') {
            $query = "
            select hubspot_contact_id as contact_record_id,client_name as name,logo,
 sum(COALESCE([moon].[statistics].payout, 0)) as net_payout FROM [moon].[statistics] where  [moon].[statistics].[hubspot_contact_id] = ".$record_id." and [moon].[statistics].[order_date] between '".$datefrom."' and '".$dateto."' group by [moon].[statistics].[hubspot_contact_id],[moon].[statistics].[client_name],[moon].[statistics].[logo] ORDER BY
    net_payout DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
            ";
            $data = DB::select($query);
        }
        else{
$query = "
    select moon.moon_coupon_performance_bi.contact_record_id,
moon.moon_clients.name,
moon.moon_clients.logo,
moon.moon_coupon_performance_bi.company_record_id,
sum(COALESCE([moon].[moon_coupon_performance_bi].net_payout, 0)) as net_payout
    from [moon].[moon_coupon_performance_bi],[moon].[moon_clients]
    where [moon].[moon_coupon_performance_bi].[company_record_id] = [moon].[moon_clients].[record_id]
    AND [moon].[moon_coupon_performance_bi].[contact_record_id] = ? and [moon].[moon_coupon_performance_bi].[coupon_status] = 'Active' and [moon].[moon_coupon_performance_bi].[order_date] between ? and ? and not ([moon].[moon_coupon_performance_bi].[client_name] = 'Ounass' and [moon].[moon_coupon_performance_bi].[coupon_code] LIKE 'GY%' and [moon].[moon_coupon_performance_bi].[order_date] >= '2024-07-01') and (([moon].[moon_coupon_performance_bi].[client_name] <> 'Sephora' or ([moon].[moon_coupon_performance_bi].[client_name] = 'Sephora' and [moon].[moon_coupon_performance_bi].[order_date] not between '2024-06-20' and '2024-06-30')) or ([moon].[moon_coupon_performance_bi].[client_name] = 'Sephora' and [moon].[moon_coupon_performance_bi].[order_date] >= '2024-07-01' and ([moon].[moon_coupon_performance_bi].[coupon_code] LIKE 'MX%' or [moon].[moon_coupon_performance_bi].[coupon_code] LIKE 'CX%')))
     group by moon.moon_coupon_performance_bi.contact_record_id,moon.moon_coupon_performance_bi.company_record_id,moon.moon_clients.name,moon.moon_clients.logo
    ORDER BY net_payout DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY";
        $data = DB::select($query,[$record_id,$datefrom,$dateto]);
        }
        $recommend_brand = [];
        $clientlist = [];
        $clientquery = "SELECT DISTINCT company_record_id from moon.moon_coupons WHERE contact_record_id = ".$record_id." AND coupon_status = 'Active'";
        $clientdata = DB::select($clientquery);
        $clientdata = array_values(array_column($clientdata, 'company_record_id'));
        if (isset($data) && !empty($data)) {
            foreach ($data as $value) {
                $tempquery = "select name,logo,id from moon.client where id NOT IN ";
                $brand = DB::select("
                select name,logo,id from moon.client where category = (select category from moon.client where name = '".$value->name."') and id NOT IN (" .implode(', ', $clientdata). ")");
                if(!empty($brand)){
                    $client['name'] = $brand[0]->name;
                    $client['logo'] = $brand[0]->logo;
                    $client['id'] = $brand[0]->id;
                    array_push($clientdata,$brand[0]->id);
                    array_push($recommend_brand,$client);
                }
                $total_Ads = DB::select("
select count(*) as total_ads FROM dbo.tickets WHERE contact_id = ".$record_id." and ticket_status = 'Closed' and client_name = '".$value->name."' and ad_date between '".$datefrom."' and '".$dateto."'
                ");
                $value->net_payout_aed = round(CurrencyHelper::convertToAed('USD',$value->net_payout));
                $value->net_payout = round($value->net_payout);
                $value->total_ads = $total_Ads[0]->total_ads;
                array_push($finaldata, $value);
            }
        }
        $status = 'Success';
        $message = 'Data fetched successfully !';

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => [
                "top_payouts" => $finaldata,
                "recommend_brand" => $recommend_brand,
                ]
        );

        return response()->json($res);
    }
    catch (\Exception $e) {
        $res = array(
            "status" => "Error",
            "message" => 'Something went wrong.',
            "data" => []
        );

        return response()->json($res);
    }
}

public
function coupons(Request $request)
{
    $status = "Success";
    $message = "";
    $data = array();

    $record_id = $request->input('contact_id');
    // echo $record_id;die;

    if ($record_id) {
        $id = DB::connection('azuresql')->table('contacts')->selectRaw("id")->where('record_id', $record_id)->get();
        $id = json_decode($id);
        if (isset($id[0])) {
            $id = $id[0]->id;
            $data = DB::connection('azuresql')->table('dbo.coupons')->selectRaw("*")->where('contact_id', $id)->where('coupon_status', 'Active')->get()->unique();
            $status = 'Success';
            $message = 'Data fetched successfully !';
        } else {
            $status = "Error";
            $message = "We couldn't find the record of the given id of hubspot.";
        }
    } else {
        $status = "Error";
        $message = "Please provide valid record id of hubspot.";
    }

    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => $data
    );

    return response()->json($res);
}

public
function clients()
{
    $status = "Success";
    $message = "";
    $data = array();

    $data = DB::connection('azuresql')->table('dbo.clients')->selectRaw("id,name,logo")->where('status', "Active")->get()->unique();

    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => $data
    );

    return response()->json($res);
}

public
function brandRegister(Request $request)
{
    try {
        $validationError = $this->validationService->validateBrandRequest($request->all());

        if (!empty($validationError)) {
            return response()->json([
                'status' => false,
                'code' => 200,
                'message' => $validationError
            ]);
        }

        $findBrand = [
            'filters' => [
                [
                    'propertyName' => 'phone',
                    'operator' => 'EQ',
                    "value" => $request->phone
                ]
            ]
        ];

        $url = 'https://api.hubapi.com/crm/v3/objects/deals/search';
        $response = $this->apiService->postRequest($url, $findBrand);

        if (empty($response['results'])) {
            $data = [
                'properties' => [
                    'dealname' => $request->name,
                    'email' => $request->email,
                    'phone' => $request->phone,
                    'audience_location' => $request->target_location,
                    'industry' => $request->industry,
                    'service_type' => $request->service_type,
                    'website_url' => $request->website_url,
                    'budget' => $request->budget,
                    'pipeline' => '304081896',
                    'dealstage' => '487324881',
                ]
            ];

            $url = 'https://api.hubapi.com/crm/v3/objects/deals';
            $response = $this->apiService->postRequest($url, $data);

            return response()->json(['status' => true, 'code' => 200, 'message' => ['Brand created successfully.'], 'data' => [$response]]);
        } else {
            return response()->json(['status' => true, 'code' => 200, 'message' => [$request->phone . ' is already registered with brand.'], 'data' => []]);
        }
    } catch (\Exception $e) {
        return response()->json(['status' => true, 'code' => 200, 'message' => [$e->getMessage()], 'data' => []]);
    }
}

public
function brandLogin(Request $request)
{
    try {
        $validationError = $this->validationService->validateBrandLoginRequest($request->all());

        if (!empty($validationError)) {
            return response()->json([
                'status' => false,
                'code' => 200,
                'message' => $validationError,
                'data' => []
            ]);
        }

        $findBrand = [
            'filters' => [
                [
                    'propertyName' => 'phone',
                    'operator' => 'EQ',
                    "value" => $request->phone
                ]
            ]
        ];

        $url = 'https://api.hubapi.com/crm/v3/objects/deals/search';
        $response = $this->apiService->postRequest($url, $findBrand);

        if (!empty($response['results'])) {
            $data = [
                'To' => $request->phone,
                'Channel' => 'sms'
            ];

            $url = 'https://verify.twilio.com/v2/Services/' . config('constants.TWILIO_SERVICE_ID') . '/Verifications';
            $response = $this->apiService->OTPRequest($url, $data);

            return response()->json(['status' => true, 'code' => 200, 'message' => ['OTP send successfully.'], 'data' => ['status' => $response['status'], 'date_updated' => $response['date_updated'], 'to' => $response['to']]]);
        } else {
            return response()->json(['status' => true, 'code' => 200, 'message' => ['phone' => $request->phone . ' is not registered.'], 'data' => []]);
        }
    } catch (\Exception $e) {
        return response()->json(['status' => true, 'code' => 200, 'message' => [$e->getMessage()], 'data' => []]);
    }
}

public
function brandVerifyOTP(Request $request)
{
    try {
        $validationError = $this->validationService->validateBrandVerifyOTPRequest($request->all());

        if (!empty($validationError)) {
            return response()->json([
                'status' => false,
                'code' => 200,
                'message' => $validationError,
                'data' => []
            ]);
        }

        $findBrand = [
            'filters' => [
                [
                    'propertyName' => 'phone',
                    'operator' => 'EQ',
                    "value" => $request->phone
                ]
            ],
            'properties' => ['dealname', 'email', 'phone', 'audience_location']
        ];

        $url = 'https://api.hubapi.com/crm/v3/objects/deals/search';
        $response = $this->apiService->postRequest($url, $findBrand);

        if (!empty($response['results'])) {
            $data = [
                'To' => $request->phone,
                'Code' => $request->code
            ];

            $url = 'https://verify.twilio.com/v2/Services/' . config('constants.TWILIO_SERVICE_ID') . '/VerificationCheck';
            $apiResponse = $this->apiService->OTPRequest($url, $data);

            return response()->json(['status' => true, 'code' => 200, 'message' => ['OTP verified successfully.'], 'data' => [$this->brandResponse($response['results'])]]);
        } else {
            return response()->json(['status' => true, 'code' => 200, 'message' => ['phone' => $request->phone . ' is not registered.'], 'data' => []]);
        }
    } catch (\Exception $e) {
        return response()->json(['status' => true, 'code' => 200, 'message' => ['code' => 'OTP was wrong or expired.'], 'data' => []]);
    }
}

private
function brandResponse($brand)
{
    $response = [];
    if (!empty($brand[0])) {
        $brand = $brand[0];
        $response['id'] = $brand['id'];
        $response['name'] = $brand['properties']['dealname'];
        $response['email'] = $brand['properties']['email'];
        $response['phone'] = $brand['properties']['phone'];
        $response['target_location'] = $brand['properties']['audience_location'];
    }
    return $response;
}

public
function getUser($id)
{

    try {
        $user = DB::connection('azuresql')->table('contacts')->selectRaw("id,first_name, last_name, moon_email, email, phone,  created_at, updated_at")->where('record_id', $id)->first();

        $res = array(
            "status" => 200,
            "message" => 'User details.',
            "data" => $user
        );

        return response()->json($res);
    } catch (\Exception $e) {
        $res = array(
            "status" => 500,
            "message" => 'Something went wrong.',
            "data" => []
        );

        return response()->json($res);
    }
}

public
function getCurrencyRate()
{

    try {
        $data = $this->currencyHelper->getCurrencyRateLists();

        $res = array(
            "status" => "Success",
            "message" => 'Get currency rates lists.',
            "data" => $data
        );

        return response()->json($res);
    } catch (\Exception $e) {
        $res = array(
            "status" => "Error",
            "message" => 'Something went wrong.',
            "data" => []
        );

        return response()->json($res);
    }
}

public
function brandConversions(Request $request)
{
    $status = "Success";
    $message = "";
    $data = array();

    $brand_id = $request->input('brand_id');
    $datefrom = $request->input('datefrom');
    $dateto = $request->input('dateto');

    if (empty($brand_id)) {
        $res = array(
            "status" => $status,
            "message" => 'Brand id is required',
            "data" => []
        );
        return response()->json($res);
    }

    $c = $this->currencyHelper->getCurrencyRateLists(true);
    $query = DB::connection('azuresql')
        ->table('coupon_performance_bi')
        ->leftJoin('coupons', function ($join) {
            $join->on('coupons.coupon_code', '=', 'coupon_performance_bi.coupon_code');
        })->join('clients', 'coupon_performance_bi.client_id', '=', 'clients.id')
        ->selectRaw(
            DB::raw('sum(coupon_performance_bi.gross_revenue_aed) as gross_revenue_aed,
                    sum(COALESCE(coupon_performance_bi.net_revenue_aed, 0)) as net_revenue_aed,
                    sum(COALESCE(coupon_performance_bi.net_revenue_aed, 0))/' . $c['AED'] . ' as net_revenue,
                    coupon_performance_bi.coupon_code,
                    sum(coupon_performance_bi.order_gross) as total_gross_conversion,
                    sum(COALESCE(coupon_performance_bi.order_net, 0)) as conversions,
                    sum(coupon_performance_bi.gross_payout) as gross_payout,
                    sum(COALESCE(coupon_performance_bi.net_payout, 0))/' . $c['AED'] . ' as net_payout,
                    sum(COALESCE(coupon_performance_bi.net_payout, 0)) as net_payout_aed,
                    coupon_performance_bi.client_id,
                    coupon_performance_bi.client_name,
                    coupon_performance_bi.currency,
                    coupon_performance_bi.cpo_or_cps,
                    clients.logo'
            )
        )
        ->where('coupon_performance_bi.client_id', $brand_id)
        ->groupBy('coupon_performance_bi.coupon_code', 'coupon_performance_bi.client_id', 'coupon_performance_bi.client_name', 'coupon_performance_bi.currency', 'coupon_performance_bi.cpo_or_cps', 'clients.logo');

    /** Filters */

    if (!empty($datefrom) && !empty($dateto)) {
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    } else {
        $datefrom = date("Y-m-1");
        $dateto = date("Y-m-t");
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    }
    $data = $query->get()->toArray();
    $status = 'Success';
    $message = 'Data fetched successfully !';

    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => $data
    );

    return response()->json($res);
}

public
function brandPerformance(Request $request)
{
    $status = "Success";
    $message = "";
    $data = array();

    $brand_id = $request->input('brand_id');
    $currency = 'AED'; //default
    $currency = $request->input('currency');
    $datefrom = $request->input('datefrom');
    $dateto = $request->input('dateto');

    if (empty($brand_id)) {
        $res = array(
            "status" => $status,
            "message" => 'Brand id is required',
            "data" => []
        );
        return response()->json($res);
    }

    $c = $this->currencyHelper->getCurrencyRateLists(true);
    $query = DB::connection('azuresql')
        ->table('coupon_performance_bi')
        ->selectRaw("SUM(coupon_performance_bi.net_payout)/" . $c['AED'] . " as total_net_payout,
                            SUM(coupon_performance_bi.net_payout) as total_net_payout_aed,
                            SUM(coupon_performance_bi.revenue_net)/" . $c['AED'] . " as total_net_revenue,
                            SUM(coupon_performance_bi.revenue_net) as total_net_revenue_aed,
                            SUM(coupon_performance_bi.order_net) as conversion,
                            COUNT(DISTINCT coupon_performance_bi.coupon_code) AS total_coupons,
                            COUNT(DISTINCT coupon_performance_bi.contact_id) AS total_influencer,
                            COUNT(DISTINCT coupon_performance_bi.client_id) AS total_advertiser")
        ->where('client_id', $brand_id);
    /** Filters */
    // if(!empty($currency)) {
    //     $query->where('coupon_performance_bi.currency', $currency);
    // }

    if (!empty($datefrom) && !empty($dateto)) {
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    } else {
        $datefrom = date("Y-m-1");
        $dateto = date("Y-m-t");
        $query->whereBetween('coupon_performance_bi.order_date', [$datefrom, $dateto]);
    }

    $data = $query->first();

    $final_data = array();

    $final_data['total_net_payout'] = ($data->total_net_payout == null || $data->total_net_payout == '') ? ('0') : ("$data->total_net_payout");
    $final_data['total_net_payout_aed'] = ($data->total_net_payout_aed == null || $data->total_net_payout_aed == '') ? ('0') : ("$data->total_net_payout_aed");
    $final_data['total_net_revenue'] = ($data->total_net_revenue == null || $data->total_net_revenue == '') ? ('0') : ("$data->total_net_revenue");
    $final_data['total_net_revenue_aed'] = ($data->total_net_revenue_aed == null || $data->total_net_revenue_aed == '') ? ('0') : ("$data->total_net_revenue_aed");
    $final_data['coupons'] = ($data->total_coupons == null || $data->total_coupons == 0) ? ('0') : ("$data->total_coupons");
    $final_data['total_advertiser'] = ($data->total_advertiser == null || $data->total_advertiser == 0) ? ('0') : ("$data->total_advertiser");
    $final_data['total_conversion'] = ($data->conversion == null || $data->conversion == 0) ? ('0') : ("$data->conversion");
    $final_data['total_influencer'] = ($data->total_influencer == null || $data->total_influencer == 0) ? ('0') : ("$data->total_influencer");
    $final_data['new_advertiser'] = '0';
    $final_data['new_advertiser_arrow'] = 'Down';

    $data = $final_data;
    $status = 'Success';
    $message = 'Data fetched successfully !';


    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => $data
    );

    return response()->json($res);
}

public function activeCoupons(Request $request)
{
    try {
        $status = "Success";
        $message = "";
        $data = array();
        $record_id = $request->input('contact_id');
        if ($record_id) {
            $count = DB::connection('azuresql')
                ->table('moon.moon_coupons')
                ->leftJoin('moon.moon_clients', function ($join) {
                    $join->on('moon.moon_clients.record_id', '=', 'moon.moon_coupons.company_record_id');
                })
                ->where('moon.moon_coupons.contact_record_id', $record_id)
                ->where('moon.moon_coupons.coupon_status', 'Active')
                ->count();
            $offset = 0;
            $pagesize = 100;
            if ((isset($request->page) && !empty($request->page)) && (isset($request->pagesize) && !empty($request->pagesize))) {
                $pagesize = $request->pagesize;
                $offset = ($request->page - 1) * $request->pagesize;
            }
            $coupons = DB::connection('azuresql')
                ->table('moon.moon_coupons')
                ->leftJoin('moon.moon_clients', function ($join) {
                    $join->on('moon.moon_clients.record_id', '=', 'moon.moon_coupons.company_record_id');
                })
                ->where('moon.moon_coupons.contact_record_id', $record_id)
                ->where('moon.moon_coupons.coupon_status', 'Active')
                ->offset($offset)
                ->limit($pagesize)
                ->get();

            $couponArray = [];
            if (!empty($coupons)) {
                foreach ($coupons as $coupon) {
//                        dd($coupon);
                    $finaldata['brand_logo'] = $coupon->logo;
                    $finaldata['hs_coupon_id'] = $coupon->hs_coupon_id;
                    $finaldata['company_id'] = $coupon->company_record_id;
                    $finaldata['company_name'] = $coupon->name;
//                        $finaldata['Industry'] = $coupon->industry;
                    $finaldata['Industry'] = $coupon->category;
                    $finaldata['coupon_code'] = $coupon->coupon_code;
                    $finaldata['coupon_status'] = $coupon->coupon_status;
                    $couponArray[] = $finaldata;
                }
            }
            $data['total_coupon'] = $count;
            $data['coupon'] = $couponArray;

        } else {
            $status = "Error";
            $message = "Please provide valid record id of hubspot.";
        }

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => $data
        );

        return response()->json($res);

    } catch (\Exception $e) {
        $res = array(
            "status" => "Error",
            "message" => 'Something went wrong.',
            "data" => []
        );

        return response()->json($res);
    }

}

public function getRequestedCoupons(Request $request)
{
    try {
        $status = "Success";
        $message = "";
        $data = array();
        $record_id = $request->input('contact_id'); // Hubspot Record Id

        if ($record_id) {
            $property = [["propertyName" => "contact_id", "operator" => "EQ", "value" => "$record_id"]];
            $requestdata = [
                "limit" => 100,
                "after" => 0,
                "filterGroups" => [['filters' => $property]],
                "properties" => [
                    "coupon_request",
                    "company_id",
                    "status",
                    "type_of_request"
                ],
            ];
            $final_response = $this->callHubspotApi(json_encode($requestdata), '2-134583587/search?associations=contacts');
            $final_arr = [];
            foreach ($final_response['results'] as $coupons){
                $coupons['properties']["hs_task_body"] = $coupons['properties']["coupon_request"];
                $coupons['properties']["hs_task_subject"] = $coupons['properties']["coupon_request"];
                if($coupons['properties']["status"] == 'Open') {
                    $coupons['properties']["hs_task_status"] = 'WAITING';
                    $coupons['properties']["hs_task_is_completed"] = "0";
                } else if($coupons['properties']["status"] == 'Approved'){
                    $coupons['properties']["hs_task_status"] = "COMPLETED";
                    $coupons['properties']["hs_task_is_completed"] = "1";
                }
                array_push($final_arr,$coupons);
            }
            $final_response['results'] = $final_arr;

            $data = $final_response;

        } else {
            $status = "Error";
            $message = "Please provide valid record id of hubspot.";
        }

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => $data
        );

        return response()->json($res);

    } catch (\Exception $e) {
        $res = array(
            "status" => "Error",
            "message" => 'Something went wrong.',
            "data" => []
        );

        return response()->json($res);
    }


}

//Submit task request
public function submitRequestCoupon(Request $request)
{

    if (!empty($request->input('contact_id'))) {
        $id = $request->input('contact_id'); // Hubspot Record Id
        $contacts = DB::connection('azuresql')->table('contacts')->where('contacts.record_id', $id)->first();
        $hubspot_record_id = $id;
        if ($hubspot_record_id > 0) {
            if( strpos($request->advertiser, ',') !== false ) {
                $advertiser = explode(',', $request->advertiser);
                $companies = explode(',', $request->company_id); //company Id
                $inputs = [];
                foreach ($advertiser as $key=>$adv){
                    $data = [
                        "properties" => [
                            "coupon_request" => $adv,
                            "company_id" => $companies[$key],
                            "status" => $request->status,
                            "contact_id" => $request->contact_id,
                            "type_of_request" => $request->requested_type,

                        ],
                        "associations" => [
                            [
                                "to" => ["id" => $hubspot_record_id],
                                "types" => [["associationCategory" => "USER_DEFINED", "associationTypeId" => 355]]
                            ]
                        ]
                    ];
                    $response = $this->callHubspotApi(json_encode($data), '2-134583587/');
                }
            } else {
                    $data = [
                        "properties" => [
                            "coupon_request" => $request->advertiser,
                            "company_id" => $request->company_id,
                            "status" => $request->status,
                            "contact_id" => $request->contact_id,
                            "type_of_request" => $request->requested_type,

                        ],
                        "associations" => [
                            [
                                "to" => ["id" => $hubspot_record_id],
                                "types" => [["associationCategory" => "USER_DEFINED", "associationTypeId" => 355]]
                            ]
                        ]
                    ];
                    $response = $this->callHubspotApi(json_encode($data), '2-134583587/');

                }
            }


            return response()->json([
                "status" => "Success",
                "result" => true,
                "message" => "User requested for coupon successfully",
                "data" => $response
            ]);
        } else {
            return response()->json([
                'status' => 'Error',
                "result" => false,
                "data" => [],
                "message" => "Please give correct contact id"
            ], 200);
        }
}

public function callHubspotApi($data, $endpoint)
{
    $curl = curl_init();
    curl_setopt_array($curl, array(
        CURLOPT_URL => env('hubspot_url') . $endpoint,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => $data,
        CURLOPT_HTTPHEADER => array(
            'Authorization: Bearer ' . env('hubspot_token'),
            'Content-Type: application/json'
        ),
    ));
    // curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0); // Skip SSL Verification
    $response = curl_exec($curl);
    curl_close($curl);
    return json_decode($response, true);
}

}