<?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'));

        $max_payout = 10000;
        $max_orders = 1000;
        $max_advertiser = 10;
        $highest_payout = $highest_orders = $highest_advertiser = $i = 0;
        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_aed, 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);
        }
        if (isset($data) && !empty($data)) {
            foreach ($data as $value) {
                $i++;
                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);
                if($value->net_payout > $highest_payout){
                    $highest_payout = $value->net_payout;
                }
                if($value->advertiser > $highest_advertiser){
                    $highest_advertiser = $value->advertiser;
                }
                if($value->conversions > $highest_orders){
                    $highest_orders = $value->conversions;
                }

            }
        }
        if ($highest_payout > 0) {
            if ($highest_payout >= 10000 && $highest_payout < 40000) {
                $max_payout = 40000;
            } elseif ($highest_payout >= 40000) {
                $max_payout = 40000;
            }
        }
        if ($highest_orders > 0) {
            if ($highest_orders >= 1000 && $highest_orders < 2000) {
                $max_orders = 2000;
            } elseif ($highest_orders >= 3000) {
                $max_orders = 3000;
            }
        }
        if ($highest_advertiser > 0) {
            if ($highest_advertiser >= 10 && $highest_advertiser < 20) {
                $max_advertiser = 20;
            } elseif ($highest_advertiser >= 20) {
                $max_advertiser = 30;
            }
        }
        $status = 'Success';
        $message = 'Data fetched successfully !';

        $res = array(
            "status" => $status,
            "message" => $message,
            "data" => [
                "conversions" => $finaldata,
                "max_payout" => $max_payout,
                "max_orders" => $max_orders,
                "max_advertiser" => $max_advertiser
            ]
        );

        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') {

            $data = DB::connection('azuresql')
                ->table('moon.statistics')
                ->selectRaw(
                    DB::raw('[moon].[statistics].hubspot_contact_id as contact_record_id,
                                    [moon].[statistics].client_name as name,
                                    [moon].[statistics].logo,
                                    sum(COALESCE([moon].[statistics].payout_aed, 0)) as net_payout'
                    )
                )
                ->where('moon.statistics.hubspot_contact_id', $record_id)
                ->whereBetween('moon.statistics.order_date', [$datefrom, $dateto])
                ->groupBy('moon.statistics.hubspot_contact_id', 'moon.statistics.client_name', 'moon.statistics.logo')
                ->orderBy('net_payout', 'DESC')->offset(0)->limit(3)->get();
        } else {
            $query = DB::connection('azuresql')
                ->table('moon.moon_coupon_performance_bi')
                ->leftJoin('moon.moon_clients', function ($join) {
                    $join->on('moon.moon_clients.record_id', '=', 'moon.moon_coupon_performance_bi.company_record_id');
                })->selectRaw(
                    DB::raw('[moon].[moon_coupon_performance_bi].contact_record_id,
                                    [moon].[moon_coupon_performance_bi].company_record_id,
                                    [moon].[moon_clients].name,
                                    [moon].[moon_clients].logo,
                                    sum(COALESCE([moon].[moon_coupon_performance_bi].net_payout, 0)) as net_payout'
                    )
                )
                ->where('moon.moon_coupon_performance_bi.contact_record_id', $record_id)
                ->whereBetween('moon.moon_coupon_performance_bi.order_date', [$datefrom, $dateto])
                ->groupBy('moon.moon_coupon_performance_bi.contact_record_id', 'moon.moon_coupon_performance_bi.company_record_id', 'moon.moon_clients.name', 'moon.moon_clients.logo')
                ->where('moon.moon_coupon_performance_bi.coupon_status', 'Active');
// 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('net_payout', 'DESC');
            $data = $query->offset(0)->limit(3)->get();
        }
        $recommend_brand = [];
        $clientArray = DB::connection('azuresql')
            ->table('moon.moon_coupons')
            ->select(DB::raw('DISTINCT [moon].[moon_coupons].company_record_id'))
            ->where('moon.moon_coupons.contact_record_id', $record_id)
            ->where('moon.moon_coupons.coupon_status', 'Active')
            ->get()->toArray();
        $clientdata = array_values(array_column($clientArray, 'company_record_id'));
        if (isset($data) && !empty($data)) {
            foreach ($data as $value) {
                $name = $value->name;
                $brand = DB::connection('azuresql')
                    ->table('moon.client')
                    ->select('name', 'logo', 'id')
                    ->where('category', function ($query1) use ($name) {
                        $query1->select('category')
                            ->from('moon.client')
                            ->where('name', $name);
                    })
                    ->whereNotIn('id', $clientdata)->get();
                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::connection('azuresql')
                    ->table('dbo.tickets')
                    ->select(DB::raw('count(*) as total_ads'))
                    ->where('contact_id', $record_id)
                    ->where('client_name', $value->name)
                    ->whereBetween('ad_date', [$datefrom, $dateto])
                    ->where('ticket_status', 'Closed')->get();

                $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"], [
                'propertyName' => 'type_of_request',
                'operator' => 'EQ',
                "value" => 'Requested'
            ]];
            $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);
    }
}

