from sqlalchemy import Column, Integer, String, DateTime, JSON, ForeignKey, Float
from sqlalchemy.orm import relationship
from datetime import datetime
from .database import Base

class Track(Base):
    __tablename__ = "tracks"

    id = Column(String, primary_key=True, index=True) # Spotify ID
    name = Column(String)
    artist = Column(String)
    album = Column(String)
    duration_ms = Column(Integer)
    popularity = Column(Integer, nullable=True)

    # Store raw full JSON response for future-proofing analysis
    raw_data = Column(JSON, nullable=True)

    # Enriched Data (Phase 3 Prep)
    # Audio Features
    danceability = Column(Float, nullable=True)
    energy = Column(Float, nullable=True)
    key = Column(Integer, nullable=True)
    loudness = Column(Float, nullable=True)
    mode = Column(Integer, nullable=True)
    speechiness = Column(Float, nullable=True)
    acousticness = Column(Float, nullable=True)
    instrumentalness = Column(Float, nullable=True)
    liveness = Column(Float, nullable=True)
    valence = Column(Float, nullable=True)
    tempo = Column(Float, nullable=True)
    time_signature = Column(Integer, nullable=True)

    # Genres (stored as JSON list of strings)
    genres = Column(JSON, nullable=True)

    # AI Analysis fields
    lyrics_summary = Column(String, nullable=True)
    genre_tags = Column(String, nullable=True) # JSON list stored as string or just raw JSON

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    plays = relationship("PlayHistory", back_populates="track")


class PlayHistory(Base):
    __tablename__ = "play_history"

    id = Column(Integer, primary_key=True, index=True)
    track_id = Column(String, ForeignKey("tracks.id"))
    played_at = Column(DateTime, index=True) # The timestamp from Spotify

    # Context (album, playlist, etc.)
    context_uri = Column(String, nullable=True)

    track = relationship("Track", back_populates="plays")