from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [
        ('mysite', '0001_initial'),
    ]
    operations = [
        migrations.RunSQL("""
            CREATE OR REPLACE VIEW song_details_view AS
            SELECT 
                s.song_id,
                s.title,
                a.album_name,
                m.name AS musician_name,
                s.duration_sec,
                g.genre_name,
                ROUND(AVG(r.score), 2) AS avg_rating
            FROM song s
            JOIN album a ON s.album_id = a.album_id
            JOIN musician m ON a.musician_id = m.musician_id
            LEFT JOIN genre g ON s.genre_id = g.genre_id
            LEFT JOIN rating r ON s.song_id = r.song_id
            GROUP BY s.song_id, a.album_name, m.name, g.genre_name;
        """),
        
        migrations.RunSQL("""
            CREATE OR REPLACE FUNCTION update_rating_timestamp()
            RETURNS TRIGGER AS $$
            BEGIN
                NEW.rating_date = CURRENT_DATE;
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
            
            CREATE TRIGGER trg_update_rating_date
            BEFORE UPDATE ON rating
            FOR EACH ROW
            EXECUTE FUNCTION update_rating_timestamp();
        """),
    ]