# -*- coding: utf-8 -*-
# Generated by Django 1.11 on 2017-05-19 16:10
from __future__ import unicode_literals

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('reqs', '0030_auto_20170517_2035'),
    ]

    operations = [
        migrations.RunSQL("""
            CREATE VIEW reqs_requirement_all_agencies AS (
                --- Direct connection
                (SELECT
                    -- prefix with letters to avoid conflicts
                    'a' || id AS id, requirement_id, agency_id
                    FROM reqs_requirement_agencies
                )
                UNION
                --- Through agency groups
                (SELECT 
                    -- prefix with letters to avoid conflicts
                    'g' || rrag.id || 'a' || rag.id, requirement_id, agency_id
                    FROM reqs_requirement_agency_groups rrag
                    INNER JOIN reqs_agencygroup_agencies rag
                        ON rrag.agencygroup_id = rag.agencygroup_id
                )
            )
            """,
            "DROP VIEW reqs_requirement_all_agencies"
        ),
    ]
