<?php

namespace App\Modules\WebContactMessage\Domain;

use App\Modules\Leads\WebChannel\Domain\WebChannel;
use App\Modules\Leads\WebPage\Domain\WebPage;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorAnd;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorLike;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorNotEqual;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorOr;
use App\Modules\Shared\Domain\Storage\Criteria\StorageCriteria;
use App\Modules\WebContact\Domain\WebContactModel;
use App\Modules\WebContactMessageDetail\Domain\WebContactMessageDetailModel;
use Illuminate\Support\Facades\DB;

class ListGeneralWebContactMessage implements StorageCriteria
{

    public function __construct(
        private string $search,
    ) {}

    public function run()
    {
        return $this->analize($this->query());
    }

    protected function query()
    {
        $filters = ['lead' => ['orderBy' => '`frm_wcm`.`WebContactMessage_Date`', 'orderByDirection' => 'DESC']];
        $dateByFilter = $filters['lead'];

        $operator = new OperatorAnd(
            new OperatorOr(
                new OperatorLike("CONCAT(`wcm`.`WebContactMessage_Name`, ' ', `wcm`.`WebContactMessage_LastName`)", '%' . $this->search . '%'),
                new OperatorLike('`wcm`.`WebContactMessage_Email`', $this->search . '%'),
                new OperatorLike('`wcm`.`WebContactMessage_Code`', $this->search . '%'),
                new OperatorLike("CONCAT(`wcm`.`WebContactMessage_PhoneCode`, ' ', `wcm`.`WebContactMessage_Phone`)", '%' . $this->search . '%'),
                new OperatorLike('`b`.`Booking_Code`', $this->search . '%'),
            ),
            new OperatorNotEqual('`wcm`.`WebContactMessage_Status`', 0)
        );

        $select = WebContactMessage::select('`wcm`');
        $wcmdSelect = WebContactMessageDetailModel::select('`wcmd`', ['Id_WebContactMessage', 'Id_Package']);
        $frmSelect = WebContactMessage::select('`frm_wcm`');
        $wcSelect = WebContactModel::select('`frm_wc`');
        $wchSelect = WebChannel::select('`frm_wch`');
        $wpSelect = WebPage::select('`frm_wp`', ['Id_Language']);

        return DB::select(
            "SELECT $frmSelect,
                    `frm_uc`.`Id_UserCountry`,
                    `frm_uc`.`UserCountry_Name`,
                    `frm_uc`.`UserCountry_Code`,
                    `frm_uc`.`UserCountry_Img`,
                    `frm_uc`.`UserCountry_Img` AS `UserCountry_Img_Thumb`,

                    (CONCAT(
                    '[',
                    GROUP_CONCAT(DISTINCT JSON_OBJECT(
                        'Id_WebContactMessageDetail', `frm_wcm`.`Id_WebContactMessageDetail`,
                        'WebContactMessageDetail_DateStart', `frm_wcm`.`WebContactMessageDetail_DateStart`,
                        'WebContactMessageDetail_Package', `frm_wcm`.`WebContactMessageDetail_Package`,
                        'WebContactMessageDetail_Status', `frm_wcm`.`WebContactMessageDetail_Status`,
                        'Id_Package', `frm_wcm`.`Id_Package`,
                        'Package_Name', `frm_wcm`.`Package_Name`,
                        'Package_Code', `frm_wcm`.`Package_Code`
                    ) SEPARATOR ','),
                    ']'
                    )) AS `WebContactMessageDetail`,

                    $wcSelect,
                    $wchSelect,
                    $wpSelect,

                    `frm_a`.`Id_Admin`,
                    `frm_a`.`Admin_Name`,
                    `frm_a`.`Admin_LastName`,
                    `frm_a`.`Admin_Abrv`,
                    `frm_a`.`Admin_Color`,
                    `frm_a`.`Admin_ImgProfile`,
                    `frm_a`.`Admin_ImgProfile_Thumb`,
                    `frm_ar`.`AdminRole_Name`,

                    `frm_a_a`.`Id_Admin` AS `Id_Admin_Assigner`,
                    `frm_a_a`.`Admin_Name` AS `Admin_Assigner_Name`,
                    `frm_a_a`.`Admin_LastName` AS `Admin_Assigner_LastName`,
                    `frm_a_a`.`Admin_Abrv` AS `Admin_Assigner_Abrv`,
                    `frm_a_a`.`Admin_Color` AS `Admin_Assigner_Color`,
                    `frm_a_a`.`Admin_ImgProfile` AS `Admin_Assigner_ImgProfile`,
                    `frm_a_a`.`Admin_ImgProfile_Thumb` AS `Admin_Assigner_ImgProfile_Thumb`,
                    `frm_ar_a`.`AdminRole_Name` AS `AdminRole_Assigner_Name`,

                    `b`.`Id_Booking`,
                    `b`.`Booking_Code`,
                    `b`.`Booking_Date`,
                    `b`.`Booking_DateConfirm`,
                    `b`.`Booking_Status`,

                    `wcm_c`.`Id_WebContactMessage` AS `Id_WebContactMessage_Connected`,
                    `wcm_c`.`WebContactMessage_Code` AS `WebContactMessage_Connected_Code`
            FROM (
                SELECT $select, $wcmdSelect,
                    `p`.`Id_Package`,
                    `p`.`Package_Name`,
                    `p`.`Package_Code`
                FROM `t_web_contact_message` `wcm`
                LEFT JOIN `t_web_contact_message_detail` `wcmd` ON `wcmd`.`Id_WebContactMessage` = `wcm`.`Id_WebContactMessage`
                    AND `WebContactMessageDetail_Status` = 2
                LEFT JOIN `t_package` `p` ON `p`.`Id_Package` = `wcmd`.`Id_Package`
                LEFT JOIN `t_package_tour` `pt` ON `pt`.`Id_Package` = `p`.`Id_Package`
                LEFT JOIN `t_booking` AS `b` ON `b`.`Id_Booking` = `wcm`.`Id_Booking`
                WHERE {$operator->query()}
            ) `frm_wcm`
            LEFT JOIN `t_web_contact` AS `frm_wc` ON `frm_wc`.`Id_WebContact` = `frm_wcm`.`Id_WebContact`
            LEFT JOIN `t_user_country` AS `frm_uc` ON `frm_uc`.`Id_UserCountry` = `frm_wcm`.`Id_Country`
            LEFT JOIN `t_web_channel` AS `frm_wch` ON `frm_wch`.`Id_WebChannel`=`frm_wcm`.`Id_Channel`
            LEFT JOIN `t_web_page` AS `frm_wp` ON `frm_wp`.`Id_WebPage`=`frm_wcm`.`Id_WebPage`
            LEFT JOIN `t_admin` AS `frm_a` ON `frm_a`.`Id_Admin` = `frm_wcm`.`Id_Admin`
            LEFT JOIN `t_admin_role` AS `frm_ar` ON `frm_ar`.`Id_AdminRole` = `frm_a`.`Id_AdminRole`
            LEFT JOIN `t_admin` AS `frm_a_a` ON `frm_a_a`.`Id_Admin` = `frm_wcm`.`Id_Admin_Assigner`
            LEFT JOIN `t_admin_role` AS `frm_ar_a` ON `frm_ar_a`.`Id_AdminRole` = `frm_a_a`.`Id_AdminRole`
            LEFT JOIN `t_package` AS `p` ON `p`.`Id_Package` = `frm_wcm`.`Id_Package`
            LEFT JOIN `t_booking` AS `b` ON `b`.`Id_Booking` = `frm_wcm`.`Id_Booking`
            LEFT JOIN `t_web_contact_message` AS `wcm_c` ON `wcm_c`.`Id_WebContactMessage` = `frm_wcm`.`Id_WebContactMessage_Connected`
            GROUP BY `frm_wcm`.`Id_WebContactMessage`
            ORDER BY {$dateByFilter['orderBy']} {$dateByFilter['orderByDirection']}, `frm_wcm`.`WebContactMessage_Date` DESC",
            $operator->values()
        );
    }

    protected function analize($data)
    {
        // return array_map(fn($dto) => WebContactMessage::fromDTO($dto), $data);
        return $data;
    }
}
