<?php

declare(strict_types=1);

namespace Mautic\CoreBundle\Tests\Unit\Helper\Chart;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Query\QueryBuilder;
use Mautic\CoreBundle\Doctrine\GeneratedColumn\GeneratedColumn;
use Mautic\CoreBundle\Doctrine\GeneratedColumn\GeneratedColumns;
use Mautic\CoreBundle\Doctrine\Provider\GeneratedColumnsProviderInterface;
use Mautic\CoreBundle\Helper\Chart\ChartQuery;
use Mautic\CoreBundle\Helper\DateTimeHelper;
use PHPUnit\Framework\MockObject\MockObject;
use PHPUnit\Framework\TestCase;

class ChartQueryTest extends TestCase
{
    private \DateTime $dateFrom;
    private DateTimeHelper $dateTimeHelper;
    private \DateTime $dateTo;
    private MockObject&Connection $connection;
    private MockObject&QueryBuilder $queryBuilder;
    private string $dateColumn;
    private string $unit;
    private ChartQuery $chartQuery;

    protected function setUp(): void
    {
        parent::setUp();

        $this->dateFrom       = new \DateTime('2018-01-01 12:00:00');
        $this->dateTo         = new \DateTime('2018-02-01 12:00:00');
        $this->unit           = 'd';
        $this->dateColumn     = 'date_sent';
        $this->connection     = $this->createMock(Connection::class);
        $this->queryBuilder   = $this->createMock(QueryBuilder::class);
        $this->dateTimeHelper = new DateTimeHelper();

        $this->connection->method('createQueryBuilder')->willReturn($this->queryBuilder);
    }

    public function testClassicDateColumn(): void
    {
        $this->createChartQuery();

        $this->queryBuilder->expects($this->once())
            ->method('select')
            ->with('DATE_FORMAT(CONVERT_TZ(t.date_sent, \'+00:00\', \''.$this->dateTimeHelper->getLocalTimezoneOffset().'\'), \'%Y-%m-%d\') AS date, COUNT(*) AS count');

        $this->queryBuilder->expects($this->once())
            ->method('groupBy')
            ->with('DATE_FORMAT(CONVERT_TZ(t.date_sent, \'+00:00\', \''.$this->dateTimeHelper->getLocalTimezoneOffset().'\'), \'%Y-%m-%d\')');

        $this->queryBuilder->expects($this->once())
            ->method('orderBy')
            ->with('DATE_FORMAT(CONVERT_TZ(t.date_sent, \'+00:00\', \''.$this->dateTimeHelper->getLocalTimezoneOffset().'\'), \'%Y-%m-%d\')');

        $this->queryBuilder->expects($this->once())
            ->method('setMaxResults')
            ->with(32);

        $this->chartQuery->prepareTimeDataQuery('email_stats', $this->dateColumn);
    }

    public function testGeneratedDateColumn(): void
    {
        $this->createChartQuery();

        $generatedColumn          = new GeneratedColumn('email_stats', 'generated_sent_date', 'DATE', 'CONCAT(YEAR(date_sent), "-", LPAD(MONTH(date_sent), 2, "0"), "-", LPAD(DAY(date_sent), 2, "0"))');
        $generatedColumns         = new GeneratedColumns();
        $generatedColumnsProvider = $this->createMock(GeneratedColumnsProviderInterface::class);

        $generatedColumn->addIndexColumn('email_id');
        $generatedColumn->setFilterDateColumn('generated_sent_date');
        $generatedColumn->setOriginalDateColumn($this->dateColumn, $this->unit);
        $generatedColumns->add($generatedColumn);

        $generatedColumnsProvider->expects($this->exactly(2))
            ->method('getGeneratedColumns')
            ->willReturn($generatedColumns);

        $this->chartQuery->setGeneratedColumnProvider($generatedColumnsProvider);

        $this->queryBuilder->expects($this->once())
            ->method('select')
            ->with("DATE_FORMAT(CONVERT_TZ(t.date_sent, '+00:00', '+00:00'), '%Y-%m-%d') AS date, COUNT(*) AS count");

        $this->queryBuilder->expects($this->once())
            ->method('groupBy')
            ->with("DATE_FORMAT(CONVERT_TZ(t.date_sent, '+00:00', '+00:00'), '%Y-%m-%d')");

        $this->queryBuilder->expects($this->once())
            ->method('orderBy')
            ->with("DATE_FORMAT(CONVERT_TZ(t.date_sent, '+00:00', '+00:00'), '%Y-%m-%d')");

        $this->queryBuilder->method('getQueryPart')
            ->willReturnMap(
                [
                    ['from', [[
                        'table' => 'emails',
                        'alias' => 'e',
                    ]]],
                    [
                        'join',
                        [
                            'e' => [
                                [
                                    'joinType'      => 'inner',
                                    'joinTable'     => 'email_stats',
                                    'joinAlias'     => 't',
                                    'joinCondition' => 't.id = e.id',
                                ],
                            ],
                        ],
                    ],
                ]
            );

        $this->chartQuery->prepareTimeDataQuery('email_stats', $this->dateColumn);
    }

