<?php

namespace Infrastructure\Persistence;

use Infrastructure\Config\DatabaseFactory;

class ContentRepository
{
    private \PDO $pdo;

    public function __construct()
    {
        $this->pdo = DatabaseFactory::content();
    }

    // ==================== Orders ====================

    public function findAllOrders(array $filters = [], int $limit = 50, int $offset = 0): array
    {
        $sql = 'SELECT co.*,
                       ap.name as profile_name,
                       cc.name as contract_name,
                       cs.name as structure_name,
                       (SELECT COUNT(*) FROM content_versions WHERE order_id = co.id) as version_count
                FROM content_orders co
                LEFT JOIN author_profiles ap ON co.author_profile_id = ap.id
                LEFT JOIN content_contracts cc ON co.contract_id = cc.id
                LEFT JOIN content_structures cs ON co.structure_id = cs.id
                WHERE 1=1';

        $params = [];

        if (isset($filters['status']) && $filters['status'] !== '') {
            $sql .= ' AND co.status = :status';
            $params['status'] = $filters['status'];
        }

        if (isset($filters['profile_id']) && $filters['profile_id'] !== '') {
            $sql .= ' AND co.author_profile_id = :profile_id';
            $params['profile_id'] = $filters['profile_id'];
        }

        $sql .= ' ORDER BY co.updated_at DESC LIMIT :limit OFFSET :offset';

        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->bindValue('limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue('offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function findOrder(int $id): ?array
    {
        $stmt = $this->pdo->prepare('
            SELECT co.*,
                   ap.name as profile_name, ap.config as profile_config,
                   cc.name as contract_name, cc.config as contract_config,
                   cs.name as structure_name, cs.config as structure_config
            FROM content_orders co
            LEFT JOIN author_profiles ap ON co.author_profile_id = ap.id
            LEFT JOIN content_contracts cc ON co.contract_id = cc.id
            LEFT JOIN content_structures cs ON co.structure_id = cs.id
            WHERE co.id = :id
        ');
        $stmt->execute(['id' => $id]);
        $result = $stmt->fetch();

        return $result !== false ? $result : null;
    }

    public function createOrder(array $data): int
    {
        $stmt = $this->pdo->prepare("
            INSERT INTO content_orders
            (title, briefing, author_profile_id, contract_id, structure_id, status)
            VALUES (:title, :briefing, :profile_id, :contract_id, :structure_id, 'draft')
        ");

        $stmt->execute([
            'title' => $data['title'],
            'briefing' => $data['briefing'],
            'profile_id' => ($data['author_profile_id'] !== '' && $data['author_profile_id'] !== null) ? $data['author_profile_id'] : null,
            'contract_id' => ($data['contract_id'] !== '' && $data['contract_id'] !== null) ? $data['contract_id'] : null,
            'structure_id' => ($data['structure_id'] !== '' && $data['structure_id'] !== null) ? $data['structure_id'] : null,
        ]);

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

    public function updateOrderStatus(int $id, string $status): void
    {
        $stmt = $this->pdo->prepare('
            UPDATE content_orders SET status = :status, updated_at = NOW() WHERE id = :id
        ');
        $stmt->execute(['id' => $id, 'status' => $status]);
    }

    public function updateOrder(int $id, array $data): bool
    {
        $stmt = $this->pdo->prepare('
            UPDATE content_orders SET
                title = :title,
                briefing = :briefing,
                author_profile_id = :profile_id,
                contract_id = :contract_id,
                structure_id = :structure_id,
                updated_at = NOW()
            WHERE id = :id
        ');

        return $stmt->execute([
            'id' => $id,
            'title' => $data['title'],
            'briefing' => $data['briefing'],
            'profile_id' => ($data['author_profile_id'] !== '' && $data['author_profile_id'] !== null) ? $data['author_profile_id'] : null,
            'contract_id' => ($data['contract_id'] !== '' && $data['contract_id'] !== null) ? $data['contract_id'] : null,
            'structure_id' => ($data['structure_id'] !== '' && $data['structure_id'] !== null) ? $data['structure_id'] : null,
        ]);
    }

    // ==================== Versions ====================

    public function findVersionsByOrder(int $orderId): array
    {
        $stmt = $this->pdo->prepare('
            SELECT * FROM content_versions
            WHERE order_id = :order_id
            ORDER BY version_number DESC
        ');
        $stmt->execute(['order_id' => $orderId]);

        return $stmt->fetchAll();
    }

    public function findLatestVersion(int $orderId): ?array
    {
        $stmt = $this->pdo->prepare('
            SELECT * FROM content_versions
            WHERE order_id = :order_id
            ORDER BY version_number DESC
            LIMIT 1
        ');
        $stmt->execute(['order_id' => $orderId]);
        $result = $stmt->fetch();

        return $result !== false ? $result : null;
    }

    public function findVersion(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM content_versions WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $result = $stmt->fetch();

        return $result !== false ? $result : null;
    }

    // ==================== Critiques ====================

    public function findCritiquesByVersion(int $versionId): array
    {
        $stmt = $this->pdo->prepare('
            SELECT cc.*, c.name as critic_name
            FROM content_critiques cc
            JOIN critics c ON cc.critic_id = c.id
            WHERE cc.version_id = :version_id
            ORDER BY cc.round DESC, c.sort_order ASC
        ');
        $stmt->execute(['version_id' => $versionId]);

        $results = $stmt->fetchAll();

        // Parse feedback JSON and merge with row
        foreach ($results as &$row) {
            if (isset($row['feedback']) && $row['feedback'] !== '') {
                $feedback = json_decode($row['feedback'], true);
                if (is_array($feedback)) {
                    $row = array_merge($row, $feedback);
                }
            }
        }

        return $results;
    }

    // ==================== Sources ====================

    public function findSourcesByOrder(int $orderId): array
    {
        $stmt = $this->pdo->prepare('
            SELECT cs.*, c.content, d.filename as document_name
            FROM content_sources cs
            JOIN chunks c ON cs.chunk_id = c.id
            JOIN documents d ON c.document_id = d.id
            WHERE cs.order_id = :order_id
            ORDER BY cs.relevance_score DESC
        ');
        $stmt->execute(['order_id' => $orderId]);

        return $stmt->fetchAll();
    }

    // ==================== Profiles ====================

    public function findAllProfiles(): array
    {
        $stmt = $this->pdo->query('
            SELECT * FROM author_profiles WHERE is_active = 1 ORDER BY name
        ');

        return $stmt->fetchAll();
    }

    // ==================== Contracts ====================

    public function findAllContracts(): array
    {
        $stmt = $this->pdo->query('
            SELECT * FROM content_contracts WHERE is_active = 1 ORDER BY name
        ');

        return $stmt->fetchAll();
    }

    // ==================== Structures ====================

    public function findAllStructures(): array
    {
        $stmt = $this->pdo->query('
            SELECT * FROM content_structures WHERE is_active = 1 ORDER BY name
        ');

        return $stmt->fetchAll();
    }

    // ==================== Critics ====================

    public function findAllCritics(): array
    {
        $stmt = $this->pdo->query('
            SELECT * FROM critics WHERE is_active = 1 ORDER BY sort_order
        ');

        return $stmt->fetchAll();
    }

    // ==================== Statistics ====================

    public function getStatistics(): array
    {
        $stats = [];

        $stmt = $this->pdo->query('SELECT COUNT(*) FROM content_orders');
        $stats['total_orders'] = (int) $stmt->fetchColumn();

        $stmt = $this->pdo->query('SELECT status, COUNT(*) as count FROM content_orders GROUP BY status');
        $stats['by_status'] = $stmt->fetchAll(\PDO::FETCH_KEY_PAIR);

        $stmt = $this->pdo->query('SELECT COUNT(*) FROM content_versions');
        $stats['total_versions'] = (int) $stmt->fetchColumn();

        $stmt = $this->pdo->query('SELECT COUNT(*) FROM content_critiques');
        $stats['total_critiques'] = (int) $stmt->fetchColumn();

        return $stats;
    }
}