//Submit Coupon Invitation
public function submitCouponInvitation(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();
        $invitation_id = $request->input('invitation_id');
        $status = $request->input('status');
        $hubspot_record_id = $id;
        if ($hubspot_record_id > 0) {
            $requestdata = [
                "properties" => [
                    "status" => $status
                ],
            ];
            $response = $this->callHubspotApi(json_encode($requestdata), '2-134583587/' . $invitation_id, 'PATCH');

        }
        return response()->json([
            "status" => "Success",
            "result" => true,
            "message" => "We approved your invitation",
            "data" => $response
        ]);
    } else {
        return response()->json([
            'status' => 'Error',
            "result" => false,
            "data" => [],
            "message" => "Please give correct contact id"
        ], 200);
    }
}

//Get Coupon Invitation
public function getInvitedCoupons(Request $request)
{

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

        if ($record_id) {
            $brands = DB::connection('azuresql')->table('moon.client')->pluck('logo', 'name')->mapWithKeys(function ($value, $key) {
                return [strtolower($key) => $value];
            })->toArray();

            $property = [["propertyName" => "contact_id", "operator" => "EQ", "value" => "$record_id"], [
                'propertyName' => 'type_of_request',
                'operator' => 'EQ',
                "value" => 'Invited'
            ]];
            $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"];
                $client_name = strtolower($coupons['properties']["coupon_request"]);
		$logo = $brands[$client_name];	
        	if(isset($logo)){       
	 $coupons['properties']['logo'] = $logo;} else {$coupons['properties']['logo']="";}
                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);
    }
}

public function getInvoices(Request $request)
{

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

        if ($record_id) {
                        $final_response = $this->callKissFlowApi("Invoice_Generation_and_Processing_Paymen/item??page_number=1&page_size=10000&q=" . $record_id);
            $final_arr = [];
            $flag = false;
            $latest = 0;
            foreach ($final_response['Data'] as $invoice_document) {
                //$invoice_document['Total_Amount_to_be_Paid_Incl_SOA'] = round($invoice_document['Total_Amount_to_be_Paid_Incl_SOA'],2);
                if(isset($invoice_document['SOA_Master']) && is_array($invoice_document['SOA_Master'])){
                    if(count($invoice_document['SOA_Master']) == 0): $invoice_document['SOA_Master'] = (object) $invoice_document['SOA_Master']; endif;
                }
                if ($flag==false)$latest = $invoice_document['Payment_Amount'];
                $flag=true;
                $documents = [];
                foreach ($invoice_document['Invoice_Document_1'] as $key => $invoice) {
                    $invoice_document['Invoice_Document_1'][$key]['url'] = 'Invoice_Generation_and_Processing_Paymen/' . $invoice_document['_id'] . '/Invoice_Document_1/attachment/' . $invoice['id'];
                }

                if(isset($invoice_document['Transaction_Receipt']) && count($invoice_document['Transaction_Receipt']) > 0){
                    foreach ($invoice_document['Transaction_Receipt'] as $tkey => $tr) {
                        $invoice_document['Transaction_Receipt'][$tkey]['url'] = 'Invoice_Generation_and_Processing_Paymen/' . $invoice_document['_id'] . '/Transaction_Receipt/attachment/' . $tr['id'];
                    }
                }


                array_push($final_arr, $invoice_document);
            }
            $data['invoices'] = $final_arr;
            $response_soa_string = $this->callKissFlowDataSetAPI($record_id)['Data'][0];
            $numericValue = 0;
            if(isset($response_soa_string['SOA'])){
                $numericValue = floatval($response_soa_string['SOA']);
            }
            $data['totl_amount'] = $numericValue+$latest;

        } 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 callKissFlowApi($endpoint)
{
    $curl = curl_init();

    curl_setopt_array($curl, array(
        CURLOPT_URL => env('kissflow_adminurl') . $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 => 'GET',
        CURLOPT_HTTPHEADER => array(
            'Accept: application/json',
            'X-Access-Key-Id:' . env('kissflow_access_key'),
            'X-Access-Key-Secret: ' . env('kisslow_secret_key'),
        ),
    ));

    $response = curl_exec($curl);
    curl_close($curl);
    return json_decode($response, true);


}

public function callKissFlowDataSetAPI($recordId)
{
    $curl = curl_init();

    curl_setopt_array($curl, array(
        CURLOPT_URL => 'https://itcan.kissflow.com/dataset/2/Ac8PBeAdsSTB/SOA_Master/view/General_View/list?q='.$recordId,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_HTTPHEADER => array(
            'Accept: application/json',
            'X-Access-Key-Id:' . env('kissflow_access_key'),
            'X-Access-Key-Secret: ' . env('kisslow_secret_key'),
        ),
    ));

    $response = curl_exec($curl);

    curl_close($curl);

    return json_decode($response, true);


}

public function callHubspotApi($data, $endpoint, $method = 'POST')
{
    $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 => $method,
        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);
}