    public function testPhpOrderingInCompleteTimeDataHour(): void
    {
        $this->dateFrom = new \DateTime('2020-12-01 00:00:00.000000', new \DateTimeZone('UTC'));
        $this->dateTo   = new \DateTime('2020-12-02 13:31:55.492167', new \DateTimeZone('UTC'));
        $this->unit     = 'H';
        $expectedResult = [
            0  => 0,
            1  => 0,
            2  => 0,
            3  => 0,
            4  => 0,
            5  => 0,
            6  => 0,
            7  => 0,
            8  => 0,
            9  => 0,
            10 => 0,
            11 => 0,
            12 => 0,
            13 => 0,
            14 => 0,
            15 => 0,
            16 => 0,
            17 => 0,
            18 => 0,
            19 => 0,
            20 => 0,
            21 => 0,
            22 => 0,
            23 => 0,
            24 => 0,
            25 => 0,
            26 => 0,
            27 => 0,
            28 => 0,
            29 => 0,
            30 => 0,
            31 => 0,
            32 => '1',
            33 => '2',
            34 => 0,
            35 => '3',
            36 => 0,
            37 => 0,
        ];

        $rawData = [
            0 => [
                'count' => '1',
                'date'  => '2020-12-02 08:00',
            ],
            1 => [
                'count' => '2',
                'date'  => '2020-12-02 09:00',
            ],
            2 => [
                'count' => '3',
                'date'  => '2020-12-02 11:00',
            ],
        ];

        $this->assertTimeDataWithoutSqlOrder($expectedResult, $rawData);

        $rawData = [
            0 => [
                'count' => '3',
                'date'  => '2020-12-02 11:00',
            ],
            1 => [
                'count' => '2',
                'date'  => '2020-12-02 09:00',
            ],
            2 => [
                'count' => '1',
                'date'  => '2020-12-02 08:00',
            ],
        ];

        $this->assertTimeDataWithoutSqlOrder($expectedResult, $rawData);
    }

    public function testPhpOrderingInCompleteTimeDataDay(): void
    {
        $this->dateFrom = new \DateTime('2020-11-18 12:00:00');
        $this->dateTo   = new \DateTime('2020-12-02 12:00:00');
        $this->unit     = 'd';
        $expectedResult = [
            0  => 0,
            1  => 0,
            2  => 0,
            3  => 0,
            4  => 0,
            5  => 0,
            6  => 0,
            7  => 0,
            8  => 0,
            9  => 0,
            10 => 0,
            11 => '1',
            12 => '2',
            13 => 0,
            14 => '3',
        ];

        $rawData = [
            0 => [
                'count' => '1',
                'date'  => '2020-11-29',
            ],
            1 => [
                'count' => '2',
                'date'  => '2020-11-30',
            ],
            2 => [
                'count' => '3',
                'date'  => '2020-12-02',
            ],
        ];

        $this->assertTimeDataWithoutSqlOrder($expectedResult, $rawData);
    }

    public function testPhpOrderingInCompleteTimeDataWeek(): void
    {
        $this->dateFrom = new \DateTime('2020-10-31 12:00:00');
        $this->dateTo   = new \DateTime('2020-12-02 12:00:00');
        $this->unit     = 'W';
        $expectedResult = [
            0 => 0,
            1 => 0,
            2 => 0,
            3 => '2',
            4 => '1',
            5 => 0,
        ];

        $rawData = [
            0 => [
                'count' => '1',
                'date'  => '2020 48',
            ],
            1 => [
                'count' => '2',
                'date'  => '2020 47',
            ],
        ];

        $this->assertTimeDataWithoutSqlOrder($expectedResult, $rawData);

        $rawData = [
            0 => [
                'count' => '2',
                'date'  => '2020 47',
            ],
            1 => [
                'count' => '1',
                'date'  => '2020 48',
            ],
        ];

        $this->assertTimeDataWithoutSqlOrder($expectedResult, $rawData);
    }

    private function createChartQuery(): void
    {
        $this->chartQuery = new ChartQuery($this->connection, $this->dateFrom, $this->dateTo, $this->unit);
    }

    /**
     * @param array<mixed> $expectedResult
     * @param array<mixed> $data
     */
    private function assertTimeDataWithoutSqlOrder(array $expectedResult, array $data): void
    {
        $this->createChartQuery();
        self::assertSame(
            $expectedResult,
            $this->chartQuery->completeTimeData($data)
        );
    }

    public function testPrepareTimeDataQueryWithLeadEventLog(): void
    {
        $table   = 'lead_event_log';
        $column  = 'date_added';
        $filters = [
            'object'    => 'segment',
            'bundle'    => 'lead',
            'action'    => 'added',
            'object_id' => '1',
        ];

        $this->queryBuilder->expects($this->once())
            ->method('select')
            ->with('DATE_FORMAT(CONVERT_TZ(t.date_added, \'+00:00\', \''.$this->dateTimeHelper->getLocalTimezoneOffset().'\'), \'%Y-%m-%d\') AS date, COUNT(*) AS count');

        $this->queryBuilder->expects($this->once())
            ->method('from')
            ->with(MAUTIC_TABLE_PREFIX.'lead_event_log', 't');

        $this->queryBuilder->expects($this->once())
            ->method('groupBy')
            ->with('DATE_FORMAT(CONVERT_TZ(t.date_added, \'+00:00\', \''.$this->dateTimeHelper->getLocalTimezoneOffset().'\'), \'%Y-%m-%d\')');

        $this->queryBuilder->expects($this->once())
            ->method('orderBy')
            ->with('DATE_FORMAT(CONVERT_TZ(t.date_added, \'+00:00\', \''.$this->dateTimeHelper->getLocalTimezoneOffset().'\'), \'%Y-%m-%d\')');

        $this->queryBuilder->expects($this->once())
            ->method('setMaxResults')
            ->with(32);

        $this->createChartQuery();
        $query = $this->chartQuery->prepareTimeDataQuery($table, $column, $filters);
        $this->assertInstanceOf(QueryBuilder::class, $query);
    }
}
