database_manager.py

import sqlite3
from song import Song

class DatabaseManager:
    def __init__(self, db_path="database.db"):
        self.db_path = db_path
        
    def get_connection(self):
        """Get a database connection"""
        return sqlite3.connect(self.db_path)
    
    def get_all_songs(self):
        """Retrieve all songs from the database"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT songID, songName, artist, genre, albumName, duration FROM songs")
        rows = cursor.fetchall()
        
        songs = []
        for row in rows:
            # Create Song objects with available data
            song = Song(
                track_id=row[0],
                title=row[1], 
                artist_name=row[2],
                genre=row[3],
                mood_tags=[],  # Not in current DB schema
                valence=0.5,   # Default values for missing attributes
                energy=0.5,
                danceability=0.5,
                tempo=120,
                duration=row[5],
                preview_url="",
                cover_image="",
                album_name=row[4],
                release_date=""
            )
            songs.append(song)
        
        conn.close()
        return songs
    
    def get_songs_by_genre(self, genre):
        """Get songs filtered by genre"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT songID, songName, artist, genre, albumName, duration FROM songs WHERE genre = ?", (genre,))
        rows = cursor.fetchall()
        
        songs = []
        for row in rows:
            song = Song(
                track_id=row[0],
                title=row[1], 
                artist_name=row[2],
                genre=row[3],
                mood_tags=[],
                valence=0.5,
                energy=0.5,
                danceability=0.5,
                tempo=120,
                duration=row[5],
                preview_url="",
                cover_image="",
                album_name=row[4],
                release_date=""
            )
            songs.append(song)
        
        conn.close()
        return songs
    
    def get_all_genres(self):
        """Get list of all unique genres"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT DISTINCT genre FROM songs ORDER BY genre")
        rows = cursor.fetchall()
        
        conn.close()
        return [row[0] for row in rows]
    
    def get_all_albums(self):
        """Get list of all unique albums with song counts"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT albumName, COUNT(*) as song_count, genre, artist
            FROM songs 
            GROUP BY albumName, artist 
            ORDER BY albumName
        """)
        rows = cursor.fetchall()
        
        conn.close()
        return rows
    
    def get_songs_by_album(self, album_name):
        """Get all songs from a specific album"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT songID, songName, artist, genre, albumName, duration FROM songs WHERE albumName = ?", (album_name,))
        rows = cursor.fetchall()
        
        songs = []
        for row in rows:
            song = Song(
                track_id=row[0],
                title=row[1], 
                artist_name=row[2],
                genre=row[3],
                mood_tags=[],
                valence=0.5,
                energy=0.5,
                danceability=0.5,
                tempo=120,
                duration=row[5],
                preview_url="",
                cover_image="",
                album_name=row[4],
                release_date=""
            )
            songs.append(song)
        
        conn.close()
        return songs
    
    def get_song_data_dict(self):
        """Get a dictionary mapping song names to artists"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT songName, artist FROM songs")
        rows = cursor.fetchall()
        
        conn.close()
        return {row[0]: row[1] for row in rows}
    
    def get_album_data_dict(self):
        """Get a dictionary mapping album names to lists of songs"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT albumName, songName FROM songs ORDER BY albumName, songName")
        rows = cursor.fetchall()
        
        album_dict = {}
        for album, song in rows:
            if album not in album_dict:
                album_dict[album] = []
            album_dict[album].append(song)
        
        conn.close()
        return album_dict
    
    def search_songs(self, query):
        """Search for songs by name, artist, or album"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        search_pattern = f"%{query}%"
        cursor.execute("""
            SELECT songID, songName, artist, genre, albumName, duration 
            FROM songs 
            WHERE songName LIKE ? OR artist LIKE ? OR albumName LIKE ?
            ORDER BY songName
        """, (search_pattern, search_pattern, search_pattern))
        
        rows = cursor.fetchall()
        
        songs = []
        for row in rows:
            song = Song(
                track_id=row[0],
                title=row[1], 
                artist_name=row[2],
                genre=row[3],
                mood_tags=[],
                valence=0.5,
                energy=0.5,
                danceability=0.5,
                tempo=120,
                duration=row[5],
                preview_url="",
                cover_image="",
                album_name=row[4],
                release_date=""
            )
            songs.append(song)
        
        conn.close()
        return songs