<?php
declare(strict_types=1);

namespace App\Support;

use App\Core\Database;
use PDO;

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

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

    public function allCareers(?string $areaCode = null, string $languageCode = 'en', string $search = ''): array
    {
        return $this->fetchList('career_opportunities', 'title', $areaCode, $languageCode, $search);
    }

    public function allProgrammes(?string $areaCode = null, string $languageCode = 'en', string $search = ''): array
    {
        return $this->fetchList('suitable_programmes', 'programme_name', $areaCode, $languageCode, $search);
    }

    public function findCareer(int $id): ?array
    {
        return $this->findById('career_opportunities', $id);
    }

    public function findProgramme(int $id): ?array
    {
        return $this->findById('suitable_programmes', $id);
    }

    public function saveCareer(array $payload): int
    {
        return $this->saveItem('career_opportunities', 'title', $payload);
    }

    public function saveProgramme(array $payload): int
    {
        return $this->saveItem('suitable_programmes', 'programme_name', $payload);
    }

    public function deleteCareer(int $id): void
    {
        $this->deleteItem('career_opportunities', $id);
    }

    public function deleteProgramme(int $id): void
    {
        $this->deleteItem('suitable_programmes', $id);
    }

    public function careersForArea(string $areaCode, string $languageCode = 'en'): array
    {
        $items = $this->fetchAreaItems('career_opportunities', 'title', $areaCode, $languageCode);

        if ($items !== []) {
            return $items;
        }

        return (array) config('riasec_areas.' . $areaCode . '.career_opportunities', []);
    }

    public function programmesForArea(string $areaCode, string $languageCode = 'en'): array
    {
        $items = $this->fetchAreaItems('suitable_programmes', 'programme_name', $areaCode, $languageCode);

        if ($items !== []) {
            return $items;
        }

        return (array) config('riasec_areas.' . $areaCode . '.suitable_programmes', []);
    }

    private function fetchList(string $table, string $titleField, ?string $areaCode, string $languageCode, string $search): array
    {
        if (!$this->pdo instanceof PDO) {
            return [];
        }

        $sql = "SELECT id, area_code, 'en' AS language_code, {$titleField} AS title, description, sort_order, is_active" .
            ($table === 'suitable_programmes' ? ', programme_code' : '') .
            " FROM {$table} WHERE language_code = 'en'";
        $params = [];

        if ($areaCode !== null && $areaCode !== '') {
            $sql .= ' AND area_code = :area_code';
            $params['area_code'] = $areaCode;
        }

        if ($search !== '') {
            $sql .= " AND {$titleField} LIKE :search";
            $params['search'] = '%' . $search . '%';
        }

        $sql .= ' ORDER BY area_code ASC, sort_order ASC, id ASC';
        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);

        return $this->translateListRows($statement->fetchAll() ?: [], $languageCode);
    }

    private function findById(string $table, int $id): ?array
    {
        if (!$this->pdo instanceof PDO) {
            return null;
        }

        $statement = $this->pdo->prepare("SELECT * FROM {$table} WHERE id = :id LIMIT 1");
        $statement->execute(['id' => $id]);
        $item = $statement->fetch();

        return is_array($item) ? $item : null;
    }

    private function saveItem(string $table, string $titleField, array $payload): int
    {
        if (!$this->pdo instanceof PDO) {
            throw new \RuntimeException('Database connection is required to manage recommendations.');
        }

        $id = (int) ($payload['id'] ?? 0);
        $params = [
            'area_code' => $payload['area_code'],
            'title' => trim((string) ($payload['title'] ?? '')),
            'description' => trim((string) ($payload['description'] ?? '')),
            'sort_order' => max(1, (int) ($payload['sort_order'] ?? 1)),
            'is_active' => !empty($payload['is_active']) ? 1 : 0,
        ];

        if ($table === 'suitable_programmes') {
            $params['programme_code'] = trim((string) ($payload['programme_code'] ?? ''));
        }

        if ($id > 0) {
            $sql = "UPDATE {$table}
                    SET area_code = :area_code,
                        language_code = 'en',
                        {$titleField} = :title,
                        description = :description,
                        sort_order = :sort_order,
                        is_active = :is_active" .
                        ($table === 'suitable_programmes' ? ', programme_code = :programme_code' : '') .
                    ' WHERE id = :id';
            $statement = $this->pdo->prepare($sql);
            $statement->execute($params + ['id' => $id]);
            return $id;
        }

        $sql = "INSERT INTO {$table} (area_code, language_code, {$titleField}, description, sort_order, is_active" .
            ($table === 'suitable_programmes' ? ', programme_code' : '') .
            ') VALUES (:area_code, "en", :title, :description, :sort_order, :is_active' .
            ($table === 'suitable_programmes' ? ', :programme_code' : '') . ')';
        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);

        return (int) $this->pdo->lastInsertId();
    }

    private function deleteItem(string $table, int $id): void
    {
        if (!$this->pdo instanceof PDO) {
            return;
        }

        $statement = $this->pdo->prepare("DELETE FROM {$table} WHERE id = :id");
        $statement->execute(['id' => $id]);
    }

    private function fetchAreaItems(string $table, string $titleField, string $areaCode, string $languageCode): array
    {
        if (!$this->pdo instanceof PDO) {
            $fallbackItems = $table === 'career_opportunities'
                ? (array) config('riasec_areas.' . $areaCode . '.career_opportunities', [])
                : (array) config('riasec_areas.' . $areaCode . '.suitable_programmes', []);

            return $this->translationService->translateArray($fallbackItems, $languageCode);
        }

        $statement = $this->pdo->prepare(
            "SELECT {$titleField} AS title
             FROM {$table}
             WHERE area_code = :area_code AND language_code = 'en' AND is_active = 1
             ORDER BY sort_order ASC, id ASC"
        );
        $statement->execute([
            'area_code' => $areaCode,
        ]);
        $rows = $statement->fetchAll() ?: [];

        if ($rows === []) {
            $fallbackItems = $table === 'career_opportunities'
                ? (array) config('riasec_areas.' . $areaCode . '.career_opportunities', [])
                : (array) config('riasec_areas.' . $areaCode . '.suitable_programmes', []);

            return $this->translationService->translateArray($fallbackItems, $languageCode);
        }

        return $this->translationService->translateArray(
            array_map(static fn (array $row): string => (string) $row['title'], $rows),
            $languageCode
        );
    }

    private function translateListRows(array $rows, string $languageCode): array
    {
        if ($languageCode === 'en') {
            return $rows;
        }

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

        return $rows;
    }
}