from django.db import migrations

# the pos code is get from student set pos, which is if the student set have join the activity, will
VIEW_SQL = """
DROP VIEW IF EXISTS emx_tt_activity_pos_view;
CREATE VIEW emx_tt_activity_pos_view AS
SELECT DISTINCT
    a.code AS activity_code,
    p.code AS pos_code
FROM tt_activity a
INNER JOIN tt_student_set_activity ssa
    ON a.id = ssa.activity_id
INNER JOIN tt_student_set ss
    ON ssa.student_set_id = ss.id
INNER JOIN tt_pos p
    ON ss.pos_id = p.id
WHERE p.code IS NOT NULL;
"""

REVERSE_SQL = """
DROP VIEW IF EXISTS emx_tt_activity_date_view;
"""

class Migration(migrations.Migration):

    dependencies = [
        ("api", "0084_emxttactivityposfinal_emxttactivityposhistory_and_more"),
    ]

    operations = [
        migrations.RunSQL(
            sql=VIEW_SQL,
            reverse_sql=REVERSE_SQL,
        ),
    ]