<?php
declare(strict_types=1);

namespace App\Support;

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

final class QuestionStore
{
    private ?PDO $pdo;
    private QuestionBank $questionBank;
    private TranslationService $translationService;

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

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

        $this->ensureSeeded();

        $sql = 'SELECT q.id, q.source_key, q.area_code, q.sort_order, q.is_active,
                       COALESCE(qt_english.statement, "") AS statement
                FROM questions q
                LEFT JOIN question_translations qt_english
                    ON qt_english.question_id = q.id AND qt_english.language_code = "en"
                WHERE 1 = 1';
        $params = [];

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

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

        $sql .= ' ORDER BY q.sort_order ASC, q.id ASC';

        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);

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

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

        $this->ensureSeeded();

        $statement = $this->pdo->prepare(
            'SELECT q.source_key AS id, q.area_code,
                    COALESCE(qt_english.statement, "") AS statement
             FROM questions q
             LEFT JOIN question_translations qt_english
                ON qt_english.question_id = q.id AND qt_english.language_code = "en"
             WHERE q.is_active = 1
             ORDER BY q.sort_order ASC, q.id ASC'
        );
        $statement->execute();

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

    public function find(int $id): ?array
    {
        if (!$this->pdo instanceof PDO) {
            return null;
        }

        $this->ensureSeeded();

        $statement = $this->pdo->prepare('SELECT id, source_key, area_code, sort_order, is_active FROM questions WHERE id = :id LIMIT 1');
        $statement->execute(['id' => $id]);
        $question = $statement->fetch();

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

        $translationStatement = $this->pdo->prepare(
            'SELECT statement FROM question_translations WHERE question_id = :question_id AND language_code = "en" LIMIT 1'
        );
        $translationStatement->execute(['question_id' => $id]);
        $question['statement'] = (string) ($translationStatement->fetchColumn() ?: '');

        return $question;
    }

    public function save(array $payload): int
    {
        if (!$this->pdo instanceof PDO) {
            throw new \RuntimeException('Database connection is required to manage questions.');
        }

        $this->ensureSeeded();

        $questionId = (int) ($payload['id'] ?? 0);
        $sortOrder = max(1, (int) ($payload['sort_order'] ?? 1));
        $isActive = !empty($payload['is_active']) ? 1 : 0;
        $statementText = trim((string) ($payload['statement'] ?? ''));

        $this->pdo->beginTransaction();

        try {
            if ($questionId > 0) {
                $statement = $this->pdo->prepare(
                    'UPDATE questions SET area_code = :area_code, sort_order = :sort_order, is_active = :is_active WHERE id = :id'
                );
                $statement->execute([
                    'area_code' => $payload['area_code'],
                    'sort_order' => $sortOrder,
                    'is_active' => $isActive,
                    'id' => $questionId,
                ]);
            } else {
                $statement = $this->pdo->prepare(
                    'INSERT INTO questions (area_code, source_key, sort_order, is_active)
                     VALUES (:area_code, :source_key, :sort_order, :is_active)'
                );
                $statement->execute([
                    'area_code' => $payload['area_code'],
                    'source_key' => $payload['source_key'] ?: ('custom-' . bin2hex(random_bytes(4))),
                    'sort_order' => $sortOrder,
                    'is_active' => $isActive,
                ]);
                $questionId = (int) $this->pdo->lastInsertId();
            }

            $deleteTranslations = $this->pdo->prepare(
                'DELETE FROM question_translations WHERE question_id = :question_id AND language_code <> "en"'
            );
            $deleteTranslations->execute(['question_id' => $questionId]);

            $translationStatement = $this->pdo->prepare(
                'INSERT INTO question_translations (question_id, language_code, statement)
                 VALUES (:question_id, "en", :statement)
                 ON DUPLICATE KEY UPDATE statement = VALUES(statement)'
            );
            $translationStatement->execute([
                'question_id' => $questionId,
                'statement' => $statementText,
            ]);

            $this->pdo->commit();

            return $questionId;
        } catch (Throwable $throwable) {
            $this->pdo->rollBack();
            throw $throwable;
        }
    }

    public function delete(int $id): string
    {
        if (!$this->pdo instanceof PDO) {
            throw new \RuntimeException('Database connection is required to manage questions.');
        }

        $answerCountStatement = $this->pdo->prepare('SELECT COUNT(*) FROM participant_answers WHERE question_id = :question_id');
        $answerCountStatement->execute(['question_id' => $id]);
        $answerCount = (int) $answerCountStatement->fetchColumn();

        if ($answerCount > 0) {
            $statement = $this->pdo->prepare('UPDATE questions SET is_active = 0 WHERE id = :id');
            $statement->execute(['id' => $id]);
            return 'deactivated';
        }

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

        return 'deleted';
    }

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

        $this->seedReferenceData();

        $count = (int) $this->pdo->query('SELECT COUNT(*) FROM questions')->fetchColumn();

        if ($count > 0) {
            return;
        }

        $questions = $this->questionBank->all();
        $questionStatement = $this->pdo->prepare(
            'INSERT INTO questions (area_code, source_key, sort_order, is_active)
             VALUES (:area_code, :source_key, :sort_order, 1)'
        );
        $translationStatement = $this->pdo->prepare(
            'INSERT INTO question_translations (question_id, language_code, statement)
             VALUES (:question_id, :language_code, :statement)
             ON DUPLICATE KEY UPDATE statement = VALUES(statement)'
        );

        foreach ($questions as $index => $question) {
            $questionStatement->execute([
                'area_code' => $question['area_code'],
                'source_key' => $question['id'],
                'sort_order' => $index + 1,
            ]);

            $translationStatement->execute([
                'question_id' => (int) $this->pdo->lastInsertId(),
                'language_code' => 'en',
                'statement' => $question['statement'],
            ]);
        }
    }

    private function seedReferenceData(): void
    {
        $languageStatement = $this->pdo->prepare(
            'INSERT INTO languages (code, name) VALUES (:code, :name) ON DUPLICATE KEY UPDATE name = VALUES(name)'
        );
        foreach ((array) config('supported_languages', []) as $code => $name) {
            $languageStatement->execute(['code' => $code, 'name' => $name]);
        }

        $areaStatement = $this->pdo->prepare(
            'INSERT INTO riasec_areas (code, name, color_hex, sort_order)
             VALUES (:code, :name, :color_hex, :sort_order)
             ON DUPLICATE KEY UPDATE name = VALUES(name), color_hex = VALUES(color_hex), sort_order = VALUES(sort_order)'
        );
        $sortOrder = 1;
        foreach ((array) config('riasec_areas', []) as $code => $area) {
            $areaStatement->execute([
                'code' => $code,
                'name' => $area['name'],
                'color_hex' => $area['color'],
                'sort_order' => $sortOrder++,
            ]);
        }
    }

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

        foreach ($questions as &$question) {
            $question['statement'] = $this->translationService->translate((string) ($question['statement'] ?? ''), $languageCode);
        }

        return $questions;
    }
}