<?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\AnalyticLeadListMonthDTO;


class AnalyticLeadListMonthController extends Controller
{
	private $oDomain;


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


	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, AnalyticLeadListMonthDTO::class);


		//------------------------------------------------------------------------------
		//	VARIABLES
		//------------------------------------------------------------------------------
		$pDate_Start	= $oDTO->DateStart;
		$pDate_End		= $oDTO->DateEnd;
		$pGroup			= $oDTO->Group;		// NONE - CHANNEL - SELLER
		$pView			= $oDTO->View;		// RANKING - STACKED - SPECIFIC
		$pId			= $oDTO->Id;		// Array of IDS


		$pQuery_Select		= "";
		$pQuery_WhereIn		= [];
		$pQuery_InneJoin	= [];
		$pQuery_LeftJoin	= [];
		$pQuery_GroupBy		= "";
		$pQuery_OrderBy		= "";


		//------------------------------------------------------------------------------
		//	FUNCTION
		//------------------------------------------------------------------------------
		if ($pGroup == "NONE")
		{
			$pQuery_Select		= "Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
			$pQuery_GroupBy 	= [ "Lead_Date_Year", "Lead_Date_Month" ];
			$pQuery_OrderBy		= [ ["Year", "ASC"], ["Month", "ASC"] ];
		}
		else if ($pGroup == "CHANNEL")
		{
			if ($pView == "RANKING")
			{
				$pQuery_Select		= "Id_Channel as Id, WebChannel_Name as Name, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_web_channel", "t_web_channel.Id_WebChannel", "v_leads.Id_Channel"] ];
				$pQuery_GroupBy 	= [ "Id_Channel" ];
				$pQuery_OrderBy		= [ [ "Total_Lead", "DESC" ] ];
			}
			else if ($pView == "STACKED")
			{
				$pQuery_Select		= "Id_Channel as Id, WebChannel_Name as Name, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_web_channel", "t_web_channel.Id_WebChannel", "v_leads.Id_Channel"] ];
				$pQuery_GroupBy 	= [ "Id_Channel", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "Id_Channel", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
			else if ($pView == "SPECIFIC")
			{
				$pQuery_Select		= "Id_Channel as Id, WebChannel_Name as Name, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_web_channel", "t_web_channel.Id_WebChannel", "v_leads.Id_Channel"] ];
				$pQuery_WhereIn		= [ [ "Id_Channel", $pId ] ];
				$pQuery_GroupBy 	= [ "Id_Channel", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "Id_Channel", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
		}
		else if ($pGroup == "SELLER")
		{
			if ($pView == "RANKING")
			{
				$pQuery_Select		= "t_admin.Id_Admin as Id, t_admin.Admin_Name as Name, t_admin.Admin_LastName as LastName, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_admin", "t_admin.Id_Admin", "v_leads.Id_Admin"] ];
				$pQuery_GroupBy 	= [ "t_admin.Id_Admin" ];
				$pQuery_OrderBy		= [ [ "Total_Lead", "DESC" ] ];
			}
			else if ($pView == "STACKED")
			{
				$pQuery_Select		= "t_admin.Id_Admin as Id, t_admin.Admin_Name as Name, t_admin.Admin_LastName as LastName, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_admin", "t_admin.Id_Admin", "v_leads.Id_Admin"] ];
				$pQuery_GroupBy 	= [ "t_admin.Id_Admin", "t_admin.Admin_Name", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "t_admin.Admin_Name", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
			else if ($pView == "SPECIFIC")
			{
				$pQuery_Select		= "t_admin.Id_Admin as Id, t_admin.Admin_Name as Name, t_admin.Admin_LastName as LastName, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_WhereIn		= [ [ "t_admin.Id_Admin", $pId ] ];
				$pQuery_InneJoin	= [ ["t_admin", "t_admin.Id_Admin", "v_leads.Id_Admin"] ];
				$pQuery_GroupBy 	= [ "t_admin.Id_Admin", "t_admin.Admin_Name", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "t_admin.Admin_Name", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
		}
		else if ($pGroup == "LANGUAGE")
		{
			if ($pView == "RANKING")
			{
				$pQuery_Select		= "t_language.Id_Language as Id, t_language.Language_Name as Name, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_language", "t_language.Id_Language", "v_leads.Id_Language"] ];
				$pQuery_GroupBy 	= [ "t_language.Id_Language" ];
				$pQuery_OrderBy		= [ [ "Total_Lead", "DESC" ] ];
			}
			else if ($pView == "STACKED")
			{
				$pQuery_Select		= "t_language.Id_Language as Id, t_language.Language_Name as Name, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_language", "t_language.Id_Language", "v_leads.Id_Language"] ];
				$pQuery_GroupBy 	= [ "t_language.Id_Language", "t_language.Language_Name", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "t_language.Language_Name", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
			else if ($pView == "SPECIFIC")
			{
				$pQuery_Select		= "t_language.Id_Language as Id, t_language.Language_Name as Name, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_WhereIn		= [ [ "t_language.Id_Language", $pId ] ];
				$pQuery_InneJoin	= [ ["t_language", "t_language.Id_Language", "v_leads.Id_Language"] ];
				$pQuery_GroupBy 	= [ "t_language.Id_Language", "t_language.Language_Name", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "t_language.Language_Name", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
		}
		else if ($pGroup == "COUNTRY")
		{
			if ($pView == "RANKING")
			{
				$pQuery_Select		= "t_user_country.Id_UserCountry as Id, t_user_country.UserCountry_Name as Name, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_user_country", "t_user_country.Id_UserCountry", "v_leads.Id_Country"] ];
				$pQuery_GroupBy 	= [ "t_user_country.Id_UserCountry" ];
				$pQuery_OrderBy		= [ [ "Total_Lead", "DESC" ] ];
			}
			else if ($pView == "STACKED")
			{
				$pQuery_Select		= "t_user_country.Id_UserCountry as Id, t_user_country.UserCountry_Name as Name, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_InneJoin	= [ ["t_user_country", "t_user_country.Id_UserCountry", "v_leads.Id_Country"] ];
				$pQuery_GroupBy 	= [ "t_user_country.Id_UserCountry", "t_user_country.UserCountry_Name", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "t_user_country.UserCountry_Name", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
			else if ($pView == "SPECIFIC")
			{
				$pQuery_Select		= "t_user_country.Id_UserCountry as Id, t_user_country.UserCountry_Name as Name, Lead_Date_Year AS Year, Lead_Date_Month AS Month, COUNT(*) AS Total_Lead, SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) AS Total_Converted, CASE WHEN COUNT(*) = 0 THEN 0 ELSE (SUM(CASE WHEN Lead_Status = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) END AS Rate_Converted";
				$pQuery_WhereIn		= [ [ "t_user_country.Id_UserCountry", $pId ] ];
				$pQuery_InneJoin	= [ ["t_user_country", "t_user_country.Id_UserCountry", "v_leads.Id_Country"] ];
				$pQuery_GroupBy 	= [ "t_user_country.Id_UserCountry", "t_user_country.UserCountry_Name", "Lead_Date_Year", "Lead_Date_Month" ];
				$pQuery_OrderBy		= [ [ "t_user_country.UserCountry_Name", "ASC" ], ["Year", "ASC"], ["Month", "ASC"] ];
			}
		}

		//------------------------------------------------------------------------------
		//	QUERY
		//------------------------------------------------------------------------------
		$oQuery = DB::query();


		$oQuery->from('v_leads');
		foreach ($pQuery_InneJoin as $oItem)
		{
			$oQuery->join($oItem[0], $oItem[1], $oItem[2]);
		}
		foreach ($pQuery_LeftJoin as $oItem)
		{
			$oQuery->leftJoin($oItem[0], $oItem[1], $oItem[2]);
		}
		$oQuery->selectRaw($pQuery_Select);
		$oQuery->whereBetween('Lead_Date', [$pDate_Start, $pDate_End]);
		foreach ($pQuery_WhereIn as $oItem)
		{
			$oQuery->whereIn($oItem[0], $oItem[1]);
		}
		foreach ($pQuery_GroupBy as $oItem)
		{
			$oQuery->groupBy($oItem);
		}
		foreach ($pQuery_OrderBy as $oItem)
		{
			$oQuery->orderBy($oItem[0], $oItem[1]);
		}

		$oData = $oQuery->get();



		//------------------------------------------------------------------------------
		//	MAP
		//------------------------------------------------------------------------------
		if ($pView == "STACKED" || $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)
				];

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



			if ($pGroup == "CHANNEL" || $pGroup == "SELLER" || $pGroup == "LANGUAGE" || $pGroup == "COUNTRY")
			{
				$oData = $oData
						->groupBy("Id")
						->map(function ($oRows, $oId) use ($oMonths)
							{
								$oMap = [];
								foreach ($oRows as $oItem) {
									$key = $oItem->Year . '-' . $oItem->Month;
									$oMap[$key] = [
										"Total_Lead"		=> $oItem->Total_Lead,
										"Total_Converted"	=> $oItem->Total_Converted,
										"Rate_Converted"	=> $oItem->Rate_Converted
									];
								}

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

									$oLead[] = [
										"Year"				=> $oItem['Year'],
										"Month"				=> $oItem['Month'],
										"Total_Lead"    	=> isset($oMap[$key]) ? $oMap[$key]['Total_Lead'] : 0,
										"Total_Converted" 	=> isset($oMap[$key]) ? $oMap[$key]['Total_Converted'] : 0,
										"Rate_Converted"	=> isset($oMap[$key]) ? $oMap[$key]['Rate_Converted'] : 0,
									];
								}

								$oMain = $oRows->first();

								return [
									"Id"		=> $oId,
									"Name"		=> $oMain->Name,
									"LastName"	=> $oMain->LastName ?? "",
									"Leads"		=> $oLead
								];
							})
						->values();
			}

		}


		//------------------------------------------------------------------------------
		//	RESPONSE
		//------------------------------------------------------------------------------
		$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);

    }


}