<?php

namespace App\Modules\Report\Booking\Domain\LeadBySellerMonthly;

use App\Modules\Admin\Domain\AdminIsAssignable;
use App\Modules\Admin\Domain\AdminStatus;
use App\Modules\Shared\Domain\DateEnd;
use App\Modules\Shared\Domain\DateReportUtils;
use App\Modules\Shared\Domain\DateStart;
use App\Modules\Shared\Domain\DateUtils;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorAnd;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorBetween;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorEqual;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorIn;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorNotEqual;
use App\Modules\Shared\Domain\Storage\Criteria\StorageCriteria;
use Illuminate\Support\Facades\DB;
use function Lambdish\Phunctional\each;

class LeadBySellerMonthlyReport implements StorageCriteria
{

    public function __construct(
        private DateStart $dateStart,
        private DateEnd $dateEnd,
        private LeadBySellerMonthlyReportShowBy $showBy,
        private array $statuses,
        private array $admin
    ) {}

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

    private function generateSelectQuery(array $selectColumns)
    {
        return implode(",\n\t", array_map(fn($k, $v) => "$v AS `$k`", array_keys($selectColumns), array_values($selectColumns)));
    }

    private function isAdminEmpty()
    {
        return count($this->admin) == 0;
    }


    private function divideDatesByCurrentType()
    {
        if ($this->showBy->value() == LeadBySellerMonthlyReportShowBy::MONTH) {
            $dates = DateUtils::divideInMonths($this->dateStart->getValue(), $this->dateEnd->getValue());
            return DateReportUtils::cutByMonth($dates);
        } else {
            $dates = DateUtils::divideInYears($this->dateStart->getValue(), $this->dateEnd->getValue());
            return DateReportUtils::cutByYears($dates);
        }
    }

    private static function count($countableExpression, $title)
    {
        return 'COUNT(' . $countableExpression . ') AS `' . $title . '`';
    }

    private static function conditional($condition, $true, $false)
    {
        return 'IF(' . $condition . ', ' . $true . ', ' . $false . ')';
    }

    private static function filterDataByStatuses($data, $statuses)
    {
        $turn = [];
        foreach ($statuses as $status) {
            if (isset($data[$status])) {
                $turn[] = $data[$status];
            }
        }
        return $turn;
    }

    protected function query()
    {

        $dates = $this->divideDatesByCurrentType($this->dateStart->getValue(), $this->dateEnd->getValue());
        $firstDate = $dates[0]['dateStart'];
        $lastDate = $dates[count($dates) - 1]['dateEnd'];

        $q = new LeadBySellerMonthlyReportQuery();

        //  BOOKING
        $monthNames = ['Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre'];

        $status = fn($title) => self::conditional($title, '`aux`.`Id_WebContactMessage`', 'NULL');
        $select = new LeadBySellerMonthlySelect($this->showBy);

        // $select = $generic[$this->groupBy->value()];

        $data = [
            1 => ['method' => $select($status('`NEW`'), 'NEW'), 'index' => 'NEW'],
            2 => ['method' => $select($status('`PRE_BOOKING`'), 'PRE_BOOKING'), 'index' => 'PRE_BOOKING'],
            3 => ['method' => $select($status('`CONFIRMED`'), 'CONFIRMED'), 'index' => 'CONFIRMED'],
            4 => ['method' => $select($status('`CANCELED`'), 'CANCELED'), 'index' => 'CANCELED'],
            5 => ['method' => $select($status('`ASSOCIATED`'), 'ASSOCIATED'), 'index' => 'ASSOCIATED'],
            6 => ['method' => $select($status('`ASSIGNED`'), 'ASSIGNED'), 'index' => 'ASSIGNED'],
            7 => ['method' => $select($status('`ANSWERED`'), 'ANSWERED'), 'index' => 'ANSWERED'],
            8 => ['method' => $select($status('`LOST`'), 'LOST'), 'index' => 'LOST'],
            9 => ['method' => $select($status('`HOLD`'), 'HOLD'), 'index' => 'HOLD'],
            10 => ['method' => $select($status('`RECOVERED`'), 'RECOVERED'), 'index' => 'RECOVERED'],
        ];

        $data = $this->filterDataByStatuses($data, $this->statuses);

        DateReportUtils::eachMonth(
            $dates,
            fn($m, $y) => $q->second()->addColumns(array_reduce(
                $data,
                fn($last, $callback) => array_merge($last, $callback['method']($m, $y)),
                []
            ))
        );

        $firstSelect = $this->generateSelectQuery($q->first()->columns());
        $secondSelect = $this->generateSelectQuery($q->second()->columns());

        $q->first()->setWhere(
            new OperatorAnd(
                new OperatorBetween(
                    '`wcm`.`WebContactMessage_Date`',
                    DateUtils::formatDateTime($firstDate, DateUtils::START),
                    DateUtils::formatDateTime($lastDate, DateUtils::END)
                ),
                new OperatorNotEqual('`wcm`.`WebContactMessage_Status`', 0),
                new OperatorIn('`wcm`.`WebContactMessage_Status`', $this->statuses)
            )
        );
        $q->second()->setWhere(
            new OperatorAnd(
                new OperatorEqual('`a`.`Admin_Status`', AdminStatus::ACTIVE),
                new OperatorEqual('`a`.`Admin_IsAssignable`', AdminIsAssignable::ANSWER_YES)
            )
        );

        $headers = $this->generateHeaders($dates);
        $values = array_merge($q->first()->operator()->values(), $q->second()->operator()->values());

        $query = "SELECT $secondSelect
            FROM `t_admin` `a`
            LEFT JOIN (
                SELECT $firstSelect
                FROM `t_web_contact_message` `wcm`
                WHERE " . $q->first()->operator()->query() . "
            ) `aux` ON `a`.`Id_Admin` = `aux`.`Id_Admin`
            WHERE " . $q->second()->operator()->query() . "
            GROUP BY `a`.`Id_Admin`";

        return [
            'headers' => $headers,
            'data' => $this->presentData($data, DB::select($query, $values), $headers, $monthNames)
        ];
        return;
    }

