-- Fix missing columns and constraints for AfricanMusic app
-- Version: 1.0

-- Add missing columns to tracks table if they don't exist
DO $$ 
BEGIN
    -- Add play_count as an alias/computed column (optional, we use total_streams)
    -- This is just for documentation - we'll use total_streams AS play_count in queries
    
    -- Add ringtone-related columns if they don't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name='tracks' AND column_name='ringtone_enabled') THEN
        ALTER TABLE tracks ADD COLUMN ringtone_enabled BOOLEAN DEFAULT FALSE;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name='tracks' AND column_name='ringtone_price') THEN
        ALTER TABLE tracks ADD COLUMN ringtone_price INTEGER DEFAULT 0;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name='tracks' AND column_name='ringtone_duration') THEN
        ALTER TABLE tracks ADD COLUMN ringtone_duration INTEGER DEFAULT 30;
    END IF;

    -- Add genre_id column for easier joins (many-to-many still exists via track_genres)
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name='tracks' AND column_name='genre_id') THEN
        ALTER TABLE tracks ADD COLUMN genre_id UUID REFERENCES genres(id) ON DELETE SET NULL;
        
        -- Populate genre_id with the first genre from track_genres
        UPDATE tracks t
        SET genre_id = (
            SELECT tg.genre_id 
            FROM track_genres tg 
            WHERE tg.track_id = t.id 
            LIMIT 1
        );
    END IF;
END $$;

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_tracks_ringtone_enabled ON tracks(ringtone_enabled) WHERE ringtone_enabled = TRUE;
CREATE INDEX IF NOT EXISTS idx_tracks_genre_id ON tracks(genre_id);
CREATE INDEX IF NOT EXISTS idx_tracks_total_streams ON tracks(total_streams DESC);

-- Add comment to clarify play_count vs total_streams
COMMENT ON COLUMN tracks.total_streams IS 'Total number of times this track has been streamed. Used as play_count in API responses.';

COMMENT ON TABLE tracks IS 'Tracks table. Note: play_count is accessed via total_streams column in queries.';