<?php

class Coin
{
    private PDO $db;

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

    public function getAllBySubcollection(int $subcollectionId): array
    {
        $stmt = $this->db->prepare(
            'SELECT c.*, co.label as condition_label 
             FROM coins c 
             LEFT JOIN condition_options co ON c.condition_id = co.id 
             WHERE c.subcollection_id = :subcollection_id 
             ORDER BY c.sort_order ASC, c.id ASC'
        );
        $stmt->execute(['subcollection_id' => $subcollectionId]);
        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM coins 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 c.*, s.name AS subcollection_name, s.collection_id, col.name AS collection_name, col.type as collection_type
             FROM coins c
             JOIN subcollections s ON c.subcollection_id = s.id
             JOIN collections col ON s.collection_id = col.id
             WHERE c.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 coins (subcollection_id, image_obverse, image_reverse, country, year_minted, nominal_value, catalog_number, material, alloy_details, weight, diameter, thickness, orientation, description_obverse, description_reverse, legends, mintage, condition_id, owned, origin, purchase_price, current_value, additional_info, note, sort_order)
             VALUES (:subcollection_id, :image_obverse, :image_reverse, :country, :year_minted, :nominal_value, :catalog_number, :material, :alloy_details, :weight, :diameter, :thickness, :orientation, :description_obverse, :description_reverse, :legends, :mintage, :condition_id, :owned, :origin, :purchase_price, :current_value, :additional_info, :note, :sort_order)'
        );
        $stmt->execute([
            'subcollection_id'   => $data['subcollection_id'],
            'image_obverse'      => $data['image_obverse'] ?? null,
            'image_reverse'      => $data['image_reverse'] ?? null,
            'country'            => $data['country'] ?? null,
            'year_minted'        => $data['year_minted'] ?? null,
            'nominal_value'      => $data['nominal_value'] ?? null,
            'catalog_number'     => $data['catalog_number'] ?? null,
            'material'           => $data['material'] ?? null,
            'alloy_details'      => $data['alloy_details'] ?? null,
            'weight'             => $data['weight'] ?? null,
            'diameter'           => $data['diameter'] ?? null,
            'thickness'          => $data['thickness'] ?? null,
            'orientation'        => $data['orientation'] ?? null,
            'description_obverse'=> $data['description_obverse'] ?? null,
            'description_reverse'=> $data['description_reverse'] ?? null,
            'legends'            => $data['legends'] ?? null,
            'mintage'            => $data['mintage'] ?? null,
            'condition_id'       => $data['condition_id'] ?? null,
            'owned'              => $data['owned'] ?? 0,
            'origin'             => $data['origin'] ?? null,
            'purchase_price'     => $data['purchase_price'] ?? null,
            'current_value'      => $data['current_value'] ?? null,
            'additional_info'     => $data['additional_info'] ?? null,
            'note'               => $data['note'] ?? null,
            'sort_order'         => $sortOrder,
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function getLastInsertId(): int
    {
        return (int)$this->db->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $stmt = $this->db->prepare(
            'UPDATE coins SET image_obverse = :image_obverse, image_reverse = :image_reverse, country = :country, year_minted = :year_minted, nominal_value = :nominal_value, catalog_number = :catalog_number, material = :material, alloy_details = :alloy_details, weight = :weight, diameter = :diameter, thickness = :thickness, orientation = :orientation, description_obverse = :description_obverse, description_reverse = :description_reverse, legends = :legends, mintage = :mintage, condition_id = :condition_id, owned = :owned, origin = :origin, purchase_price = :purchase_price, current_value = :current_value, additional_info = :additional_info, note = :note, sort_order = :sort_order WHERE id = :id'
        );
        return $stmt->execute([
            'image_obverse'      => $data['image_obverse'] ?? null,
            'image_reverse'      => $data['image_reverse'] ?? null,
            'country'            => $data['country'] ?? null,
            'year_minted'        => $data['year_minted'] ?? null,
            'nominal_value'      => $data['nominal_value'] ?? null,
            'catalog_number'     => $data['catalog_number'] ?? null,
            'material'           => $data['material'] ?? null,
            'alloy_details'      => $data['alloy_details'] ?? null,
            'weight'             => $data['weight'] ?? null,
            'diameter'           => $data['diameter'] ?? null,
            'thickness'          => $data['thickness'] ?? null,
            'orientation'        => $data['orientation'] ?? null,
            'description_obverse'=> $data['description_obverse'] ?? null,
            'description_reverse'=> $data['description_reverse'] ?? null,
            'legends'            => $data['legends'] ?? null,
            'mintage'            => $data['mintage'] ?? null,
            'condition_id'       => $data['condition_id'] ?? null,
            'owned'              => $data['owned'] ?? 0,
            'origin'             => $data['origin'] ?? null,
            'purchase_price'     => $data['purchase_price'] ?? null,
            'current_value'      => $data['current_value'] ?? null,
            'additional_info'     => $data['additional_info'] ?? null,
            'note'               => $data['note'] ?? null,
            'sort_order'         => $data['sort_order'] ?? 0,
            'id'                 => $id,
        ]);
    }

    public function delete(int $id): bool
    {
        $coin = $this->findById($id);
        if ($coin) {
            if ($coin['image_obverse']) {
                FileUpload::delete(UPLOAD_ITEMS_PATH . '/' . $coin['image_obverse']);
            }
            if ($coin['image_reverse']) {
                FileUpload::delete(UPLOAD_ITEMS_PATH . '/' . $coin['image_reverse']);
            }
        }
        $stmt = $this->db->prepare('DELETE FROM coins 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 coins 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 coins 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 toggleOwned(int $id): bool
    {
        $coin = $this->findById($id);
        if (!$coin) {
            return false;
        }

        $newOwned = $coin['owned'] ? 0 : 1;
        $stmt = $this->db->prepare('UPDATE coins SET owned = :owned WHERE id = :id');
        return $stmt->execute(['owned' => $newOwned, 'id' => $id]);
    }

    public function getNextSortOrder(int $subcollectionId): int
    {
        $stmt = $this->db->prepare(
            'SELECT MAX(sort_order) as max_sort FROM coins 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 coins SET sort_order = :sort_order WHERE id = :id');
        return $stmt->execute(['sort_order' => $sortOrder, 'id' => $id]);
    }
}