# -*- coding: utf-8 -*-
# Generated by Django 1.11.20 on 2019-04-25 06:50
from __future__ import unicode_literals

from django.db import migrations
from django.conf import settings


class Migration(migrations.Migration):

    ops = [
        (
            '''
            CREATE FOREIGN TABLE "campusonline"."abschlussarbeiten" (
                ID numeric,
                STUDIENBEZEICHNUNG varchar,
                LETZTE_AENDERUNG timestamp,
                AUTOR_ID numeric,
                VORNAME_AUTOR varchar,
                NACHNAME_AUTOR varchar,
                AUTOREN varchar,
                TITEL varchar,
                KURZFASSUNG text,
                SEITEN_ANZAHL numeric,
                BETREUER_ID numeric,
                BETREUER varchar,
                ERSCHEINUNGSJAHR numeric,
                LINK varchar,
                WERK_TYP numeric,
                PUBLIZIERT varchar,
                ORGANISATION numeric,
                ORGANISATIONS_ID numeric
            )
            SERVER sqlalchemy OPTIONS (
                tablename 'F_ABSCHLUSSARBEIT',
                db_url '{}'
            );
            '''.format(settings.MULTICORN.get('campusonline')),
            '''
            DROP FOREIGN TABLE IF EXISTS "campusonline"."abschlussarbeiten";
            ''',
        ),
        (
            '''
            CREATE MATERIALIZED VIEW "public"."campusonline_finalthesis" AS SELECT
                aa.ID::integer AS id,
                aa.STUDIENBEZEICHNUNG AS study_designation,
                aa.LETZTE_AENDERUNG::timestamptz AS modified,
                s.STUD_NR::integer AS author_id,
                regexp_split_to_array(trim(both ' ' from aa.AUTOREN), ';\s*') AS authors,
                aa.TITEL AS title,
                aa.KURZFASSUNG AS abstract,
                aa.SEITEN_ANZAHL::integer AS pages,
                p.PERS_NR::integer AS tutor_id,
                aa.ERSCHEINUNGSJAHR::integer AS year,
                aa.LINK AS url,
                aa.PUBLIZIERT AS category,
                o.NR::integer AS organization_id
            FROM "campusonline"."abschlussarbeiten" aa
            INNER JOIN "campusonline"."stud" s
                ON aa.AUTOR_ID::integer = s.STUD_NR::integer
            INNER JOIN "campusonline"."personen" p
                ON aa.BETREUER_ID::integer = p.PERS_NR::integer
            INNER JOIN "campusonline"."organisationen" o
                ON aa.ORGANISATIONS_ID::integer = o.NR::integer
            WITH DATA;
            ''',
            '''
            DROP MATERIALIZED VIEW IF EXISTS "public"."campusonline_finalthesis";
            ''',
        ),
        (
            '''
            CREATE INDEX campusonline_finalthesis_id_idx ON "public"."campusonline_finalthesis" ("id");
            ''',
            '''
            DROP INDEX IF EXISTS campusonline_finalthesis_id_idx;
            ''',
        ),
        (
            '''
            CREATE INDEX campusonline_finalthesis_modified_idx ON "public"."campusonline_finalthesis" ("modified");
            ''',
            '''
            DROP INDEX IF EXISTS campusonline_finalthesis_modified_idx;
            ''',
        ),
        (
            '''
            CREATE INDEX campusonline_finalthesis_author_id_idx ON "public"."campusonline_finalthesis" ("author_id");
            ''',
            '''
            DROP INDEX IF EXISTS campusonline_finalthesis_author_id_idx;
            ''',
        ),
        (
            '''
            CREATE INDEX campusonline_finalthesis_tutor_id_idx ON "public"."campusonline_finalthesis" ("tutor_id");
            ''',
            '''
            DROP INDEX IF EXISTS campusonline_finalthesis_tutor_id_idx;
            ''',
        ),
        (
            '''
            CREATE INDEX campusonline_finalthesis_year_idx ON "public"."campusonline_finalthesis" ("year");
            ''',
            '''
            DROP INDEX IF EXISTS campusonline_finalthesis_year_idx;
            ''',
        ),
        (
            '''
            CREATE INDEX campusonline_finalthesis_organization_id_idx ON "public"."campusonline_finalthesis" ("organization_id");
            ''',
            '''
            DROP INDEX IF EXISTS campusonline_finalthesis_organization_id_idx;
            ''',
        ),
    ]

    dependencies = [
        ('campusonline', '0046_student_username'),
    ]

    operations = [
        migrations.RunSQL(
            [forward for forward, reverse in ops],
            [reverse for forward, reverse in reversed(ops)]
        )
    ]
