<?php

declare(strict_types=1);

namespace Plugin\tnt_stabila_shop\src\Repositories;

use DateTime;
use JTL\Backend\Notification;
use JTL\DB\DbInterface;
use Plugin\tnt_stabila_shop\src\DomainObjects\PerformanceDTO;
use Plugin\tnt_stabila_shop\src\DomainObjects\StabilaDataDTO;
use Plugin\tnt_stabila_shop\src\Enums\SettingType;
use Plugin\tnt_stabila_shop\src\Models\Events;
use Plugin\tnt_stabila_shop\src\Models\Notifications;
use Plugin\tnt_stabila_shop\src\Models\Performance;
use Plugin\tnt_stabila_shop\src\Models\SalesChannel;
use Plugin\tnt_stabila_shop\src\Models\Settings;
use Sentry\Event;
use Sentry\Frame;
use Sentry\Stacktrace;
use Symfony\Component\VarDumper\Cloner\VarCloner;
use Symfony\Component\VarDumper\Dumper\HtmlDumper;

/**
 * @phpstan-type SettingValue int|float|string|object|list<string>
 */
class DatabaseRepository
{
    protected const EVENT_STORAGE_LIMIT = 1000;

    /**
     * @param DbInterface $db
     */
    public function __construct(protected DbInterface $db)
    {
    }

    public function getEventsTableName(): string
    {
        return Events::getTable();
    }

    public function getPerformanceTableName(): string
    {
        return Performance::getTable();
    }

    public function getSalesChannelTableName(): string
    {
        return SalesChannel::getTable();
    }

    public function getSettingsTableName(): string
    {
        return Settings::getTable();
    }

    public function getNotificationsTableName(): string
    {
        return Notifications::getTable();
    }

    public function getLastEvent(): StabilaDataDTO|null
    {
        return StabilaDataDTO::initFromTableData(
            $this->db->getSingleObject(
                'SELECT *
                FROM ' . $this->getEventsTableName() . '
                ORDER BY id DESC
                LIMIT 1',
            )
        );
    }

    /**
     * Get paginated records from the database.
     *
     * @param int $limit The max number of results.
     * @return StabilaDataDTO[]
     */
    public function getLastEvents(int $limit): array
    {
        $events = [];
        foreach (
            $this->db->getObjects(
                'SELECT *
                FROM ' . $this->getEventsTableName() . '
                ORDER BY id DESC
                LIMIT :limit',
                ['limit' => $limit]
            ) as $record
        ) {
            $event = StabilaDataDTO::initFromTableData($record);
            if ($event instanceof StabilaDataDTO) {
                $events[] = $event;
            }
        }

        return $events;
    }

    public function getEventByID(int $id): StabilaDataDTO|null
    {
        return StabilaDataDTO::initFromTableData(
            $this->db->getSingleObject(
                'SELECT *
                FROM ' . $this->getEventsTableName() . '
                WHERE id = :id
                ORDER BY id DESC
                LIMIT 1',
                ['id' => $id]
            )
        );
    }

    /**
     * Get paginated records from the database.
     *
     * @param int $offset The starting index for pagination.
     * @param int $limit The total number of results.
     * @return StabilaDataDTO[]
     */
    public function getPaginatedEvents(int $offset, int $limit): array
    {
        $events = [];
        foreach (
            $this->db->getObjects(
                'SELECT *
                FROM ' . $this->getEventsTableName() . '
                ORDER BY id DESC
                LIMIT :offset, :limit',
                ['offset' => $offset, 'limit' => $limit]
            ) as $record
        ) {
            $event = StabilaDataDTO::initFromTableData($record);
            if ($event instanceof StabilaDataDTO) {
                $events[] = $event;
            }
        }

        return $events;
    }

    public function deleteEventByID(int $id): bool
    {
        return $this->db->executeQueryPrepared(
            'DELETE
            FROM ' . $this->getEventsTableName() . '
            WHERE BY id = :id',
            ['id' => $id]
        ) > 0;
    }

