from django.db import migrations

VIEW_SQL = """
DROP VIEW IF EXISTS emx_tt_activity_date_view;
CREATE VIEW emx_tt_activity_date_view AS
SELECT
    a.code AS activity_code,
    to_char((w.start_date + a.scheduled_day), 'FMDD/FMMM/YYYY') AS activity_date
FROM tt_activity a
INNER JOIN tt_week_pattern_week wpw
    ON wpw.week_pattern_id = a.week_pattern_id
INNER JOIN tt_week w
    ON w.id = wpw.week_id
WHERE a.week_pattern_id IS NOT NULL AND a.scheduled = 1

UNION ALL

SELECT
    a.code AS activity_code,
    to_char((w.start_date + a.scheduled_day), 'FMDD/FMMM/YYYY') AS activity_date
FROM tt_activity a
INNER JOIN tt_activity_week aw
    ON aw.activity_id = a.id
INNER JOIN tt_week w
    ON w.id = aw.week_id
WHERE a.week_pattern_id IS NULL AND a.scheduled = 1;
"""

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

class Migration(migrations.Migration):

    dependencies = [
        ("api", "0082_emxttactivitydatefinal_emxttactivitydatehistory_and_more"),
    ]

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