<?php

class Subcollection
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function getAllByCollection(int $collectionId): array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM subcollections WHERE collection_id = :collection_id ORDER BY sort_order ASC, name ASC'
        );
        $stmt->execute(['collection_id' => $collectionId]);
        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM subcollections WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO subcollections (collection_id, name, description, sort_order) VALUES (:collection_id, :name, :description, :sort_order)'
        );
        $stmt->execute([
            'collection_id' => $data['collection_id'],
            'name'          => $data['name'],
            'description'   => $data['description'] ?? null,
            'sort_order'    => $data['sort_order'] ?? 0,
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $stmt = $this->db->prepare(
            'UPDATE subcollections SET name = :name, description = :description, sort_order = :sort_order WHERE id = :id'
        );
        return $stmt->execute([
            'name'        => $data['name'],
            'description' => $data['description'] ?? null,
            'sort_order'  => $data['sort_order'] ?? 0,
            'id'          => $id,
        ]);
    }

    public function delete(int $id): bool
    {
        $stmt = $this->db->prepare('DELETE FROM subcollections WHERE id = :id');
        return $stmt->execute(['id' => $id]);
    }

    public function countItems(int $subcollectionId): int
    {
        // Proveri tip kolekcije
        $stmt = $this->db->prepare(
            'SELECT c.type FROM subcollections s 
             JOIN collections c ON s.collection_id = c.id 
             WHERE s.id = :id LIMIT 1'
        );
        $stmt->execute(['id' => $subcollectionId]);
        $collectionType = $stmt->fetchColumn();

        if ($collectionType === 'coin') {
            $stmt = $this->db->prepare('SELECT COUNT(*) FROM coins WHERE subcollection_id = :id');
        } else {
            $stmt = $this->db->prepare('SELECT COUNT(*) FROM items WHERE subcollection_id = :id');
        }
        $stmt->execute(['id' => $subcollectionId]);
        return (int)$stmt->fetchColumn();
    }

    public function updateSortOrder(int $id, int $sortOrder): bool
    {
        $stmt = $this->db->prepare('UPDATE subcollections SET sort_order = :sort_order WHERE id = :id');
        return $stmt->execute(['sort_order' => $sortOrder, 'id' => $id]);
    }

    public function swap(int $id1, int $id2, int $collectionId): void
    {
        $all = $this->getAllByCollection($collectionId);
        $pos1 = $pos2 = null;
        foreach ($all as $i => $s) {
            if ($s['id'] == $id1) $pos1 = $i;
            if ($s['id'] == $id2) $pos2 = $i;
        }
        if ($pos1 !== null && $pos2 !== null) {
            $this->updateSortOrder($id1, $pos2);
            $this->updateSortOrder($id2, $pos1);
        }
    }

    public function getWithCollection(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT s.*, c.name AS collection_name, c.type, c.user_id FROM subcollections s 
             JOIN collections c ON s.collection_id = c.id 
             WHERE s.id = :id LIMIT 1'
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }
}