<?php

class Listing
{
    private PDO $db;
    public const PER_PAGE = 20;
    public const EXPIRE_DAYS = 90;

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

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT l.*, u.username, u.display_name, u.avatar,
                    co.label as condition_label, co.description as condition_description
             FROM listings l
             JOIN users u ON l.user_id = u.id
             LEFT JOIN condition_options co ON l.condition_id = co.id
             WHERE l.id = :id LIMIT 1'
        );
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT l.*, u.username, u.display_name, u.avatar,
                    co.label as condition_label, co.description as condition_description
             FROM listings l
             JOIN users u ON l.user_id = u.id
             LEFT JOIN condition_options co ON l.condition_id = co.id
             WHERE l.slug = :slug LIMIT 1'
        );
        $stmt->execute(['slug' => $slug]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function create(array $data): int
    {
        $slug = $this->generateUniqueSlug($data['title'] ?? 'listing');
        $expiresAt = date('Y-m-d H:i:s', strtotime('+' . self::EXPIRE_DAYS . ' days'));

        $stmt = $this->db->prepare(
            'INSERT INTO listings (user_id, item_type, item_id, listing_type, title, slug, description, country, year, condition_id, material, asking_price, currency, status, expires_at)
             VALUES (:user_id, :item_type, :item_id, :listing_type, :title, :slug, :description, :country, :year, :condition_id, :material, :asking_price, :currency, :status, :expires_at)'
        );
        $stmt->execute([
            'user_id'      => $data['user_id'],
            'item_type'    => $data['item_type'] ?? 'banknote',
            'item_id'      => $data['item_id'] ?: null,
            'listing_type' => $data['listing_type'] ?? 'sell',
            'title'        => $data['title'],
            'slug'         => $slug,
            'description'  => $data['description'] ?? null,
            'country'      => $data['country'] ?? null,
            'year'         => $data['year'] ?? null,
            'condition_id' => $data['condition_id'] ?: null,
            'material'     => $data['material'] ?? null,
            'asking_price' => $data['asking_price'] ?: null,
            'currency'     => $data['currency'] ?? 'EUR',
            'status'       => 'active',
            'expires_at'   => $expiresAt,
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $stmt = $this->db->prepare(
            'UPDATE listings SET item_type = :item_type, listing_type = :listing_type, title = :title, description = :description, country = :country, year = :year, condition_id = :condition_id, material = :material, asking_price = :asking_price, currency = :currency, status = :status WHERE id = :id'
        );
        return $stmt->execute([
            'item_type'    => $data['item_type'] ?? 'banknote',
            'listing_type' => $data['listing_type'] ?? 'sell',
            'title'        => $data['title'],
            'description'  => $data['description'] ?? null,
            'country'      => $data['country'] ?? null,
            'year'         => $data['year'] ?? null,
            'condition_id' => $data['condition_id'] ?: null,
            'material'     => $data['material'] ?? null,
            'asking_price' => $data['asking_price'] ?: null,
            'currency'     => $data['currency'] ?? 'EUR',
            'status'       => $data['status'] ?? 'active',
            'id'           => $id,
        ]);
    }

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

    public function updateStatus(int $id, string $status): bool
    {
        $stmt = $this->db->prepare('UPDATE listings SET status = :status WHERE id = :id');
        return $stmt->execute(['status' => $status, 'id' => $id]);
    }

    /**
     * Mark a listing as sold and optionally record the buyer.
     * When $buyerId is null, it is treated as an external sale (no rating flow).
     */
    public function markSold(int $id, ?int $buyerId): bool
    {
        $stmt = $this->db->prepare(
            "UPDATE listings SET status = 'sold', buyer_id = :buyer_id, sold_at = NOW() WHERE id = :id"
        );
        return $stmt->execute(['buyer_id' => $buyerId, 'id' => $id]);
    }

    public function incrementViews(int $id): void
    {
        $stmt = $this->db->prepare('UPDATE listings SET views_count = views_count + 1 WHERE id = :id');
        $stmt->execute(['id' => $id]);
    }

    public function listActive(array $filters = [], int $page = 1): array
    {
        $where = ['l.status = :status'];
        $params = ['status' => 'active'];
        $this->applyFilters($where, $params, $filters);

        $offset = ($page - 1) * self::PER_PAGE;
        $orderBy = $this->getOrderBy($filters['sort'] ?? 'newest');

        $sql = 'SELECT l.*, u.username, u.display_name, u.avatar,
                       co.label as condition_label
                FROM listings l
                JOIN users u ON l.user_id = u.id
                LEFT JOIN condition_options co ON l.condition_id = co.id
                WHERE ' . implode(' AND ', $where) .
               ' ORDER BY ' . $orderBy .
               ' LIMIT ' . self::PER_PAGE . ' OFFSET ' . $offset;

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function countActive(array $filters = []): int
    {
        $where = ['l.status = :status'];
        $params = ['status' => 'active'];
        $this->applyFilters($where, $params, $filters);

        $sql = 'SELECT COUNT(*) FROM listings l WHERE ' . implode(' AND ', $where);
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    public function getByUser(int $userId, ?string $status = null): array
    {
        $sql = 'SELECT l.*, co.label as condition_label
                FROM listings l
                LEFT JOIN condition_options co ON l.condition_id = co.id
                WHERE l.user_id = :user_id';
        $params = ['user_id' => $userId];

        if ($status) {
            $sql .= ' AND l.status = :status';
            $params['status'] = $status;
        }

        $sql .= ' ORDER BY l.created_at DESC';
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function countByUser(int $userId): int
    {
        $stmt = $this->db->prepare('SELECT COUNT(*) FROM listings WHERE user_id = :user_id');
        $stmt->execute(['user_id' => $userId]);
        return (int)$stmt->fetchColumn();
    }

    public function similar(int $listingId, int $limit = 4): array
    {
        $listing = $this->findById($listingId);
        if (!$listing) return [];

        $where = ['l.id != :id', 'l.status = :status'];
        $params = ['id' => $listingId, 'status' => 'active'];

        // Match by item_type first, then country, then year
        $orderParts = [];
        if ($listing['country']) {
            $orderParts[] = 'IF(l.country = :country, 0, 1)';
            $params['country'] = $listing['country'];
        }
        if ($listing['item_type']) {
            $where[] = 'l.item_type = :item_type';
            $params['item_type'] = $listing['item_type'];
        }

        $orderBy = !empty($orderParts) ? implode(', ', $orderParts) . ', l.created_at DESC' : 'l.created_at DESC';

        $sql = 'SELECT l.*, u.username, u.display_name, u.avatar, co.label as condition_label
                FROM listings l
                JOIN users u ON l.user_id = u.id
                LEFT JOIN condition_options co ON l.condition_id = co.id
                WHERE ' . implode(' AND ', $where) .
               ' ORDER BY ' . $orderBy .
               ' LIMIT ' . $limit;

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function getActiveByUser(int $userId, int $limit = 10): array
    {
        $stmt = $this->db->prepare(
            'SELECT l.*, co.label as condition_label
             FROM listings l
             LEFT JOIN condition_options co ON l.condition_id = co.id
             WHERE l.user_id = :user_id AND l.status = :status
             ORDER BY l.created_at DESC LIMIT ' . $limit
        );
        $stmt->execute(['user_id' => $userId, 'status' => 'active']);
        return $stmt->fetchAll();
    }

    public function expireOldListings(): int
    {
        $stmt = $this->db->prepare(
            "UPDATE listings SET status = 'expired' WHERE status = 'active' AND expires_at IS NOT NULL AND expires_at < NOW()"
        );
        $stmt->execute();
        return $stmt->rowCount();
    }

    public function lastModified(): ?string
    {
        return $this->db->query("SELECT MAX(updated_at) FROM listings WHERE status = 'active'")->fetchColumn() ?: null;
    }

    public function getAllActiveSlugs(): array
    {
        $stmt = $this->db->query("SELECT slug, updated_at FROM listings WHERE status = 'active' ORDER BY updated_at DESC");
        return $stmt->fetchAll();
    }

    private function generateUniqueSlug(string $title): string
    {
        $base = Slug::make($title);
        $slug = $base;
        $i = 1;
        while ($this->slugExists($slug)) {
            $slug = $base . '-' . $i;
            $i++;
        }
        return $slug;
    }

    private function slugExists(string $slug): bool
    {
        $stmt = $this->db->prepare('SELECT COUNT(*) FROM listings WHERE slug = :slug');
        $stmt->execute(['slug' => $slug]);
        return (int)$stmt->fetchColumn() > 0;
    }

    private function applyFilters(array &$where, array &$params, array $filters): void
    {
        if (!empty($filters['listing_type'])) {
            $where[] = 'l.listing_type = :listing_type';
            $params['listing_type'] = $filters['listing_type'];
        }
        if (!empty($filters['item_type'])) {
            $where[] = 'l.item_type = :item_type';
            $params['item_type'] = $filters['item_type'];
        }
        if (!empty($filters['country'])) {
            $where[] = 'l.country LIKE :country';
            $params['country'] = '%' . $filters['country'] . '%';
        }
        if (!empty($filters['year'])) {
            $where[] = 'l.year LIKE :year';
            $params['year'] = '%' . $filters['year'] . '%';
        }
        if (!empty($filters['year_from'])) {
            $where[] = 'l.year >= :year_from';
            $params['year_from'] = $filters['year_from'];
        }
        if (!empty($filters['year_to'])) {
            $where[] = 'l.year <= :year_to';
            $params['year_to'] = $filters['year_to'];
        }
        if (!empty($filters['condition_id'])) {
            $where[] = 'l.condition_id = :condition_id';
            $params['condition_id'] = $filters['condition_id'];
        }
        if (!empty($filters['material'])) {
            $where[] = 'l.material = :material';
            $params['material'] = $filters['material'];
        }
        if (!empty($filters['price_from'])) {
            $where[] = 'l.asking_price >= :price_from';
            $params['price_from'] = $filters['price_from'];
        }
        if (!empty($filters['price_to'])) {
            $where[] = 'l.asking_price <= :price_to';
            $params['price_to'] = $filters['price_to'];
        }
        if (!empty($filters['currency'])) {
            $where[] = 'l.currency = :currency';
            $params['currency'] = $filters['currency'];
        }
        if (!empty($filters['q'])) {
            $where[] = '(l.title LIKE :q OR l.description LIKE :q2)';
            $params['q'] = '%' . $filters['q'] . '%';
            $params['q2'] = '%' . $filters['q'] . '%';
        }
    }

    private function getOrderBy(string $sort): string
    {
        return match ($sort) {
            'price_asc'  => 'l.asking_price ASC, l.created_at DESC',
            'price_desc' => 'l.asking_price DESC, l.created_at DESC',
            'popular'    => 'l.views_count DESC, l.created_at DESC',
            default      => 'l.created_at DESC',
        };
    }
}