# -*- coding: utf-8 -*-
# Generated by Django 1.11.15 on 2018-10-01 09:21
from __future__ import unicode_literals

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


class Migration(migrations.Migration):

    forward = [
        """
        DROP INDEX IF EXISTS campusonline_person_hash_idx;
        """,
        """
        DROP MATERIALIZED VIEW IF EXISTS "public"."campusonline_person";
        """,
        """
        DROP FOREIGN TABLE IF EXISTS "campusonline"."personen";
        """,
        """
        CREATE FOREIGN TABLE "campusonline"."personen" (
            PERS_NR numeric,
            PERS_VORNAME varchar,
            PERS_FAMNAM varchar,
            PERS_TITEL varchar,
            PERS_SEX varchar,
            PERS_BENUTZERNAME varchar,
            PERS_SPRECHSTUNDE varchar,
            PERS_ZUSATZ_INFO varchar,
            PERS_PROFILBILD varchar,
            RAUM_NR numeric,
            PERS_EMAIL varchar
        )
        SERVER sqlalchemy OPTIONS (
            tablename 'PERSON_V',
            db_url '{}'
        );
        """.format(
            settings.MULTICORN.get("campusonline")
        ),
        """
        CREATE MATERIALIZED VIEW "public"."campusonline_person" AS SELECT
            p.pers_nr::integer AS id,
            p.pers_vorname AS first_name,
            p.pers_famnam AS last_name,
            p.pers_titel AS title,
            p.pers_sex AS sex,
            p.pers_benutzername AS username,
            p.pers_sprechstunde AS consultation,
            p.pers_zusatz_info AS appendix,
            p.pers_profilbild AS avatar,
            p.pers_email AS email,
            p.raum_nr::integer AS room_id,
            ppd.content AS avatar_private,
            CASE
                ppd.content
            WHEN
                NULL
            THEN
                NULL
            ELSE
                encode(digest(format('%s-%s-', p.pers_nr, p.pers_benutzername)::bytea || ppd.content, 'sha1'), 'hex')
            END AS hash
        FROM
            "campusonline"."personen" p
        LEFT JOIN
            "campusonline"."personen_profilbilder_daten" ppd
        ON
            p.pers_nr::integer = ppd.person_nr::integer
        WITH DATA;
        """,
        """
        CREATE UNIQUE INDEX campusonline_person_id_idx ON "public"."campusonline_person" ("id");
        """,
        """
        CREATE UNIQUE INDEX campusonline_person_email_idx ON "public"."campusonline_person" ("email");
        """,
        """
        CREATE UNIQUE INDEX campusonline_person_hash_idx ON "public"."campusonline_person" ("hash");
        """,
        """
        CREATE INDEX campusonline_person_sex_idx ON "public"."campusonline_person" ("sex");
        """,
        """
        CREATE INDEX campusonline_person_first_name_idx ON "public"."campusonline_person" ("first_name");
        """,
        """
        CREATE INDEX campusonline_person_last_name_idx ON "public"."campusonline_person" ("last_name");
        """,
    ]
    reverse = [
        """
        DROP INDEX IF EXISTS campusonline_person_last_name_idx;
        """,
        """
        DROP INDEX IF EXISTS campusonline_person_first_name_idx;
        """,
        """
        DROP INDEX IF EXISTS campusonline_person_sex_idx;
        """,
        """
        DROP INDEX IF EXISTS campusonline_person_hash_idx;
        """,
        """
        DROP INDEX IF EXISTS campusonline_person_email_idx;
        """,
        """
        DROP INDEX IF EXISTS campusonline_person_id_idx;
        """,
        """
        DROP MATERIALIZED VIEW IF EXISTS "public"."campusonline_person";
        """,
        """
        DROP FOREIGN TABLE IF EXISTS "campusonline"."personen";
        """,
        """
        CREATE FOREIGN TABLE "campusonline"."personen" (
            PERS_NR numeric,
            PERS_VORNAME varchar,
            PERS_FAMNAM varchar,
            PERS_TITEL varchar,
            PERS_SEX varchar,
            PERS_BENUTZERNAME varchar,
            PERS_SPRECHSTUNDE varchar,
            PERS_ZUSATZ_INFO varchar,
            PERS_PROFILBILD varchar,
            RAUM_NR numeric
        )
        SERVER sqlalchemy OPTIONS (
            tablename 'PERSON_V',
            db_url '{}'
        );
        """.format(
            settings.MULTICORN.get("campusonline")
        ),
        """
        CREATE MATERIALIZED VIEW "public"."campusonline_person" AS SELECT
            pers_nr ::integer AS id,
            pers_vorname AS first_name,
            pers_famnam AS last_name,
            pers_titel AS title,
            pers_sex AS sex,
            pers_benutzername AS username,
            pers_sprechstunde AS consultation,
            pers_zusatz_info AS appendix,
            pers_profilbild AS avatar,
            raum_nr ::integer AS room_id
        FROM "campusonline"."personen"
        WITH DATA;
        """,
        """
        CREATE MATERIALIZED VIEW "public"."campusonline_person" AS SELECT
            p.pers_nr::integer AS id,
            p.pers_vorname AS first_name,
            p.pers_famnam AS last_name,
            p.pers_titel AS title,
            p.pers_sex AS sex,
            p.pers_benutzername AS username,
            p.pers_sprechstunde AS consultation,
            p.pers_zusatz_info AS appendix,
            p.pers_profilbild AS avatar,
            p.raum_nr::integer AS room_id,
            ppd.content AS avatar_private,
            CASE
                ppd.content
            WHEN
                NULL
            THEN
                NULL
            ELSE
                encode(digest(format('%s-%s-', p.pers_nr, p.pers_benutzername)::bytea || ppd.content, 'sha1'), 'hex')
            END AS hash
        FROM
            "campusonline"."personen" p
        LEFT JOIN
            "campusonline"."personen_profilbilder_daten" ppd
        ON
            p.pers_nr::integer = ppd.person_nr::integer
        WITH DATA;
        """,
        """
        CREATE UNIQUE INDEX campusonline_person_hash_idx ON "public"."campusonline_person" ("hash");
        """,
    ]

    dependencies = [("campusonline", "0033_person_avatar_clob")]

    operations = [migrations.RunSQL(forward, reverse)]
