<?php
declare(strict_types=1);

namespace App\Support;

use App\Core\Database;
use PDO;
use Throwable;

final class AreaContentStore
{
    private ?PDO $pdo;
    private TranslationService $translationService;

    public function __construct()
    {
        $this->pdo = (new Database())->connection();
        $this->translationService = new TranslationService();
    }

    public function all(string $languageCode = 'en', string $search = ''): array
    {
        if (!$this->pdo instanceof PDO) {
            return $this->fallbackRows($languageCode);
        }

        $this->ensureSeeded();

        $sql = 'SELECT ra.code AS area_code, ra.name, ra.color_hex,
                       COALESCE(fallback.description, "") AS description,
                       "en" AS language_code
                FROM riasec_areas ra
                LEFT JOIN riasec_area_content fallback
                    ON fallback.area_code = ra.code AND fallback.language_code = "en"
                WHERE 1 = 1';
        $params = [];

        if ($search !== '') {
            $sql .= ' AND (ra.name LIKE :search OR COALESCE(fallback.description, "") LIKE :search)';
            $params['search'] = '%' . $search . '%';
        }

        $sql .= ' ORDER BY ra.sort_order ASC';

        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);
        $rows = $statement->fetchAll() ?: [];

        foreach ($rows as &$row) {
            $row['name'] = \area_name((string) $row['area_code'], $languageCode);
            $row['description'] = $this->translationService->translate((string) ($row['description'] ?? ''), $languageCode);
        }

        return $rows;
    }

    public function find(string $areaCode): ?array
    {
        if (!$this->pdo instanceof PDO) {
            $fallback = config('riasec_areas.' . $areaCode, []);

            if ($fallback === []) {
                return null;
            }

            return [
                'area_code' => $areaCode,
                'language_code' => 'en',
                'name' => \area_name($areaCode, 'en'),
                'color_hex' => $fallback['color'],
                'description' => $fallback['description'],
            ];
        }

        $this->ensureSeeded();

        $statement = $this->pdo->prepare(
            'SELECT ra.code AS area_code, ra.name, ra.color_hex,
                          COALESCE(fallback.description, "") AS description,
                          "en" AS language_code
             FROM riasec_areas ra
             LEFT JOIN riasec_area_content fallback
                ON fallback.area_code = ra.code AND fallback.language_code = "en"
             WHERE ra.code = :area_code
             LIMIT 1'
        );
          $statement->execute(['area_code' => $areaCode]);
        $item = $statement->fetch();

        if (!is_array($item)) {
            return null;
        }

        $item['name'] = \area_name($areaCode, 'en');

        return $item;
    }

    public function save(string $areaCode, string $description): void
    {
        if (!$this->pdo instanceof PDO) {
            throw new \RuntimeException('Database connection is required to manage interest area descriptions.');
        }

        $this->ensureSeeded();

        $statement = $this->pdo->prepare(
            'INSERT INTO riasec_area_content (area_code, language_code, description)
             VALUES (:area_code, :language_code, :description)
             ON DUPLICATE KEY UPDATE description = VALUES(description)'
        );
        $statement->execute([
            'area_code' => $areaCode,
            'language_code' => 'en',
            'description' => $description,
        ]);
    }

    public function descriptionForArea(string $areaCode, string $languageCode): string
    {
        $sourceDescription = (string) config('riasec_areas.' . $areaCode . '.description', '');

        if (!$this->pdo instanceof PDO) {
            return $this->translationService->translate($sourceDescription, $languageCode);
        }

        $this->ensureSeeded();

        try {
            $statement = $this->pdo->prepare(
                'SELECT description FROM riasec_area_content WHERE area_code = :area_code AND language_code = "en" LIMIT 1'
            );
            $statement->execute(['area_code' => $areaCode]);
            $description = $statement->fetchColumn();

            if (is_string($description) && $description !== '') {
                return $this->translationService->translate($description, $languageCode);
            }
        } catch (Throwable) {
        }

        return $this->translationService->translate($sourceDescription, $languageCode);
    }

    private function ensureSeeded(): void
    {
        if (!$this->pdo instanceof PDO) {
            return;
        }

        $countStatement = $this->pdo->query('SELECT COUNT(*) FROM riasec_area_content WHERE language_code = "en"');
        $count = (int) $countStatement->fetchColumn();

        if ($count > 0) {
            return;
        }

        $statement = $this->pdo->prepare(
            'INSERT INTO riasec_area_content (area_code, language_code, description)
             VALUES (:area_code, :language_code, :description)
             ON DUPLICATE KEY UPDATE description = VALUES(description)'
        );

        foreach ((array) config('riasec_areas', []) as $areaCode => $area) {
            $statement->execute([
                'area_code' => $areaCode,
                'language_code' => 'en',
                'description' => (string) ($area['description'] ?? ''),
            ]);
        }
    }

    private function fallbackRows(string $languageCode): array
    {
        $rows = [];

        foreach ((array) config('riasec_areas', []) as $areaCode => $area) {
            $rows[] = [
                'area_code' => $areaCode,
                'name' => \area_name($areaCode, $languageCode),
                'color_hex' => $area['color'],
                'description' => $this->translationService->translate((string) $area['description'], $languageCode),
                'language_code' => 'en',
            ];
        }

        return $rows;
    }
}