mirror of
https://github.com/bnair123/MusicAnalyser.git
synced 2026-02-25 11:46:07 +00:00
272148c5bf
- Migrate database from SQLite to PostgreSQL (100.91.248.114:5433) - Fix playlist curation to use actual top tracks instead of AI name matching - Add /playlists/history endpoint for historical playlist viewing - Add Playlist Archives section to frontend with expandable history - Add playlist-modify-* scopes to Spotify OAuth for playlist creation - Rewrite Genius client to use official API (fixes 403 scraping blocks) - Ensure playlists are created on Spotify before curation attempts - Add DATABASE.md documentation for PostgreSQL schema - Add migrations for PlaylistConfig and composition storage
148 lines
5.0 KiB
Python
148 lines
5.0 KiB
Python
from sqlalchemy import (
|
|
Boolean,
|
|
Column,
|
|
Integer,
|
|
String,
|
|
DateTime,
|
|
JSON,
|
|
ForeignKey,
|
|
Float,
|
|
Table,
|
|
Text,
|
|
)
|
|
from sqlalchemy.orm import relationship
|
|
from datetime import datetime
|
|
from .database import Base
|
|
|
|
# Association Table for Many-to-Many Relationship between Track and Artist
|
|
track_artists = Table(
|
|
"track_artists",
|
|
Base.metadata,
|
|
Column("track_id", String, ForeignKey("tracks.id"), primary_key=True),
|
|
Column("artist_id", String, ForeignKey("artists.id"), primary_key=True),
|
|
)
|
|
|
|
|
|
class Artist(Base):
|
|
__tablename__ = "artists"
|
|
|
|
id = Column(String, primary_key=True, index=True) # Spotify ID
|
|
name = Column(String)
|
|
genres = Column(JSON, nullable=True) # List of genre strings
|
|
image_url = Column(String, nullable=True) # Artist profile image
|
|
|
|
# Relationships
|
|
tracks = relationship("Track", secondary=track_artists, back_populates="artists")
|
|
|
|
|
|
class Track(Base):
|
|
__tablename__ = "tracks"
|
|
|
|
id = Column(String, primary_key=True, index=True) # Spotify ID
|
|
reccobeats_id = Column(String, nullable=True, index=True) # ReccoBeats UUID
|
|
name = Column(String)
|
|
artist = Column(
|
|
String
|
|
) # Display string (e.g. "Drake, Future") - kept for convenience
|
|
album = Column(String)
|
|
image_url = Column(String, nullable=True) # Album art
|
|
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) - DEPRECATED in favor of Artist.genres but kept for now
|
|
genres = Column(JSON, nullable=True)
|
|
|
|
# AI Analysis fields
|
|
lyrics = Column(Text, nullable=True) # Full lyrics from Genius
|
|
lyrics_summary = Column(String, nullable=True)
|
|
genre_tags = Column(String, nullable=True)
|
|
|
|
created_at = Column(DateTime, default=datetime.utcnow)
|
|
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
|
|
|
|
plays = relationship("PlayHistory", back_populates="track")
|
|
artists = relationship("Artist", secondary=track_artists, back_populates="tracks")
|
|
|
|
|
|
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)
|
|
context_uri = Column(String, nullable=True)
|
|
|
|
listened_ms = Column(Integer, nullable=True)
|
|
skipped = Column(Boolean, nullable=True)
|
|
source = Column(String, nullable=True)
|
|
|
|
track = relationship("Track", back_populates="plays")
|
|
|
|
|
|
class AnalysisSnapshot(Base):
|
|
"""
|
|
Stores the computed statistics and LLM analysis for a given period.
|
|
Allows for trend analysis over time.
|
|
"""
|
|
|
|
__tablename__ = "analysis_snapshots"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
date = Column(
|
|
DateTime, default=datetime.utcnow, index=True
|
|
) # When the analysis was run
|
|
period_start = Column(DateTime)
|
|
period_end = Column(DateTime)
|
|
period_label = Column(String) # e.g., "last_30_days", "monthly_nov_2023"
|
|
|
|
# The heavy lifting: stored as JSON blobs
|
|
metrics_payload = Column(JSON) # The input to the LLM (StatsService output)
|
|
narrative_report = Column(JSON) # The output from the LLM (NarrativeService output)
|
|
|
|
model_used = Column(String, nullable=True) # e.g. "gemini-1.5-flash"
|
|
playlist_theme = Column(
|
|
String, nullable=True
|
|
) # AI-generated theme name (e.g., "Morning Focus Mode")
|
|
playlist_theme_reasoning = Column(
|
|
Text, nullable=True
|
|
) # AI explanation for why this theme
|
|
six_hour_playlist_id = Column(
|
|
String, nullable=True
|
|
) # Spotify playlist ID for 6-hour playlist
|
|
daily_playlist_id = Column(
|
|
String, nullable=True
|
|
) # Spotify playlist ID for 24-hour playlist
|
|
playlist_composition = Column(JSON, nullable=True)
|
|
playlist_composition = Column(
|
|
JSON, nullable=True
|
|
) # Store the track list at this snapshot
|
|
|
|
|
|
class PlaylistConfig(Base):
|
|
__tablename__ = "playlist_config"
|
|
|
|
key = Column(String, primary_key=True, index=True) # e.g., "six_hour", "daily"
|
|
spotify_id = Column(String, nullable=False)
|
|
last_updated = Column(DateTime, default=datetime.utcnow)
|
|
current_theme = Column(String, nullable=True)
|
|
description = Column(String, nullable=True)
|
|
composition = Column(JSON, nullable=True)
|