from django.db import migrations

# all activity code is changed to used id to send, so is confirm unique
VIEW_SQL = """
DROP VIEW IF EXISTS emx_tt_activity_view;

CREATE VIEW emx_tt_activity_view AS
SELECT
    'MV' || a.id::text AS 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]+)') IS NULL THEN NULL
        ELSE (regexp_match(trim(a.name), '.*/([0-9]+)'))[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;

DROP VIEW IF EXISTS emx_tt_activity_jta_variant_view;

CREATE VIEW emx_tt_activity_jta_variant_view AS
SELECT
    'MV' || a.id::text AS parent_activity_code,
    'MV' || jp.id::text AS child_activity_code
FROM tt_activity a
INNER JOIN tt_activity jp
    ON jp.id = a.jta_parent_id
WHERE a.jta_parent_id IS NOT NULL AND a.is_booking = 0

UNION ALL

SELECT
    'MV' || vp.id::text AS parent_activity_code,
    'MV' || a.id::text AS child_activity_code
FROM tt_activity a
INNER JOIN tt_activity vp
    ON vp.id = a.variant_parent_id
WHERE a.variant_parent_id IS NOT NULL AND a.is_booking = 0;

DROP VIEW IF EXISTS emx_tt_activity_location_view;

CREATE VIEW emx_tt_activity_location_view AS
SELECT
    'MV' || a.id::text AS activity_code,
    l.code AS location_code
FROM tt_activity_location al
LEFT JOIN tt_activity a ON a.id = al.activity_id
LEFT JOIN tt_location l ON l.id = al.location_id
WHERE a.is_booking = 0
  AND a.scheduled = 1;

DROP VIEW IF EXISTS emx_tt_activity_staff_view;

CREATE VIEW emx_tt_activity_staff_view AS
SELECT
    'MV' || a.id::text AS activity_code,
    s.code AS staff_code
FROM tt_activity_staff ast
LEFT JOIN tt_activity a ON a.id = ast.activity_id
LEFT JOIN tt_staff s ON s.id = ast.staff_id
WHERE a.is_booking = 0
  AND a.scheduled = 1;

DROP VIEW IF EXISTS emx_tt_activity_pos_view;
CREATE VIEW emx_tt_activity_pos_view AS
SELECT DISTINCT
    'MV' || a.id::text 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;

DROP VIEW IF EXISTS emx_tt_activity_date_view;
CREATE VIEW emx_tt_activity_date_view AS
SELECT
    'MV' || a.id::text 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
    'MV' || a.id::text 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;
"""

class Migration(migrations.Migration):

    dependencies = [
        ("api", "0092_emx_tt_activity_pos_view"),
    ]

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