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_view;

CREATE VIEW emx_tt_activity_view AS
SELECT
    a.id,
    a.code,
    a.name,
    a."desc",
    d.code AS department_code,
    aty.code AS activity_type_code,
    m.code AS module_code,
    CASE a.scheduled_day
        WHEN 0 THEN 'Monday'
        WHEN 1 THEN 'Tuesday'
        WHEN 2 THEN 'Wednesday'
        WHEN 3 THEN 'Thursday'
        WHEN 4 THEN 'Friday'
        WHEN 5 THEN 'Saturday'
        WHEN 6 THEN 'Sunday'
        ELSE NULL
    END AS scheduled_day,
    CASE
        WHEN regexp_match(trim(a.name), '.*/([0-9]+)(?:[[:space:]]*(?:<[^>]*>|\([^)]*\)))?$') IS NULL THEN NULL
        ELSE (regexp_match(trim(a.name), '.*/([0-9]+)(?:[[:space:]]*(?:<[^>]*>|\([^)]*\)))?$'))[1]::integer
    END AS group_id,
    a.duration,
    CASE
        WHEN a.scheduled_start_time IS NULL THEN NULL
        ELSE to_char(
            (CURRENT_DATE + a.scheduled_start_time)::timestamp,
            'FMMM/FMDD/YYYY FMHH12:MI:SS AM'
        )
    END AS start_time,
    CASE
        WHEN a.scheduled_start_time IS NULL OR a.duration IS NULL THEN NULL
        ELSE to_char(
            (CURRENT_DATE + a.scheduled_start_time) + (a.duration * INTERVAL '1 minute'),
            'FMMM/FMDD/YYYY FMHH12:MI:SS AM'
        )
    END AS end_time,
    a.planned_size,
    a.is_jta,
    a.is_variant
FROM tt_activity a
LEFT JOIN tt_activity_type aty ON aty.id = a.activity_type_id
LEFT JOIN tt_department d ON d.id = a.department_id
LEFT JOIN tt_module m ON m.id = a.module_id
WHERE a.is_booking = 0;
"""

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

class Migration(migrations.Migration):

    dependencies = [
        ("api", "0089_insert_setting"),
    ]

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