<?php

namespace App\Http\Controllers\Owner\Analytic;

use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Http\Helpers\ResponseBuilder;
use App\Modules\Shared\Validator\Infraestructure\LaravelDTOGenerator;
use App\Http\Controllers\Owner\Analytic\Dto\AnalyticLeadListDayDTO;

use App\Modules\Analytics\Sales\Application\Reports\AnalyticLeadReport;


class AnalyticLeadListDayController extends Controller
{
	private $oDomain;


	public function __construct
	(
		private LaravelDTOGenerator $oLaravelDTOGenerator
	)
	{
        $this->oDomain = "Analytic Lead List Days";
    }


	public function __invoke(Request $request)
    {
		//------------------------------------------------------------------------------
		//	VALIDATE
		//------------------------------------------------------------------------------
		if (is_string($request->Id)) {
			$request->merge([
				'Id' => json_decode($request->Id, true)
			]);
		}
		$oDTO = $this->oLaravelDTOGenerator->generate($request->all(), $this->oDomain, AnalyticLeadListDayDTO::class);


		//------------------------------------------------------------------------------
		//	VARIABLES
		//------------------------------------------------------------------------------
		$pDate_Start		= $oDTO->DateStart;
		$pDate_End			= $oDTO->DateEnd;
		$pDate_Start_YoY 	= date('Y-m-d', strtotime('-1 year', strtotime($pDate_Start)));
		$pDate_End_YoY		= date('Y-m-d', strtotime('-1 year', strtotime($pDate_End)));

		$pGroup				= $oDTO->Group;		// NONE - CHANNEL - SELLER
		$pView				= $oDTO->View;		// RANKING - STACKED - SPECIFIC
		$pId				= $oDTO->Id;		// Array of IDS
		$pExport			= $oDTO->Export;


		//------------------------------------------------------------------------------
		//	FUNCTION
		//------------------------------------------------------------------------------
		if ($pGroup == "NONE")
		{
			$oSubQuery 		= DB::query();

			$oSubQuery->from('v_leads');
			$oSubQuery->selectRaw("
				Lead_Date_Year AS Year,
				Lead_Date_Month AS Month,
				Lead_Date_Day AS Day,
				COUNT(*) AS Total_Received,
				SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted,
				SUM(CASE WHEN Lead_Status = 8 THEN 1 ELSE 0 END) AS Total_Lost
			");
			$oSubQuery->whereBetween('Lead_Date', [$pDate_Start_YoY, $pDate_End]);
			$oSubQuery->groupBy("Lead_Date_Year");
			$oSubQuery->groupBy("Lead_Date_Month");
			$oSubQuery->groupBy("Lead_Date_Day");


			$oQuery = DB::query();

			$oQuery->fromSub($oSubQuery, "sub");
			$oQuery->leftJoinSub(
				$oSubQuery,
				"pre",
				function ($oJoinQuery) {
					$oJoinQuery->on("pre.Day", "=", "sub.Day");
					$oJoinQuery->on("pre.Month", "=", "sub.Month");
					$oJoinQuery->on("pre.Year", "=", DB::raw("sub.Year - 1"));
				}
			);
			$oQuery->selectRaw("
				CONCAT (sub.Day, '/', sub.Month, '/', sub.Year) AS Label,
				CONCAT (sub.Day, ' ', fn_month_name_get(sub.Month, 'SHORT'), ' ', sub.Year) AS Title,
				sub.Year,
				sub.Month,
				sub.Day,
				sub.Total_Received,
				sub.Total_Converted,
				sub.Total_Lost,
				CASE
					WHEN IFNULL(LAG(sub.Total_Received) OVER (PARTITION BY Year ORDER BY Month), 0) = 0
					THEN 0
					ELSE
						(sub.Total_Received - LAG(sub.Total_Received) OVER (PARTITION BY Year ORDER BY Month) ) / LAG(sub.Total_Received) OVER (PARTITION BY Year ORDER BY Month )
				END AS MoM_Received,
				CASE
					WHEN IFNULL(LAG(sub.Total_Converted) OVER (PARTITION BY Year ORDER BY Month), 0) = 0
					THEN 0
					ELSE
						(sub.Total_Converted - LAG(sub.Total_Converted) OVER (PARTITION BY Year ORDER BY Month) ) / LAG(sub.Total_Converted) OVER (PARTITION BY Year ORDER BY Month )
				END AS MoM_Converted,
				CASE
					WHEN IFNULL(LAG(sub.Total_Lost) OVER (PARTITION BY Year ORDER BY Month), 0) = 0
					THEN 0
					ELSE
						(sub.Total_Lost - LAG(sub.Total_Lost) OVER (PARTITION BY Year ORDER BY Month) ) / LAG(sub.Total_Lost) OVER (PARTITION BY Year ORDER BY Month )
				END AS MoM_Lost,

				IFNULL(pre.Total_Received, 0) AS Previous_Total_Received,
				IFNULL(pre.Total_Converted, 0) AS Previous_Total_Converted,
				IFNULL(pre.Total_Lost, 0) AS Previous_Total_Lost,

				CASE
					WHEN IFNULL(pre.Total_Received, 0) = 0
					THEN 0
					ELSE (sub.Total_Received - pre.Total_Received) / pre.Total_Received
				END AS YoY_Received,
				CASE
					WHEN IFNULL(pre.Total_Converted, 0) = 0
					THEN 0
					ELSE (sub.Total_Converted - pre.Total_Converted) / pre.Total_Converted
				END AS YoY_Converted,
				CASE
					WHEN IFNULL(pre.Total_Lost, 0) = 0
					THEN 0
					ELSE (sub.Total_Lost - pre.Total_Lost) / pre.Total_Lost
				END AS YoY_Lost,

				CASE
					WHEN IFNULL(sub.Total_Converted, 0) = 0
					THEN 0
					ELSE ((sub.Total_Converted * 100.0) / sub.Total_Received) / 100
				END AS Rate_Converted,
				CASE
					WHEN IFNULL(sub.Total_Lost, 0) = 0
					THEN 0
					ELSE ((sub.Total_Lost * 100.0) / sub.Total_Received) / 100
				END AS Rate_Lost
			");
			$oQuery->whereBetween(
				DB::raw("STR_TO_DATE(CONCAT(sub.Year,'-',sub.Month,'-01'), '%Y-%m-%d')"),
				[$pDate_Start, $pDate_End]
			);
			$oQuery->orderBy("Year", "ASC");
			$oQuery->orderBy("Month", "ASC");
			$oQuery->orderBy("Day", "ASC");

			$oData = $oQuery->get();
		}
		else if ($pGroup == "CHANNEL")
		{
			$oCurrentQuery = DB::query();
			$oCurrentQuery->from('v_leads');
			$oCurrentQuery->selectRaw("
				Id_Channel,
				Lead_Date_Year AS Year,
				Lead_Date_Month AS Month,
				Lead_Date_Day AS Day,
				COUNT(*) AS Total_Received,
				SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted,
				SUM(CASE WHEN Lead_Status = 8 THEN 1 ELSE 0 END) AS Total_Lost
			");
			$oCurrentQuery->whereBetween('Lead_Date', [$pDate_Start, $pDate_End]);


			$oPreviusQuery = DB::query();
			$oPreviusQuery->from('v_leads');
			$oPreviusQuery->selectRaw("
				Id_Channel,
				Lead_Date_Year AS Year,
				Lead_Date_Month AS Month,
				Lead_Date_Day AS Day,
				COUNT(*) AS Total_Received,
				SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted,
				SUM(CASE WHEN Lead_Status = 8 THEN 1 ELSE 0 END) AS Total_Lost
			");
			$oPreviusQuery->whereBetween('Lead_Date', [$pDate_Start_YoY, $pDate_End_YoY]);


			if ($pView == "RANKING")
			{
				$oCurrentQuery->groupBy("Id_Channel");
				$oPreviusQuery->groupBy("Id_Channel");


				$oParam		= "
					wc.Id_WebChannel AS Id,
					wc.WebChannel_Name as Label,
					wc.WebChannel_Name AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_web_channel AS wc", "wc.Id_WebChannel", "=", "cur.Id_Channel");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Channel",
					"=",
					"cur.Id_Channel"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy('Rank_Current');

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_Channel", "Lead_Date_Year", "Lead_Date_Month", "Lead_Date_Day");
				$oPreviusQuery->groupBy("Id_Channel", "Lead_Date_Year", "Lead_Date_Month", "Lead_Date_Day");


				$oParam		= "
					wc.Id_WebChannel AS Id,
					wc.WebChannel_Name as Label,
					wc.WebChannel_Name AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_web_channel AS wc", "wc.Id_WebChannel", "=", "cur.Id_Channel");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Channel",
					"=",
					"cur.Id_Channel"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_Channel", $pId );
				$oQuery->groupBy("cur.Id_Channel", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_Channel", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}
		else if ($pGroup == "SELLER")
		{
			$oCurrentQuery = DB::query();
			$oCurrentQuery->from('v_leads');
			$oCurrentQuery->selectRaw("
				Id_Admin,
				Lead_Date_Year AS Year,
				Lead_Date_Month AS Month,
				Lead_Date_Day AS Day,
				COUNT(*) AS Total_Received,
				SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted,
				SUM(CASE WHEN Lead_Status = 8 THEN 1 ELSE 0 END) AS Total_Lost
			");
			$oCurrentQuery->whereBetween('Lead_Date', [$pDate_Start, $pDate_End]);


			$oPreviusQuery = DB::query();
			$oPreviusQuery->from('v_leads');
			$oPreviusQuery->selectRaw("
				Id_Admin,
				Lead_Date_Year AS Year,
				Lead_Date_Month AS Month,
				Lead_Date_Day AS Day,
				COUNT(*) AS Total_Received,
				SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted,
				SUM(CASE WHEN Lead_Status = 8 THEN 1 ELSE 0 END) AS Total_Lost
			");
			$oPreviusQuery->whereBetween('Lead_Date', [$pDate_Start_YoY, $pDate_End_YoY]);


			if ($pView == "RANKING")
			{
				$oCurrentQuery->groupBy("Id_Admin");
				$oPreviusQuery->groupBy("Id_Admin");


				$oParam		= "
					adm.Id_Admin AS Id,
					adm.Admin_Name as Label,
					CONCAT(adm.Admin_Name, ' ', adm.Admin_LastName) AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_admin AS adm", "adm.Id_Admin", "=", "cur.Id_Admin");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Admin",
					"=",
					"cur.Id_Admin"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy('Rank_Current');

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_Admin", "Lead_Date_Year", "Lead_Date_Month", "Lead_Date_Day");
				$oPreviusQuery->groupBy("Id_Admin", "Lead_Date_Year", "Lead_Date_Month", "Lead_Date_Day");


				$oParam		= "
					adm.Id_Admin AS Id,
					adm.Admin_Name as Label,
					CONCAT(adm.Admin_Name, ' ', adm.Admin_LastName) AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_admin AS adm", "adm.Id_Admin", "=", "cur.Id_Admin");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_Admin",
					"=",
					"cur.Id_Admin"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_Admin", $pId );
				$oQuery->groupBy("cur.Id_Admin", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_Admin", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}
		else if ($pGroup == "NATIONALITY")
		{
			$oCurrentQuery = DB::query();
			$oCurrentQuery->from('v_leads');
			$oCurrentQuery->selectRaw("
				Id_UserCountry,
				Lead_Date_Year AS Year,
				Lead_Date_Month AS Month,
				Lead_Date_Day AS Day,
				COUNT(*) AS Total_Received,
				SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted,
				SUM(CASE WHEN Lead_Status = 8 THEN 1 ELSE 0 END) AS Total_Lost
			");
			$oCurrentQuery->whereBetween('Lead_Date', [$pDate_Start, $pDate_End]);


			$oPreviusQuery = DB::query();
			$oPreviusQuery->from('v_leads');
			$oPreviusQuery->selectRaw("
				Id_UserCountry,
				Lead_Date_Year AS Year,
				Lead_Date_Month AS Month,
				Lead_Date_Day AS Day,
				COUNT(*) AS Total_Received,
				SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted,
				SUM(CASE WHEN Lead_Status = 8 THEN 1 ELSE 0 END) AS Total_Lost
			");
			$oPreviusQuery->whereBetween('Lead_Date', [$pDate_Start_YoY, $pDate_End_YoY]);


			if ($pView == "RANKING")
			{
				$oCurrentQuery->groupBy("Id_UserCountry");
				$oPreviusQuery->groupBy("Id_UserCountry");


				$oParam		= "
					uc.Id_UserCountry AS Id,
					uc.UserCountry_Name as Label,
					uc.UserCountry_Name AS Title,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_user_country AS uc", "uc.Id_UserCountry", "=", "cur.Id_UserCountry");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_UserCountry",
					"=",
					"cur.Id_UserCountry"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->orderBy('Rank_Current');

				$oData = $oQuery->get();
			}
			else  if ($pView == "SPECIFIC")
			{
				$oCurrentQuery->groupBy("Id_UserCountry", "Lead_Date_Year", "Lead_Date_Month");
				$oPreviusQuery->groupBy("Id_UserCountry", "Lead_Date_Year", "Lead_Date_Month");


				$oParam		= "
					uc.Id_UserCountry AS Id,
					uc.UserCountry_Name as Label,
					uc.UserCountry_Name AS Title,
					cur.Year,
					cur.Month,
					cur.Day,
				";


				$oQuery 		= DB::query();

				$oQuery->fromSub($oCurrentQuery, "cur");
				$oQuery->join("t_user_country AS uc", "uc.Id_UserCountry", "=", "cur.Id_UserCountry");
				$oQuery->leftJoinSub(
					$oPreviusQuery,
					"pre",
					"pre.Id_UserCountry",
					"=",
					"cur.Id_UserCountry"
				);
				$oQuery->selectRaw( $this->GetSelect($oParam) );
				$oQuery->whereIn("cur.Id_UserCountry", $pId );
				$oQuery->groupBy("cur.Id_UserCountry", "cur.Year", "cur.Month", "cur.Day");
				$oQuery->orderBy("cur.Id_UserCountry", "ASC");
				$oQuery->orderBy("cur.Year", "ASC");
				$oQuery->orderBy("cur.Month", "ASC");
				$oQuery->orderBy("cur.Day", "ASC");

				$oData = $oQuery->get();
			}

		}


		//------------------------------------------------------------------------------
		//	MAP RESULT
		//------------------------------------------------------------------------------
		if ($pGroup != "NONE" && $pView == "SPECIFIC")
		{
			$oMonths 	= [];
			$pCursor 	= strtotime(date('Y-m-01', strtotime($pDate_Start)));
			$pEnd 		= strtotime(date('Y-m-01', strtotime($pDate_End)));

			while ($pCursor <= $pEnd)
			{
				$oMonths[] = [
					'Year'  => (int)date('Y', $pCursor),
					'Month' => (int)date('n', $pCursor),
					'Day' 	=> (int)date('d', $pCursor)
				];

				$pCursor = strtotime('+1 day', $pCursor);
			}


			$oData = $oData
					->groupBy("Id")
					->map(function ($oRows, $oId) use ($oMonths)
						{
							$oMap = [];
							foreach ($oRows as $oItem) {
								$key = $oItem->Year . '-' . $oItem->Month . '-' . $oItem->Day;
								$oMap[$key] = [
									"Total_Received"     			=> $oItem->Total_Received,
									"Total_Converted"  				=> $oItem->Total_Converted,
									"Total_Lost"					=> $oItem->Total_Lost,
									"Previous_Total_Received"		=> $oItem->Previous_Total_Received,
									"Previous_Total_Converted"		=> $oItem->Previous_Total_Converted,
									"Previous_Total_Lost"			=> $oItem->Previous_Total_Lost,
									"YoY_Received"					=> $oItem->YoY_Received,
									"YoY_Converted"					=> $oItem->YoY_Converted,
									"YoY_Lost"						=> $oItem->YoY_Lost,
									"Rate_Converted"				=> $oItem->Rate_Converted
								];
							}

							$oDetail = [];
							foreach ($oMonths as $oItem)
							{
								$key = $oItem['Year'].'-'.$oItem['Month'].'-'.$oItem['Day'];

								$oDetail[] = [
									"Year"       					=> $oItem['Year'],
									"Month"      					=> $oItem['Month'],
									"Day"      						=> $oItem['Day'],
									"Total_Received"				=> isset($oMap[$key]) ? $oMap[$key]["Total_Received"] : 0,
									"Total_Converted"				=> isset($oMap[$key]) ? $oMap[$key]["Total_Converted"] : 0,
									"Total_Lost"					=> isset($oMap[$key]) ? $oMap[$key]["Total_Lost"] : 0,
									"Previous_Total_Received"		=> isset($oMap[$key]) ? $oMap[$key]["Previous_Total_Received"] : 0,
									"Previous_Total_Converted"		=> isset($oMap[$key]) ? $oMap[$key]["Previous_Total_Converted"] : 0,
									"Previous_Total_Lost"			=> isset($oMap[$key]) ? $oMap[$key]["Previous_Total_Lost"] : 0,
									"YoY_Received"					=> isset($oMap[$key]) ? $oMap[$key]["YoY_Received"] : 0,
									"YoY_Converted"					=> isset($oMap[$key]) ? $oMap[$key]["YoY_Converted"] : 0,
									"YoY_Lost"						=> isset($oMap[$key]) ? $oMap[$key]["YoY_Lost"] : 0,
									"Rate_Converted"				=> isset($oMap[$key]) ? $oMap[$key]["Rate_Converted"] : 0
								];
							}

							$oMain = $oRows->first();

							return [
								"Id"		=> $oId,
								"Label"		=> $oMain->Label,
								"Title"		=> $oMain->Title,
								"Detail"	=> $oDetail
							];
						})
					->values();
		}


		//------------------------------------------------------------------------------
		//	RESPONSE
		//------------------------------------------------------------------------------
		if ( $pExport == false )
		{
			$oResponse["Response_Status"]           = 200;
			$oResponse["Response_Code"]             = 200;
			$oResponse["Response_Domain"]           = $this->oDomain;
			$oResponse["Response_Message"]          = $this->oDomain . " List";
			$oResponse["Response_Data"]             = $oData;
			$oResponse["Response_Error_Message"]    = "";
			$oResponse["Response_Error_Reason"]     = "";

			return ResponseBuilder::Response($oResponse);
		}
		else
		{
			if ($pGroup == "NONE")
			{
				AnalyticLeadReport::Report_Detailed($oData->toArray());
			}
			else
			{
				if ($pView == "RANKING")
				{
					AnalyticLeadReport::Report_Detailed_Ranking($oData->toArray());
				}
				else
				{
					AnalyticLeadReport::Report_Detailed_Specific($oData->toArray());
				}
			}
		}

    }




	private function GetSelect($Params)
	{
		$oSelect = "";

		$oSelect = $Params;
		$oSelect .= "
			cur.Total_Received,
			cur.Total_Converted,
			cur.Total_Lost,

			IFNULL(pre.Total_Received, 0) AS Previous_Total_Received,
			IFNULL(pre.Total_Converted, 0) AS Previous_Total_Converted,
			IFNULL(pre.Total_Lost, 0) AS Previous_Total_Lost,

			RANK() OVER (ORDER BY IFNULL(cur.Total_Received, 0) DESC) AS Rank_Current,
			RANK() OVER (ORDER BY IFNULL(pre.Total_Received, 0) DESC) AS Rank_Previous,
			( RANK() OVER (ORDER BY IFNULL(pre.Total_Received, 0) DESC) - RANK() OVER (ORDER BY cur.Total_Received DESC) ) AS Rank_Change,

			CASE
				WHEN IFNULL(pre.Total_Received, 0) = 0
				THEN 0
				ELSE (cur.Total_Received - pre.Total_Received) / pre.Total_Received
			END AS YoY_Received,
			CASE
				WHEN IFNULL(pre.Total_Converted, 0) = 0
				THEN 0
				ELSE (cur.Total_Converted - pre.Total_Converted) / pre.Total_Converted
			END AS YoY_Converted,
			CASE
				WHEN IFNULL(pre.Total_Lost, 0) = 0
				THEN 0
				ELSE (cur.Total_Lost - pre.Total_Lost) / pre.Total_Lost
			END AS YoY_Lost,

			CASE
				WHEN IFNULL(cur.Total_Converted, 0) = 0
				THEN 0
				ELSE ((cur.Total_Converted * 100.0) / cur.Total_Received) / 100
			END AS Rate_Converted,
			CASE
				WHEN IFNULL(cur.Total_Lost, 0) = 0
				THEN 0
				ELSE ((cur.Total_Lost * 100.0) / cur.Total_Received) / 100
			END AS Rate_Lost
		";

		return $oSelect;
	}


}