    public function tidyUpEventsTable(): bool
    {
        $totalRowsInTable = $this->db->getSingleInt(
            'SELECT COUNT(*) AS cnt
            FROM ' . $this->getEventsTableName(),
            'cnt'
        );
        if ($totalRowsInTable <= self::EVENT_STORAGE_LIMIT) {
            return true;
        }

        return $this->db->executeQueryPrepared(
            'DELETE
            FROM ' . $this->getEventsTableName() . '
            ORDER BY id DESC
            LIMIT :rowsToPreserve, :limit',
            [
                'rowsToPreserve' => self::EVENT_STORAGE_LIMIT,
                'limit'          => $totalRowsInTable - self::EVENT_STORAGE_LIMIT
            ]
        ) > 0;
    }

    /**
     * @param PerformanceDTO[] $performanceData
     */
    public function savePerformanceData(array $performanceData): void
    {
        if ($performanceData === []) {
            return;
        }
        $this->db->insertBatch(
            $this->getPerformanceTableName(),
            \array_map(
                static fn (PerformanceDTO $data): object => (object)[
                    'pageType'                => $data->pageType->value,
                    'totalExecutionInSeconds' => $data->totalExecutionInSeconds,
                    'createDate'              => $data->createDateTime->format('Y-m-d H:i:s'),
                ],
                $performanceData
            )
        );
    }

    public function tidyUpPerformanceTable(DateTime $maxDateToPreserve): bool
    {
        return $this->db->executeQueryPrepared(
            'DELETE
            FROM ' . $this->getPerformanceTableName() . '
            WHERE createDate < :maxDateTpPreserve',
            ['maxDateToPreserve' => $maxDateToPreserve->format('Y-m-d H:i:s')]
        ) > 0;
    }

    public function saveEvent(Event $event, string $userIP): bool
    {
        $exception = $event->getExceptions()[0] ?? null;
        if ($exception === null) {
            return false;
        }
        try {
            $extra = \json_encode($event->getExtra(), JSON_THROW_ON_ERROR);
        } catch (\Throwable) {
            $extra = '{}';
        }
        $exceptionFrames = $exception->getStacktrace()?->getFrames() ?? [];
        $lastFrame       = \end($exceptionFrames);

        $tags         = $event->getTags();
        $filePath     = $lastFrame instanceof Frame ? $lastFrame->getFile() : '';
        $functionName = $lastFrame instanceof Frame ? $lastFrame->getFunctionName() : '';
        $template     = $tags['activeTemplate'] ?? '';
        $shopVersion  = $tags['shopVersion'] ?? '';
        $severity     = $tags['severity'] ?? '';
        $hash         = \md5(
            $exception->getType()
            . $event->getSdkVersion()
            . $filePath
            . $functionName
            . $template
            . $shopVersion
            . $exception->getValue()
            . $severity
            . $userIP
        );
        if (
            $this->db->getSingleObject(
                'SELECT id FROM ' . $this->getEventsTableName() . '
                WHERE hash = :hash
                LIMIT 1',
                ['hash' => $hash]
            ) !== null
        ) {
            return false;
        }
        $exceptions = $event->getExceptions();
        foreach ($exceptions as $sentryException) {
            $newStackTrace = new Stacktrace(
                \array_reverse(
                    $sentryException->getStacktrace()?->getFrames() ?? []
                )
            );
            $sentryException->setStacktrace($newStackTrace);
        }

        $stackTraceDump = (new HtmlDumper())->dump(
            (new VarCloner())->cloneVar($event->getExceptions()),
            true
        );

        return $this->db->insertRow(
            $this->getEventsTableName(),
            (object)[
                'exceptionType' => $exception->getType(),
                'sdkVersion'    => $event->getSdkVersion(),
                'extra'         => $extra,
                'filePath'      => $filePath,
                'functionName'  => $functionName,
                'template'      => $template,
                'shopVersion'   => $shopVersion,
                'severity'      => $severity,
                'message'       => $exception->getValue(),
                'varDump'       => $stackTraceDump,
                'hash'          => $hash,
            ]
        ) > 0;
    }

    public function getTotalCustomers(): int
    {
        return $this->db->getSingleInt(
            'SELECT COUNT(*) AS cnt
            FROM tkunde',
            'cnt'
        );
    }

