<?php
namespace App\Models;

use Core\Model;

class PropertyModel extends Model
{
    protected string $table = 'properties';

    public function allWithDetails(array $filters = []): array
    {
        $conditions = ["p.status = 'active'"];
        $params = [];

        if (!empty($filters['type_id'])) {
            $conditions[] = "p.property_type_id = ?";
            $params[] = $filters['type_id'];
        }
        if (!empty($filters['region_id'])) {
            $conditions[] = "p.region_id = ?";
            $params[] = $filters['region_id'];
        }

        $where = implode(' AND ', $conditions);

        return $this->db->fetchAll(
            "SELECT p.*, pt.name AS type_name, r.name AS region_name,
                    d.name AS district_name,
                    MIN(u.monthly_rent) AS min_rent,
                    COUNT(DISTINCT u.id) AS total_units,
                    SUM(CASE WHEN u.status='available' THEN 1 ELSE 0 END) AS available_units,
                    SUM(CASE WHEN u.status='occupied' THEN 1 ELSE 0 END) AS occupied_units
             FROM properties p
             LEFT JOIN property_types pt ON pt.id = p.property_type_id
             LEFT JOIN regions r ON r.id = p.region_id
             LEFT JOIN districts d ON d.id = p.district_id
             LEFT JOIN units u ON u.property_id = p.id
             WHERE {$where}
             GROUP BY p.id
             ORDER BY p.is_featured DESC, p.created_at DESC",
            $params
        );
    }

    public function findWithDetails(int $id): array|false
    {
        return $this->db->fetch(
            "SELECT p.*, pt.name AS type_name, r.name AS region_name,
                    d.name AS district_name, w.name AS ward_name,
                    u.first_name AS owner_first, u.last_name AS owner_last,
                    m.first_name AS manager_first, m.last_name AS manager_last
             FROM properties p
             LEFT JOIN property_types pt ON pt.id = p.property_type_id
             LEFT JOIN regions r ON r.id = p.region_id
             LEFT JOIN districts d ON d.id = p.district_id
             LEFT JOIN wards w ON w.id = p.ward_id
             LEFT JOIN users u ON u.id = p.owner_id
             LEFT JOIN users m ON m.id = p.manager_id
             WHERE p.id = ?",
            [$id]
        );
    }

    public function getFeatured(int $limit = 6): array
    {
        return $this->db->fetchAll(
            "SELECT p.*, pt.name AS type_name, r.name AS region_name,
                    MIN(u.monthly_rent) AS min_rent,
                    COUNT(DISTINCT u.id) AS total_units,
                    SUM(CASE WHEN u.status='available' THEN 1 ELSE 0 END) AS available_units
             FROM properties p
             LEFT JOIN property_types pt ON pt.id = p.property_type_id
             LEFT JOIN regions r ON r.id = p.region_id
             LEFT JOIN units u ON u.property_id = p.id
             WHERE p.is_featured = 1 AND p.status = 'active'
             GROUP BY p.id
             ORDER BY p.created_at DESC
             LIMIT ?",
            [$limit]
        );
    }

    public function search(array $filters): array
    {
        $conditions = ["p.status = 'active'"];
        $params = [];

        if (!empty($filters['region_id'])) {
            $conditions[] = "p.region_id = ?";
            $params[] = $filters['region_id'];
        }
        if (!empty($filters['district_id'])) {
            $conditions[] = "p.district_id = ?";
            $params[] = $filters['district_id'];
        }
        if (!empty($filters['type_id'])) {
            $conditions[] = "p.property_type_id = ?";
            $params[] = $filters['type_id'];
        }
        if (!empty($filters['bedrooms'])) {
            $conditions[] = "u.bedrooms >= ?";
            $params[] = $filters['bedrooms'];
        }
        if (!empty($filters['min_price'])) {
            $conditions[] = "u.monthly_rent >= ?";
            $params[] = $filters['min_price'];
        }
        if (!empty($filters['max_price'])) {
            $conditions[] = "u.monthly_rent <= ?";
            $params[] = $filters['max_price'];
        }
        if (!empty($filters['is_furnished'])) {
            $conditions[] = "u.is_furnished = 1";
        }
        if (!empty($filters['has_parking'])) {
            $conditions[] = "u.has_parking = 1";
        }
        if (!empty($filters['keyword'])) {
            $conditions[] = "(p.name LIKE ? OR p.description LIKE ? OR p.area LIKE ?)";
            $kw = '%' . $filters['keyword'] . '%';
            $params = array_merge($params, [$kw, $kw, $kw]);
        }

        $where = implode(' AND ', $conditions);
        return $this->db->fetchAll(
            "SELECT DISTINCT p.*, pt.name AS type_name, r.name AS region_name,
                    MIN(u.monthly_rent) AS min_rent,
                    SUM(CASE WHEN u.status='available' THEN 1 ELSE 0 END) AS available_units
             FROM properties p
             LEFT JOIN property_types pt ON pt.id = p.property_type_id
             LEFT JOIN regions r ON r.id = p.region_id
             LEFT JOIN units u ON u.property_id = p.id
             WHERE {$where}
             GROUP BY p.id
             ORDER BY p.is_featured DESC, p.created_at DESC",
            $params
        );
    }

    public function getImages(int $propertyId): array
    {
        return $this->db->fetchAll("SELECT * FROM property_images WHERE property_id = ? ORDER BY is_primary DESC, sort_order ASC", [$propertyId]);
    }

    public function getAmenities(int $propertyId): array
    {
        return $this->db->fetchAll(
            "SELECT a.* FROM amenities a 
             JOIN property_amenities pa ON pa.amenity_id = a.id 
             WHERE pa.property_id = ?", [$propertyId]
        );
    }

    public function getUnits(int $propertyId, string $status = ''): array
    {
        $sql = "SELECT u.*, b.name AS building_name FROM units u 
                JOIN buildings b ON b.id = u.building_id 
                WHERE u.property_id = ?";
        $params = [$propertyId];
        if ($status) { $sql .= " AND u.status = ?"; $params[] = $status; }
        $sql .= " ORDER BY b.name, u.unit_number";
        return $this->db->fetchAll($sql, $params);
    }

    public function getStats(): array
    {
        return [
            'total'     => $this->count(),
            'active'    => $this->count("status = 'active'"),
            'featured'  => $this->count("is_featured = 1"),
        ];
    }
}