public function AdSummary(Request $request){
    $startOfMonth = date('Y-m-01');
    $endOfMonth = date('Y-m-t');
    $startOfLastMonth = date('Y-m-01', strtotime('first day of last month'));
    $endOfLastMonth = date('Y-m-t', strtotime('last day of last month'));
    $startOfThreeMonthsAgo = date('Y-m-01', strtotime('first day of -3 months'));

    $current_month_total_Ads = DB::connection('azuresql')
        ->table('dbo.tickets')
        ->select(DB::raw('count(*) as total_ads'))
        ->where('contact_id', $request->contact_id)
        ->whereBetween('ad_date', [$startOfMonth, $endOfMonth])
        ->where('ticket_status', 'Closed')->get();

    $last_month_total_Ads = DB::connection('azuresql')
        ->table('dbo.tickets')
        ->select(DB::raw('count(*) as total_ads'))
        ->where('contact_id', $request->contact_id)
        ->whereBetween('ad_date', [$startOfLastMonth, $endOfLastMonth])
        ->where('ticket_status', 'Closed')->get();

    $difference = (($current_month_total_Ads[0]->total_ads - $last_month_total_Ads[0]->total_ads) * 100 / $last_month_total_Ads[0]->total_ads);

    $last_three_month_payout = DB::connection('azuresql')
        ->table('moon.moon_coupon_performance_bi')
        ->leftJoin('moon.moon_clients', function ($join) {
            $join->on('moon.moon_clients.record_id', '=', 'moon.moon_coupon_performance_bi.company_record_id');
        })->selectRaw(
            DB::raw('[moon].[moon_coupon_performance_bi].contact_record_id,
                                    [moon].[moon_coupon_performance_bi].company_record_id,
                                    [moon].[moon_clients].name,
                                    [moon].[moon_clients].logo,
                                    sum(COALESCE([moon].[moon_coupon_performance_bi].net_payout, 0)) as net_payout'
            )
        )
        ->where('moon.moon_coupon_performance_bi.contact_record_id', $request->contact_id)
        ->whereBetween('moon.moon_coupon_performance_bi.order_date', [$startOfThreeMonthsAgo, $endOfLastMonth])
        ->groupBy('moon.moon_coupon_performance_bi.contact_record_id', 'moon.moon_coupon_performance_bi.company_record_id', 'moon.moon_clients.name', 'moon.moon_clients.logo')
        ->where('moon.moon_coupon_performance_bi.coupon_status', 'Active')->orderBy('net_payout', 'DESC')->get();

    $top_brands = DB::connection('azuresql')
        ->table('moon.moon_clients')->select('name', 'logo')
        ->whereIn('name' , ["Ounass","Sephora","Bloomingdales"])->get();
//    dd($last_three_month_payout);
    $data = [];
    if(!empty($last_three_month_payout)){
        foreach ($last_three_month_payout as $value){
            $total_Ads = DB::connection('azuresql')
                ->table('dbo.tickets')
                ->select(DB::raw('count(*) as total_ads'))
                ->where('contact_id', $request->contact_id)
                ->where('client_id', (int)$value->company_record_id)
                ->whereBetween('ad_date', [$startOfThreeMonthsAgo, $endOfLastMonth])
                ->where('ticket_status', 'Closed')->get();

            if($total_Ads[0]->total_ads != 0){
                $tempdata['average_payout'] = round($value->net_payout/$total_Ads[0]->total_ads);
                $tempdata['company_record_id'] = $value->company_record_id;
                $tempdata['client_name'] = $value->name;
                $tempdata['logo'] = $value->logo;
                array_push($data,$tempdata);
            }

        }
    }
    usort($data, function($a, $b) {
        return $b['average_payout'] <=> $a['average_payout'];
    });
    $status = 'Success';
    $message = 'Data fetched successfully !';
    $res = array(
        "status" => $status,
        "message" => $message,
        "data" => [
            "monthly_difference" => round($difference),
            "top_brands" => $top_brands,
            "average_ad" => array_slice($data, 0, 3),
        ]
    );

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

}

}