    public function getTotalCustomersXLastDays(int $days): int
    {
        return $this->db->getSingleInt(
            'SELECT COUNT(*) AS cnt
            FROM tkunde
            WHERE dErstellt > DATE_SUB(NOW(), INTERVAL :days DAY)',
            'cnt',
            ['days' => $days]
        );
    }

    /**
     * @return list<object{returnValue: int}>
     */
    public function getMonthlyCustomerTotals(): array
    {
        $totals = $this->db->getObjects(
            'SELECT COUNT(*) AS returnValue
            FROM tkunde
            WHERE dErstellt > DATE_SUB(NOW(), INTERVAL 2 Year)
            GROUP BY DATE_FORMAT(dErstellt, "%Y-%m")
            ORDER BY dErstellt',
        );

        /** @var list<\stdClass&object{returnValue: int}> $totals */
        return $totals;
    }

    public function getTotalOrders(): int
    {
        return $this->db->getSingleInt(
            'SELECT COUNT(*) AS cnt
            FROM tbestellung',
            'cnt'
        );
    }

    public function getTotalOrdersXLastDays(int $days): int
    {
        return $this->db->getSingleInt(
            'SELECT COUNT(*) AS cnt
            FROM tbestellung
            WHERE dErstellt > DATE_SUB(NOW(), INTERVAL :days DAY)',
            'cnt',
            ['days' => $days]
        );
    }

    /**
     * @return list<object{returnValue: int|float}>
     */
    public function getMonthlyOrderTotalAverage(): array
    {
        $totals = $this->db->getObjects(
            'SELECT SUM(fGesamtsumme) AS returnValue
            FROM tbestellung
            WHERE dErstellt > DATE_SUB(NOW(), INTERVAL 2 Year) AND dErstellt < :endDate
            GROUP BY DATE_FORMAT(dErstellt, "%Y-%m")
            ORDER BY dErstellt',
            ['endDate' => date('Y-m-01 00:00:00')]
        );

        /** @var list<\stdClass&object{returnValue: int|float}> $totals */
        return $totals;
    }

    public function getOrdersTotalThisMonth(): float
    {
        return (float)($this->db->getSingleObject(
            'SELECT SUM(fGesamtsumme) AS amount
            FROM tbestellung
            WHERE dErstellt >= :startDate',
            ['startDate' => date('Y-m-01 00:00:00')]
        )->amount ?? 0.0);
    }

    /**
     * @return list<object{returnValue: int}>
     */
    public function getMonthlyOrderTotals(): array
    {
        $totals = $this->db->getObjects(
            'SELECT COUNT(*) AS returnValue
            FROM tbestellung
            WHERE dErstellt > DATE_SUB(NOW(), INTERVAL 2 Year)
            GROUP BY DATE_FORMAT(dErstellt, "%Y-%m")
            ORDER BY dErstellt',
        );

        /** @var list<\stdClass&object{returnValue: int}> $totals */
        return $totals;
    }

    public function getErrorsLastXDays(int $days): int
    {
        return $this->db->getSingleInt(
            'SELECT COUNT(*) AS cnt
            FROM ' . $this->getEventsTableName() . '
            WHERE createDate > DATE_SUB(NOW(), INTERVAL :days DAY)',
            'cnt',
            ['days' => $days]
        );
    }

    /**
     * @return list<object{returnValue: int}>
     */
    public function getWeeklyErrorTotals(): array
    {
        $totals = $this->db->getObjects(
            'SELECT COUNT(*) AS returnValue
            FROM ' . $this->getEventsTableName() . '
            WHERE createDate > DATE_SUB(NOW(), INTERVAL 2 Year)
            GROUP BY DATE_FORMAT(createDate, "%Y-%u")
            ORDER BY createDate',
        );

        /** @var list<\stdClass&object{returnValue: int}> $totals */
        return $totals;
    }

    public function getPerformanceLastXDays(int $days): float
    {
        return (float)($this->db->getSingleObject(
            'SELECT AVG(totalExecutionInSeconds) AS performance
            FROM ' . $this->getPerformanceTableName() . '
            WHERE createDate > DATE_SUB(NOW(), INTERVAL :days DAY)',
            ['days' => $days]
        )->performance ?? 0.0);
    }

