<?php

class Item
{
    private PDO $db;

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

    public function getAllBySubcollection(int $subcollectionId): array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM items WHERE subcollection_id = :subcollection_id ORDER BY sort_order ASC, id ASC'
        );
        $stmt->execute(['subcollection_id' => $subcollectionId]);
        return $stmt->fetchAll();
    }

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

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

    public function create(array $data): int
    {
        $sortOrder = $data['sort_order'] ?? $this->getNextSortOrder($data['subcollection_id']);

        $stmt = $this->db->prepare(
            'INSERT INTO items (subcollection_id, image_obverse, image_reverse, nominal_value, dimensions, color, description_obverse, description_reverse, issue_date, additional_info, catalog_number, country, purchase_price, note, sort_order)
             VALUES (:subcollection_id, :image_obverse, :image_reverse, :nominal_value, :dimensions, :color, :description_obverse, :description_reverse, :issue_date, :additional_info, :catalog_number, :country, :purchase_price, :note, :sort_order)'
        );
        $stmt->execute([
            'subcollection_id'   => $data['subcollection_id'],
            'image_obverse'      => $data['image_obverse'] ?? null,
            'image_reverse'      => $data['image_reverse'] ?? null,
            'nominal_value'      => $data['nominal_value'] ?? null,
            'dimensions'         => $data['dimensions'] ?? null,
            'color'              => $data['color'] ?? null,
            'description_obverse'=> $data['description_obverse'] ?? null,
            'description_reverse'=> $data['description_reverse'] ?? null,
            'issue_date'         => $data['issue_date'] ?? null,
            'additional_info'    => $data['additional_info'] ?? null,
            'catalog_number'     => $data['catalog_number'] ?? null,
            'country'            => $data['country'] ?? null,
            'purchase_price'     => $data['purchase_price'] ?? null,
            'note'               => $data['note'] ?? null,
            'sort_order'         => $sortOrder,
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $stmt = $this->db->prepare(
            'UPDATE items SET image_obverse = :image_obverse, image_reverse = :image_reverse, nominal_value = :nominal_value, dimensions = :dimensions, color = :color, description_obverse = :description_obverse, description_reverse = :description_reverse, issue_date = :issue_date, additional_info = :additional_info, catalog_number = :catalog_number, country = :country, purchase_price = :purchase_price, note = :note, sort_order = :sort_order WHERE id = :id'
        );
        return $stmt->execute([
            'image_obverse'      => $data['image_obverse'] ?? null,
            'image_reverse'      => $data['image_reverse'] ?? null,
            'nominal_value'      => $data['nominal_value'] ?? null,
            'dimensions'         => $data['dimensions'] ?? null,
            'color'              => $data['color'] ?? null,
            'description_obverse'=> $data['description_obverse'] ?? null,
            'description_reverse'=> $data['description_reverse'] ?? null,
            'issue_date'         => $data['issue_date'] ?? null,
            'additional_info'    => $data['additional_info'] ?? null,
            'catalog_number'     => $data['catalog_number'] ?? null,
            'country'            => $data['country'] ?? null,
            'purchase_price'     => $data['purchase_price'] ?? null,
            'note'               => $data['note'] ?? null,
            'sort_order'         => $data['sort_order'] ?? 0,
            'id'                 => $id,
        ]);
    }

    public function delete(int $id): bool
    {
        $item = $this->findById($id);
        if ($item) {
            if ($item['image_obverse']) {
                FileUpload::delete(UPLOAD_ITEMS_PATH . '/' . $item['image_obverse']);
            }
            if ($item['image_reverse']) {
                FileUpload::delete(UPLOAD_ITEMS_PATH . '/' . $item['image_reverse']);
            }
        }
        $stmt = $this->db->prepare('DELETE FROM items WHERE id = :id');
        return $stmt->execute(['id' => $id]);
    }

    public function moveUp(int $id): bool
    {
        $current = $this->findById($id);
        if (!$current) {
            return false;
        }

        $stmt = $this->db->prepare(
            'SELECT * FROM items WHERE subcollection_id = :subcollection_id AND sort_order < :current_sort ORDER BY sort_order DESC LIMIT 1'
        );
        $stmt->execute(['subcollection_id' => $current['subcollection_id'], 'current_sort' => $current['sort_order']]);
        $previous = $stmt->fetch();

        if (!$previous) {
            return false;
        }

        $this->updateSortOrder($id, $previous['sort_order']);
        $this->updateSortOrder($previous['id'], $current['sort_order']);

        return true;
    }

    public function moveDown(int $id): bool
    {
        $current = $this->findById($id);
        if (!$current) {
            return false;
        }

        $stmt = $this->db->prepare(
            'SELECT * FROM items WHERE subcollection_id = :subcollection_id AND sort_order > :current_sort ORDER BY sort_order ASC LIMIT 1'
        );
        $stmt->execute(['subcollection_id' => $current['subcollection_id'], 'current_sort' => $current['sort_order']]);
        $next = $stmt->fetch();

        if (!$next) {
            return false;
        }

        $this->updateSortOrder($id, $next['sort_order']);
        $this->updateSortOrder($next['id'], $current['sort_order']);

        return true;
    }

    public function getNextSortOrder(int $subcollectionId): int
    {
        $stmt = $this->db->prepare(
            'SELECT MAX(sort_order) as max_sort FROM items WHERE subcollection_id = :subcollection_id'
        );
        $stmt->execute(['subcollection_id' => $subcollectionId]);
        $result = $stmt->fetch();
        return ($result['max_sort'] ?? 0) + 1;
    }

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