theovilardo · theovilardo · Dec 17, 2025 · Dec 16, 2025 · Dec 16, 2025 · Dec 16, 2025
diff --git a/.idea/gradle.xml b/.idea/gradle.xml
diff --git a/app/src/main/java/com/theveloper/pixelplay/data/database/MusicDao.kt b/app/src/main/java/com/theveloper/pixelplay/data/database/MusicDao.kt
@@ -288,4 +288,158 @@ interface MusicDao {
     """)
     suspend fun getAudioMetadataById(id: Long): AudioMeta?
 
+    // ===== Song-Artist Cross Reference (Junction Table) Operations =====
+
+    @Insert(onConflict = OnConflictStrategy.IGNORE)
+    suspend fun insertSongArtistCrossRefs(crossRefs: List<SongArtistCrossRef>)
+
+    @Query("DELETE FROM song_artist_cross_ref")
+    suspend fun clearAllSongArtistCrossRefs()
+
+    @Query("DELETE FROM song_artist_cross_ref WHERE song_id = :songId")
+    suspend fun deleteCrossRefsForSong(songId: Long)
+
+    @Query("DELETE FROM song_artist_cross_ref WHERE artist_id = :artistId")
+    suspend fun deleteCrossRefsForArtist(artistId: Long)
+
+    /**
+     * Get all artists for a specific song using the junction table.
+     */
+    @Query("""
+        SELECT artists.* FROM artists
+        INNER JOIN song_artist_cross_ref ON artists.id = song_artist_cross_ref.artist_id
+        WHERE song_artist_cross_ref.song_id = :songId
+        ORDER BY song_artist_cross_ref.is_primary DESC, artists.name ASC
+    """)
+    fun getArtistsForSong(songId: Long): Flow<List<ArtistEntity>>
+
+    /**
+     * Get all artists for a specific song (one-shot).
+     */
+    @Query("""
+        SELECT artists.* FROM artists
+        INNER JOIN song_artist_cross_ref ON artists.id = song_artist_cross_ref.artist_id
+        WHERE song_artist_cross_ref.song_id = :songId
+        ORDER BY song_artist_cross_ref.is_primary DESC, artists.name ASC
+    """)
+    suspend fun getArtistsForSongList(songId: Long): List<ArtistEntity>
+
+    /**
+     * Get all songs for a specific artist using the junction table.
+     */
+    @Query("""
+        SELECT songs.* FROM songs
+        INNER JOIN song_artist_cross_ref ON songs.id = song_artist_cross_ref.song_id
+        WHERE song_artist_cross_ref.artist_id = :artistId
+        ORDER BY songs.title ASC
+    """)
+    fun getSongsForArtist(artistId: Long): Flow<List<SongEntity>>
+
+    /**
+     * Get all songs for a specific artist (one-shot).
+     */
+    @Query("""
+        SELECT songs.* FROM songs
+        INNER JOIN song_artist_cross_ref ON songs.id = song_artist_cross_ref.song_id
+        WHERE song_artist_cross_ref.artist_id = :artistId
+        ORDER BY songs.title ASC
+    """)
+    suspend fun getSongsForArtistList(artistId: Long): List<SongEntity>
+
+    /**
+     * Get the cross-references for a specific song.
+     */
+    @Query("SELECT * FROM song_artist_cross_ref WHERE song_id = :songId")
+    suspend fun getCrossRefsForSong(songId: Long): List<SongArtistCrossRef>
+
+    /**
+     * Get the primary artist for a song.
+     */
+    @Query("""
+        SELECT artists.id AS artist_id, artists.name FROM artists
+        INNER JOIN song_artist_cross_ref ON artists.id = song_artist_cross_ref.artist_id
+        WHERE song_artist_cross_ref.song_id = :songId AND song_artist_cross_ref.is_primary = 1
+        LIMIT 1
+    """)
+    suspend fun getPrimaryArtistForSong(songId: Long): PrimaryArtistInfo?
+
+    /**
+     * Get song count for an artist from the junction table.
+     */
+    @Query("SELECT COUNT(*) FROM song_artist_cross_ref WHERE artist_id = :artistId")
+    suspend fun getSongCountForArtist(artistId: Long): Int
+
+    /**
+     * Get all artists with their song counts computed from the junction table.
+     */
+    @Query("""
+        SELECT artists.id, artists.name, 
+               (SELECT COUNT(*) FROM song_artist_cross_ref WHERE song_artist_cross_ref.artist_id = artists.id) AS track_count
+        FROM artists
+        ORDER BY artists.name ASC
+    """)
+    fun getArtistsWithSongCounts(): Flow<List<ArtistEntity>>
+
+    /**
+     * Get all artists with song counts, filtered by allowed directories.
+     */
+    @Query("""
+        SELECT DISTINCT artists.id, artists.name,
+               (SELECT COUNT(*) FROM song_artist_cross_ref 
+                INNER JOIN songs ON song_artist_cross_ref.song_id = songs.id
+                WHERE song_artist_cross_ref.artist_id = artists.id
+                AND (:applyDirectoryFilter = 0 OR songs.parent_directory_path IN (:allowedParentDirs))) AS track_count
+        FROM artists
+        INNER JOIN song_artist_cross_ref ON artists.id = song_artist_cross_ref.artist_id
+        INNER JOIN songs ON song_artist_cross_ref.song_id = songs.id
+        WHERE (:applyDirectoryFilter = 0 OR songs.parent_directory_path IN (:allowedParentDirs))
+        ORDER BY artists.name ASC
+    """)
+    fun getArtistsWithSongCountsFiltered(
+        allowedParentDirs: List<String>,
+        applyDirectoryFilter: Boolean
+    ): Flow<List<ArtistEntity>>
+
+    /**
+     * Clear all music data including cross-references.
+     */
+    @Transaction
+    suspend fun clearAllMusicDataWithCrossRefs() {
+        clearAllSongArtistCrossRefs()
+        clearAllSongs()
+        clearAllAlbums()
+        clearAllArtists()
+    }
+
+    /**
+     * Insert music data with cross-references in a single transaction.
+     * Uses chunked inserts for cross-refs to avoid SQLite variable limits.
+     */
+    @Transaction
+    suspend fun insertMusicDataWithCrossRefs(
+        songs: List<SongEntity>,
+        albums: List<AlbumEntity>,
+        artists: List<ArtistEntity>,
+        crossRefs: List<SongArtistCrossRef>
+    ) {
+        insertArtists(artists)
+        insertAlbums(albums)
+        insertSongs(songs)
+        // Insert cross-refs in chunks to avoid SQLite variable limit.
+        // Each SongArtistCrossRef has 3 fields, so batch size is calculated accordingly.
+        crossRefs.chunked(CROSS_REF_BATCH_SIZE).forEach { chunk ->
+            insertSongArtistCrossRefs(chunk)
+        }
+    }
+
+    companion object {
+        /**
+         * SQLite has a limit on the number of variables per statement (default 999, higher in newer versions).
+         * Each SongArtistCrossRef insert uses 3 variables (songId, artistId, isPrimary).
+         * The batch size is calculated so that batchSize * 3 <= SQLITE_MAX_VARIABLE_NUMBER.
+         */
+        private const val SQLITE_MAX_VARIABLE_NUMBER = 999 // Increase if you know your SQLite version supports more
+        private const val CROSS_REF_FIELDS_PER_OBJECT = 3
+        val CROSS_REF_BATCH_SIZE: Int = SQLITE_MAX_VARIABLE_NUMBER / CROSS_REF_FIELDS_PER_OBJECT
+    }
 }
diff --git a/app/src/main/java/com/theveloper/pixelplay/data/database/PixelPlayDatabase.kt b/app/src/main/java/com/theveloper/pixelplay/data/database/PixelPlayDatabase.kt
@@ -13,9 +13,10 @@ import androidx.sqlite.db.SupportSQLiteDatabase
         SongEntity::class,
         AlbumEntity::class,
         ArtistEntity::class,
-        TransitionRuleEntity::class
+        TransitionRuleEntity::class,
+        SongArtistCrossRef::class
     ],
-    version = 9, // Incremented version for audio metadata cols
+    version = 10, // Incremented version for multi-artist support
     exportSchema = false
 )
 abstract class PixelPlayDatabase : RoomDatabase() {
@@ -49,5 +50,36 @@ abstract class PixelPlayDatabase : RoomDatabase() {
                 db.execSQL("ALTER TABLE songs ADD COLUMN sample_rate INTEGER")
             }
         }
+
+        val MIGRATION_9_10 = object : Migration(9, 10) {
+            override fun migrate(db: SupportSQLiteDatabase) {
+                // Add album_artist column to songs table
+                db.execSQL("ALTER TABLE songs ADD COLUMN album_artist TEXT DEFAULT NULL")
+
+                // Create song_artist_cross_ref junction table for many-to-many relationship
+                db.execSQL("""
+                    CREATE TABLE IF NOT EXISTS song_artist_cross_ref (
+                        song_id INTEGER NOT NULL,
+                        artist_id INTEGER NOT NULL,
+                        is_primary INTEGER NOT NULL DEFAULT 0,
+                        PRIMARY KEY (song_id, artist_id),
+                        FOREIGN KEY (song_id) REFERENCES songs(id) ON DELETE CASCADE,
+                        FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
+                    )
+                """.trimIndent())
+
+                // Create indices for efficient queries
+                db.execSQL("CREATE INDEX IF NOT EXISTS index_song_artist_cross_ref_song_id ON song_artist_cross_ref(song_id)")
+                db.execSQL("CREATE INDEX IF NOT EXISTS index_song_artist_cross_ref_artist_id ON song_artist_cross_ref(artist_id)")
+                db.execSQL("CREATE INDEX IF NOT EXISTS index_song_artist_cross_ref_is_primary ON song_artist_cross_ref(is_primary)")
+
+                // Migrate existing song-artist relationships to junction table
+                // Each existing song gets its current artist as the primary artist
+                db.execSQL("""
+                    INSERT OR REPLACE INTO song_artist_cross_ref (song_id, artist_id, is_primary)
+                    SELECT id, artist_id, 1 FROM songs WHERE artist_id IS NOT NULL
+                """.trimIndent())
+            }
+        }
     }
 }
diff --git a/app/src/main/java/com/theveloper/pixelplay/data/database/SongArtistCrossRef.kt b/app/src/main/java/com/theveloper/pixelplay/data/database/SongArtistCrossRef.kt
@@ -0,0 +1,87 @@
+package com.theveloper.pixelplay.data.database
+
+import androidx.room.ColumnInfo
+import androidx.room.Embedded
+import androidx.room.Entity
+import androidx.room.ForeignKey
+import androidx.room.Index
+import androidx.room.Junction
+import androidx.room.Relation
+
+/**
+ * Junction table for many-to-many relationship between songs and artists.
+ * Enables multi-artist support where a song can have multiple artists
+ * and an artist can have multiple songs.
+ */
+@Entity(
+    tableName = "song_artist_cross_ref",
+    primaryKeys = ["song_id", "artist_id"],
+    indices = [
+        Index(value = ["song_id"]),
+        Index(value = ["artist_id"]),
+        Index(value = ["is_primary"])
+    ],
+    foreignKeys = [
+        ForeignKey(
+            entity = SongEntity::class,
+            parentColumns = ["id"],
+            childColumns = ["song_id"],
+            onDelete = ForeignKey.CASCADE
+        ),
+        ForeignKey(
+            entity = ArtistEntity::class,
+            parentColumns = ["id"],
+            childColumns = ["artist_id"],
+            onDelete = ForeignKey.CASCADE
+        )
+    ]
+)
+data class SongArtistCrossRef(
+    @ColumnInfo(name = "song_id") val songId: Long,
+    @ColumnInfo(name = "artist_id") val artistId: Long,
+    @ColumnInfo(name = "is_primary", defaultValue = "0") val isPrimary: Boolean = false
+)
+
+/**
+ * Data class representing a song with all its associated artists.
+ * Used for queries that need to retrieve a song along with its artists.
+ */
+data class SongWithArtists(
+    @Embedded val song: SongEntity,
+    @Relation(
+        parentColumn = "id",
+        entityColumn = "id",
+        associateBy = Junction(
+            value = SongArtistCrossRef::class,
+            parentColumn = "song_id",
+            entityColumn = "artist_id"
+        )
+    )
+    val artists: List<ArtistEntity>
+)
+
+/**
+ * Data class representing an artist with all their songs.
+ * Used for queries that need to retrieve an artist along with their songs.
+ */
+data class ArtistWithSongs(
+    @Embedded val artist: ArtistEntity,
+    @Relation(
+        parentColumn = "id",
+        entityColumn = "id",
+        associateBy = Junction(
+            value = SongArtistCrossRef::class,
+            parentColumn = "artist_id",
+            entityColumn = "song_id"
+        )
+    )
+    val songs: List<SongEntity>
+)
+
+/**
+ * Data class for retrieving the primary artist of a song efficiently.
+ */
+data class PrimaryArtistInfo(
+    @ColumnInfo(name = "artist_id") val artistId: Long,
+    @ColumnInfo(name = "name") val artistName: String
+)