    /**
     * @return list<object{returnValue: int|float}>
     */
    public function getWeeklyPerformanceTotals(): array
    {
        $totals = $this->db->getObjects(
            'SELECT AVG(totalExecutionInSeconds) AS returnValue
            FROM ' . $this->getPerformanceTableName() . '
            WHERE createDate > DATE_SUB(NOW(), INTERVAL 2 Year)
            GROUP BY DATE_FORMAT(createDate, "%Y-%u")
            ORDER BY createDate',
        );

        /** @var list<\stdClass&object{returnValue: int|float}> $totals */
        return $totals;
    }

    /**
     * Get average performance grouped by page type for the last X days.
     *
     * @param int $days The number of days to look back.
     * @return object{performance: float, pageType: int}[]
     */
    public function getPerformanceWithPageTypeLastXDays(int $days): array
    {
        $performances = $this->db->getObjects(
            'SELECT AVG(totalExecutionInSeconds) AS performance, pageType
            FROM ' . $this->getPerformanceTableName() . '
            WHERE createDate > DATE_SUB(NOW(), INTERVAL :days DAY)
            GROUP BY pageType',
            ['days' => $days]
        );

        /** @var array<\stdClass&object{performance: float, pageType: int}> $performances */
        return $performances;
    }

    /**
     * @param object{orderID: int, referrer: string, totalOrderValue: float} $data
     */
    public function saveOrderData(object $data): void
    {
        if ($data->orderID <= 0 || $data->referrer === '') {
            return;
        }
        $this->db->insertRow($this->getSalesChannelTableName(), $data);
    }

    public function update(string $table, string $column, string $value, object $data): bool
    {
        return $this->db->update(
            $table,
            $column,
            $value,
            $data
        ) !== -1;
    }

    /**
     * @return SettingValue|null
     */
    public function getSetting(string $settingName): int|float|string|object|array|null
    {
        $dbRow = $this->db->getSingleObject(
            'SELECT settingType, settingValue FROM ' . $this->getSettingsTableName()
            . ' WHERE settingName = :settingName',
            ['settingName' => $settingName]
        );
        if ($dbRow === null) {
            return null;
        }

        $settingValue = $dbRow->settingValue;

        return match (SettingType::fromValue((int)$dbRow->settingType)) {
            SettingType::INT    => \is_numeric($settingValue) ? (int)$settingValue : 0,
            SettingType::FLOAT  => \is_numeric($settingValue) ? (float)$settingValue : 0.0,
            SettingType::STRING => \is_scalar($settingValue) ? (string)$settingValue : '',
            SettingType::OBJECT => \is_string($settingValue)
                ? (\unserialize($settingValue, ['allowed_classes' => true]) ?: new \stdClass())
                : new \stdClass(),
            SettingType::LIST   => \is_string($settingValue) ? \explode('||', $settingValue) : [],
            default => null
        };
    }

    /**
     * @param object{name: string, type: string, defaultValue: string} $setting
     * @param SettingValue $value
     */
    public function setSetting(object $setting, int|float|string|object|array $value): void
    {
        // upsert (not updateRow): settingName is UNIQUE, so a setting whose row was
        // never seeded still persists on first save. A plain UPDATE silently no-ops
        // for a missing row — that was the dead-"Speichern" bug (Migration20260730210000).
        $this->db->upsert(
            $this->getSettingsTableName(),
            (object)[
                'settingName'  => $setting->name,
                'settingType'  => $setting->type,
                'settingValue' => match (SettingType::fromValue((int)$setting->type)) {
                    SettingType::INT    => \is_numeric($value) ? (int)$value : 0,
                    SettingType::FLOAT  => \is_numeric($value) ? (float)$value : 0.0,
                    SettingType::STRING => \is_scalar($value) ? (string)$value : '',
                    SettingType::OBJECT => \serialize(\is_object($value) || \is_array($value) ? $value : new \stdClass()),
                    SettingType::LIST   => \implode('||', \is_array($value) ? \array_map('strval', $value) : []),
                    default => null
                }
            ],
        );
    }

