from pydantic import BaseModel
from typing import Optional, List, Dict, Any
from datetime import date

from sqlalchemy import Column, Integer, String, Text, Date, DECIMAL
from sqlalchemy.dialects.postgresql import JSONB
from db.db_connection import Base

# ===============================
# SQLAlchemy ORM Model
# ===============================
class House(Base):
    __tablename__ = "houses"  # table name in db

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(255), nullable=False)
    price = Column(DECIMAL(12, 2), nullable=False)
    address = Column(Text, nullable=False)
    property_type = Column(String(50), nullable=False)
    furnishing_status = Column(String(50), nullable=False)
    bedroom = Column(Integer, nullable=False)
    bathroom = Column(Integer, nullable=False)
    size_sqft = Column(Integer, nullable=False)
    broadband_services = Column(JSONB, nullable=True)   # ["Unifi", "Maxis"]
    agent = Column(JSONB, nullable=True)               # {"name":"John Doe","contact":"+60 12-3456789"}
    amenities = Column(JSONB, nullable=True)           # ["gym", "Swimming Pool"]
    facilities = Column(JSONB, nullable=True)          # ["24hr security", "Carpark"]
    availability_on = Column(Date, nullable=True)

    # Latitude & Longitude instead of "nearby"
    latitude = Column(DECIMAL(9, 6), nullable=True)    # e.g. 3.139003
    longitude = Column(DECIMAL(9, 6), nullable=True)   # e.g. 101.686855

    images = Column(JSONB, nullable=True)              # ["filepath1", "filepath2"]
    crime_level = Column(String(20), nullable=True)    # Low


# ===============================
# Pydantic Model for Input
# ===============================
class HouseModel(BaseModel):
    title: str
    price: float
    address: str
    property_type: str
    furnishing_status: str
    bedroom: int
    bathroom: int
    size_sqft: int
    broadband_services: Optional[List[str]] = None
    agent: Optional[Dict[str, Any]] = None
    amenities: Optional[List[str]] = None
    facilities: Optional[List[str]] = None
    availability_on: Optional[date] = None

    latitude: Optional[float] = None
    longitude: Optional[float] = None

    images: Optional[List[str]] = None
    crime_level: Optional[str] = None  


# ===============================
# Pydantic Model for Response
# ===============================
class HouseResponse(BaseModel):
    id: int
    title: str
    price: float
    address: str
    property_type: str
    furnishing_status: str
    bedroom: int
    bathroom: int
    size_sqft: int
    broadband_services: Optional[List[str]] = None
    agent: Optional[Dict[str, Any]] = None
    amenities: Optional[List[str]] = None
    facilities: Optional[List[str]] = None
    availability_on: Optional[date] = None

    latitude: Optional[float] = None
    longitude: Optional[float] = None

    images: Optional[List[str]] = None
    crime_level: Optional[str] = None

    class Config:
        from_attributes = True  # read directly from ORM objects