    protected function presentData($keys, $data, $headers, $monthNames)
    {
        $response = [];

        $indexes = array_map(fn($item) => $item['index'], $keys);
        foreach ($data as $value) {
            foreach ($headers as $index => $header) {
                $response[$value->Admin_Name][$index] = (object)[];
                foreach ($indexes as $indexValue) {
                    $valueIndex = $indexValue . ' ' . $monthNames[$header['month'] - 1] . ' ' . $header['year'];
                    $response[$value->Admin_Name][$index]->$indexValue = $value->$valueIndex;
                }
            }
        }
        return $response;
    }

    protected function generateHeaders($dates)
    {
        $headers = [];
        foreach ($dates as $date) {
            $headers[] = [
                'month' => (int)$date['dateStart']->format('m'),
                'year' => (int)$date['dateStart']->format('Y')
            ];
        }
        return $headers;
    }

    protected function analize($data)
    {
        return $data;
    }
}



// SELECT  `a`.`Admin_Name` as `Admin_Name`,

//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `NEW diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 2, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `PRE_BOOKING diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 3, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `CONFIRMED diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 4, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `CANCELED diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 5, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `ASSOCIATED diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 6, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `ASSIGNED diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 7, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `ANSWERED diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 8, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `LOST diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 9, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `HOLD diciembre 2024`,
//         COUNT(IF(`aux`.`y` = 2024 AND `aux`.`m` = 12, IF(`aux`.`WebContactMessage_Status` = 10, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `RECOVERED diciembre 2024`,

//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `NEW enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `PRE_BOOKING enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `CONFIRMED enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `CANCELED enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `ASSOCIATED enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `ASSIGNED enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `ANSWERED enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `LOST enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `HOLD enero 2025`,
//         COUNT(IF(`aux`.`y` = 2025 AND `aux`.`m` = 1, IF(`aux`.`WebContactMessage_Status` = 1, `aux`.`Id_WebContactMessage`, NULL), NULL)) AS `RECOVERED enero 2025`
// FROM
// `t_admin` `a`
// LEFT JOIN (
//   SELECT  `wcm`.`Id_WebContactMessage` as `Id_WebContactMessage`,
//           `wcm`.`WebContactMessage_Status` as `WebContactMessage_Status`,
//           `wcm`.`WebContactMessage_Date` as `WebContactMessage_Date`,
//           `wcm`.`Id_Admin` as `Id_Admin`,
//           YEAR(`wcm`.`WebContactMessage_Date`) as `y`,
//           MONTH(`wcm`.`WebContactMessage_Date`) as `m`
//   FROM `t_web_contact_message` `wcm`
//   WHERE `wcm`.`Id_Admin` IN (1, 2, 3, 4)
//   AND `wcm`.`WebContactMessage_Date` BETWEEN '2024-12-01 00:00:00' AND '2025-01-31 00:00:00'
//   AND `wcm`.`WebContactMessage_Status` <> 0
// ) `aux` ON `a`.`Id_Admin` = `aux`.`Id_Admin`
// WHERE `a`.`Id_Admin` IN (1, 2, 3, 4)
// GROUP BY `a`.`Id_Admin`