    public function isSQLiteCacheActive(): bool
    {
        $cacheMethod  = $this->db->getSingleObject(
            'SELECT cWert
            FROM teinstellungen
            WHERE cName = :cName',
            ['cName' => 'caching_method'],
        );
        $cacheEnabled = $this->db->getSingleObject(
            'SELECT cWert
            FROM teinstellungen
            WHERE cName = :cName',
            ['cName' => 'caching_activated'],
        );

        $cacheMethodValue  = \is_object($cacheMethod) && isset($cacheMethod->cWert)
            ? (string)$cacheMethod->cWert
            : '';
        $cacheEnabledValue = \is_object($cacheEnabled) && isset($cacheEnabled->cWert)
            ? (string)$cacheEnabled->cWert
            : '';

        return $cacheMethodValue === 'sqlite'
            && $cacheEnabledValue === 'Y';
    }

    public function getTotalPages(string $query, int $pageSize): int
    {
        $totalRows = $this->db->getSingleInt(
            $query,
            'cnt',
        );
        $result    = (int)($totalRows / $pageSize);
        if (($totalRows % $pageSize) > 0) {
            $result++;
        }

        return $result;
    }

    /**
     * Get paginated results from the database.
     *
     * @param string $query The SQL query to execute.
     * @return object[]
     */
    public function getPaginatedResults(string $query): array
    {
        return $this->db->getObjects($query);
    }

    /**
     * Get error details by ID.
     *
     * @return object{id: int, exceptionType: string, sdkVersion: string, filePath: string, functionName: string,
     *      template: string, shopVersion: string, severity: object{badgeType: string, value: string},
     *      message: string, varDump: string, hash: string, createDate: DateTime, reportLink: string,
     *      reportMaxDate: string, totalExecTime: float, shopLanguage: string, shopCurrency: string,
     *      cartCount: int, cartItems: string, deliveryCountry: string, invoiceCountry: string,
     *      taxRates: string[], userBrowser: string, userID: int}|null
     */
    public function getErrorDetails(int $id): object|null
    {
        $result = $this->db->getSingleObject(
            'SELECT *
            FROM ' . $this->getEventsTableName() . '
            WHERE id = :id',
            ['id' => $id]
        );

        return $result === null ? null : Events::transformData($result);
    }

    public function saveNotification(
        int $type,
        string $title,
        string $description,
        string|null $url = null,
        string|null $hash = null
    ): bool {
        // upsert (not insertRow): `hash` is the PRIMARY key and is a deterministic digest of
        // (type, description), so a recurring notification maps to the same row. A plain INSERT
        // raised a 1062 duplicate-key error that got logged on every repeat (e.g. on each
        // customer-address save). ON DUPLICATE KEY UPDATE refreshes createDate to the latest
        // occurrence instead of erroring.
        return $this->db->upsert(
            $this->getNotificationsTableName(),
            (object)[
                'type'        => $type,
                'title'       => $title,
                'description' => $description,
                'hash'        => $hash,
                'createDate'  => (new Datetime())->format('Y-m-d H:i:s'),
            ]
        ) >= 0;
    }

    public function addNotificationsToSession(
        int $type,
        string $title,
        string $description,
        string|null $url = null,
        string|null $hash = null
    ): void {
        Notification::getInstance($this->db)->add(
            $type,
            $title,
            $description,
            $url,
            $hash
        );
    }

    /**
     * Get notifications for a specific user.
     *
     * @return object{type: int, title: string, description: string,
     *     hash: string, createDate: DateTime}[]
     */
    public function getNotifications(): array
    {
        $result        = [];
        $notifications = $this->db->getObjects(
            'SELECT *
            FROM ' . $this->getNotificationsTableName() . '
            ORDER BY createDate DESC',
        );
        foreach ($notifications as $notification) {
            $notificationData = Notifications::transformData($notification);
            /** @var object{type: int, title: string, description: string, hash: string, createDate: DateTime} $notificationData */
            $result[] = $notificationData;
        }

        return $result;
    }
}
