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

CREATE VIEW emx_tt_activity_jta_variant_view AS
SELECT
    a.id AS parent_activity_code,
    jp.id 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
    vp.id AS parent_activity_code,
    a.id 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
    a.id 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
    a.id 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
    a.id 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
    a.id 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.id 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", "0091_update_emx_tt_activity_view"),
    ]

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