<?php

declare(strict_types=1);

namespace Esun\MultiLocaleTranslation\Model;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\DB\Adapter\AdapterInterface;

class CmsTranslationRepository
{
    public const ENTITY_PAGE = 'page';
    public const ENTITY_BLOCK = 'block';
    public const ENTITY_BLOG_POST = 'blog_post';
    public const ENTITY_WIKI_CATEGORY = 'wiki_category';
    public const ENTITY_WIKI_HOMEPAGE = 'wiki_homepage';

    private const TABLE_NAME = 'esun_localetranslation_cms';

    public function __construct(private readonly ResourceConnection $resource)
    {
    }

    public function getTranslations(int $entityId, int $storeId, string $entityType, array $fields = []): array
    {
        if (!$this->isSupportedType($entityType) || $entityId <= 0 || $storeId <= 0) {
            return [];
        }

        $connection = $this->getConnection();
        $select = $connection->select()
            ->from(
                $this->resource->getTableName(self::TABLE_NAME),
                ['field_name', 'translated_value']
            )->where('entity_type = ?', $entityType)
            ->where('entity_id = ?', $entityId)
            ->where('store_id = ?', $storeId);

        if ($fields !== []) {
            $select->where('field_name IN (?)', $fields);
        }

        return (array)$connection->fetchPairs($select);
    }

    public function saveTranslations(int $entityId, int $storeId, string $entityType, array $translations): void
    {
        if (!$this->isSupportedType($entityType) || $entityId <= 0 || $storeId <= 0) {
            return;
        }

        $connection = $this->getConnection();
        $tableName = $this->resource->getTableName(self::TABLE_NAME);
        $now = date('Y-m-d H:i:s');
        $rows = [];

        foreach ($translations as $field => $value) {
            if (!$field) {
                continue;
            }
            $rows[] = [
                'entity_type' => $entityType,
                'entity_id' => $entityId,
                'store_id' => $storeId,
                'field_name' => (string)$field,
                'translated_value' => is_string($value) ? $value : (string)$value,
                'created_at' => $now,
                'updated_at' => $now,
            ];
        }

        if ($rows === []) {
            return;
        }

        $connection->insertOnDuplicate(
            $tableName,
            $rows,
            ['translated_value', 'updated_at']
        );
    }

    /** @return array<int, array<string, string>> */
    public function getTranslationsForEntities(
        array $entityIds,
        int $storeId,
        string $entityType,
        array $fields = []
    ): array {
        $entityIds = array_values(array_unique(array_filter(array_map('intval', $entityIds))));
        if (!$this->isSupportedType($entityType) || $entityIds === [] || $storeId <= 0) {
            return [];
        }

        $select = $this->getConnection()->select()
            ->from(
                $this->resource->getTableName(self::TABLE_NAME),
                ['entity_id', 'field_name', 'translated_value']
            )->where('entity_type = ?', $entityType)
            ->where('entity_id IN (?)', $entityIds)
            ->where('store_id = ?', $storeId);
        if ($fields !== []) {
            $select->where('field_name IN (?)', $fields);
        }

        $result = [];
        foreach ($this->getConnection()->fetchAll($select) as $row) {
            $result[(int)$row['entity_id']][(string)$row['field_name']] = (string)$row['translated_value'];
        }
        return $result;
    }

    /** @return array<int, int[]> */
    public function getTranslatedStoreIdsForEntities(array $entityIds, string $entityType): array
    {
        $entityIds = array_values(array_unique(array_filter(array_map('intval', $entityIds))));
        if (!$this->isSupportedType($entityType) || $entityIds === []) {
            return [];
        }

        $select = $this->getConnection()->select()
            ->from(
                $this->resource->getTableName(self::TABLE_NAME),
                ['entity_id', 'store_id']
            )->where('entity_type = ?', $entityType)
            ->where('entity_id IN (?)', $entityIds)
            ->group(['entity_id', 'store_id']);

        $result = [];
        foreach ($this->getConnection()->fetchAll($select) as $row) {
            $result[(int)$row['entity_id']][] = (int)$row['store_id'];
        }
        return $result;
    }

    public function clearTranslations(int $entityId, int $storeId, string $entityType): void
    {
        if (!$this->isSupportedType($entityType) || $entityId <= 0 || $storeId <= 0) {
            return;
        }

        $connection = $this->getConnection();
        $connection->delete(
            $this->resource->getTableName(self::TABLE_NAME),
            [
                'entity_type = ?' => $entityType,
                'entity_id = ?' => $entityId,
                'store_id = ?' => $storeId
            ]
        );
    }

    public function clearEntityTranslations(int $entityId, string $entityType): void
    {
        if (!$this->isSupportedType($entityType) || $entityId <= 0) {
            return;
        }

        $this->getConnection()->delete(
            $this->resource->getTableName(self::TABLE_NAME),
            [
                'entity_type = ?' => $entityType,
                'entity_id = ?' => $entityId,
            ]
        );
    }

    public function deleteTranslations(
        int $entityId,
        int $storeId,
        string $entityType,
        array $fields
    ): void {
        $fields = array_values(array_filter(array_map('strval', $fields)));
        if (!$this->isSupportedType($entityType) || $entityId <= 0 || $storeId <= 0 || $fields === []) {
            return;
        }

        $connection = $this->getConnection();
        $connection->delete(
            $this->resource->getTableName(self::TABLE_NAME),
            [
                'entity_type = ?' => $entityType,
                'entity_id = ?' => $entityId,
                'store_id = ?' => $storeId,
                'field_name IN (?)' => $fields,
            ]
        );
    }

    private function isSupportedType(string $entityType): bool
    {
        return in_array($entityType, [
            self::ENTITY_PAGE,
            self::ENTITY_BLOCK,
            self::ENTITY_BLOG_POST,
            self::ENTITY_WIKI_CATEGORY,
            self::ENTITY_WIKI_HOMEPAGE,
        ], true);
    }

    private function getConnection(): AdapterInterface
    {
        return $this->resource->getConnection();
    }
}