# -*- coding: utf-8 -*-

# Copyright 2010 - 2017 RhodeCode GmbH and the AppEnlight project authors
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

"""initial tables

Revision ID: 55b6e612672f
Revises: None
Create Date: 2014-10-13 23:47:38.295159

"""

# revision identifiers, used by Alembic.
revision = "55b6e612672f"
down_revision = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.add_column("users", sa.Column("first_name", sa.Unicode(25)))
    op.add_column("users", sa.Column("last_name", sa.Unicode(50)))
    op.add_column("users", sa.Column("company_name", sa.Unicode(255)))
    op.add_column("users", sa.Column("company_address", sa.Unicode(255)))
    op.add_column("users", sa.Column("phone1", sa.Unicode(25)))
    op.add_column("users", sa.Column("phone2", sa.Unicode(25)))
    op.add_column("users", sa.Column("zip_code", sa.Unicode(25)))
    op.add_column(
        "users",
        sa.Column(
            "default_report_sort",
            sa.Unicode(20),
            nullable=False,
            server_default="newest",
        ),
    )
    op.add_column("users", sa.Column("city", sa.Unicode(128)))
    op.add_column("users", sa.Column("notes", sa.UnicodeText, server_default=""))
    op.add_column(
        "users",
        sa.Column("notifications", sa.Boolean(), nullable=False, server_default="true"),
    )
    op.add_column(
        "users",
        sa.Column("registration_ip", sa.Unicode(40), nullable=False, server_default=""),
    )

    op.create_table(
        "integrations",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "resource_id",
            sa.Integer(),
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
        ),
        sa.Column("integration_name", sa.Unicode(64)),
        sa.Column("config", sa.dialects.postgresql.JSON, nullable=False),
        sa.Column(
            "modified_date", sa.DateTime(), nullable=False, server_default=sa.func.now()
        ),
        sa.Column("external_id", sa.Unicode(255)),
        sa.Column("external_id2", sa.Unicode(255)),
    )

    op.create_table(
        "alert_channels",
        sa.Column(
            "owner_id",
            sa.Integer(),
            sa.ForeignKey("users.id", onupdate="cascade", ondelete="cascade"),
            nullable=False,
        ),
        sa.Column("channel_name", sa.Unicode(25), nullable=False),
        sa.Column("channel_value", sa.Unicode(80), nullable=False),
        sa.Column("channel_json_conf", sa.dialects.postgresql.JSON, nullable=False),
        sa.Column(
            "channel_validated", sa.Boolean, nullable=False, server_default="False"
        ),
        sa.Column("send_alerts", sa.Boolean, nullable=False, server_default="True"),
        sa.Column(
            "notify_only_first", sa.Boolean, nullable=False, server_default="False"
        ),
        sa.Column("daily_digest", sa.Boolean, nullable=False, server_default="True"),
        sa.Column("pkey", sa.Integer(), primary_key=True),
        sa.Column(
            "integration_id",
            sa.Integer,
            sa.ForeignKey("integrations.id", onupdate="cascade", ondelete="cascade"),
        ),
    )
    op.create_unique_constraint(
        "uq_alert_channels",
        "alert_channels",
        ["owner_id", "channel_name", "channel_value"],
    )

    op.create_table(
        "alert_channels_actions",
        sa.Column("owner_id", sa.Integer(), nullable=False),
        sa.Column(
            "resource_id",
            sa.Integer(),
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
        ),
        sa.Column("pkey", sa.Integer(), primary_key=True),
        sa.Column("action", sa.Unicode(10), nullable=False, server_default="always"),
        sa.Column("rule", sa.dialects.postgresql.JSON),
        sa.Column("type", sa.Unicode(10), index=True),
        sa.Column("other_id", sa.Unicode(40), index=True),
        sa.Column("config", sa.dialects.postgresql.JSON),
        sa.Column("name", sa.Unicode(255), server_default=""),
    )

    op.create_table(
        "application_postprocess_conf",
        sa.Column("pkey", sa.Integer(), primary_key=True),
        sa.Column("do", sa.Unicode(25), nullable=False),
        sa.Column("new_value", sa.UnicodeText(), nullable=False, server_default=""),
        sa.Column(
            "resource_id",
            sa.Integer(),
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
            nullable=False,
        ),
        sa.Column("rule", sa.dialects.postgresql.JSON),
    )

    op.create_table(
        "applications",
        sa.Column(
            "resource_id",
            sa.Integer(),
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
            nullable=False,
            primary_key=True,
            autoincrement=False,
        ),
        sa.Column("domains", sa.UnicodeText, nullable=False),
        sa.Column("api_key", sa.Unicode(32), nullable=False, index=True),
        sa.Column(
            "default_grouping",
            sa.Unicode(20),
            nullable=False,
            server_default="url_type",
        ),
        sa.Column("public_key", sa.Unicode(32), nullable=False, index=True),
        sa.Column(
            "error_report_threshold", sa.Integer(), server_default="10", nullable=False
        ),
        sa.Column(
            "slow_report_threshold", sa.Integer(), server_default="10", nullable=False
        ),
        sa.Column("apdex_threshold", sa.Float(), server_default="0.7", nullable=False),
        sa.Column(
            "allow_permanent_storage",
            sa.Boolean(),
            server_default="false",
            nullable=False,
        ),
    )
    op.create_unique_constraint(None, "applications", ["public_key"])
    op.create_unique_constraint(None, "applications", ["api_key"])

    op.create_table(
        "metrics",
        sa.Column("pkey", sa.types.BigInteger, nullable=False, primary_key=True),
        sa.Column(
            "resource_id",
            sa.Integer(),
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
        ),
        sa.Column("timestamp", sa.DateTime),
        sa.Column("namespace", sa.Unicode(255)),
        sa.Column("tags", sa.dialects.postgresql.JSON, server_default="{}"),
    )

    op.create_table(
        "events",
        sa.Column("id", sa.Integer, nullable=False, primary_key=True),
        sa.Column("start_date", sa.DateTime, nullable=False, index=True),
        sa.Column("end_date", sa.DateTime),
        sa.Column("status", sa.Integer(), nullable=False, index=True),
        sa.Column("event_type", sa.Integer(), nullable=False, index=True),
        sa.Column("origin_user_id", sa.Integer()),
        sa.Column("target_user_id", sa.Integer()),
        sa.Column("resource_id", sa.Integer(), index=True),
        sa.Column("text", sa.UnicodeText, server_default=""),
        sa.Column("values", sa.dialects.postgresql.JSON),
        sa.Column("target_id", sa.Integer()),
        sa.Column("target_uuid", sa.Unicode(40), index=True),
    )

    op.create_table(
        "logs",
        sa.Column("log_id", sa.types.BigInteger, nullable=False, primary_key=True),
        sa.Column(
            "resource_id",
            sa.Integer(),
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
        ),
        sa.Column("log_level", sa.SmallInteger(), nullable=False),
        sa.Column("primary_key", sa.Unicode(128), nullable=True),
        sa.Column("message", sa.UnicodeText, nullable=False, server_default=""),
        sa.Column("timestamp", sa.DateTime),
        sa.Column("namespace", sa.Unicode(255)),
        sa.Column("request_id", sa.Unicode(40)),
        sa.Column("tags", sa.dialects.postgresql.JSON, server_default="{}"),
        sa.Column("permanent", sa.Boolean(), server_default="false", nullable=False),
    )

    op.create_table(
        "reports_groups",
        sa.Column("id", sa.types.BigInteger, primary_key=True),
        sa.Column(
            "resource_id",
            sa.Integer,
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
            nullable=False,
        ),
        sa.Column("priority", sa.Integer, nullable=False, server_default="5"),
        sa.Column(
            "first_timestamp",
            sa.DateTime(),
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column("last_timestamp", sa.DateTime()),
        sa.Column("error", sa.UnicodeText, nullable=False, server_default=""),
        sa.Column("grouping_hash", sa.Unicode(40), nullable=False, server_default=""),
        sa.Column(
            "triggered_postprocesses_ids",
            sa.dialects.postgresql.JSON,
            nullable=False,
            server_default="[]",
        ),
        sa.Column("report_type", sa.Integer, nullable=False, server_default="0"),
        sa.Column("total_reports", sa.Integer, nullable=False, server_default="0"),
        sa.Column("last_report", sa.Integer, nullable=False, server_default="0"),
        sa.Column("occurences", sa.Integer, nullable=False, server_default="1"),
        sa.Column("average_duration", sa.Float(), nullable=False, server_default="0"),
        sa.Column("summed_duration", sa.Float(), nullable=False, server_default="0"),
        sa.Column("notified", sa.Boolean, nullable=False, server_default="False"),
        sa.Column("fixed", sa.Boolean, nullable=False, server_default="False"),
        sa.Column("public", sa.Boolean, nullable=False, server_default="False"),
        sa.Column("read", sa.Boolean, nullable=False, server_default="False"),
    )

    op.create_table(
        "reports",
        sa.Column("id", sa.types.BigInteger, primary_key=True),
        sa.Column(
            "group_id",
            sa.types.BigInteger,
            sa.ForeignKey("reports_groups.id", onupdate="cascade", ondelete="cascade"),
            nullable=False,
            index=True,
        ),
        sa.Column("resource_id", sa.Integer, nullable=False, index=True),
        sa.Column("report_type", sa.Integer, nullable=False, server_default="0"),
        sa.Column("error", sa.UnicodeText, nullable=False, server_default=""),
        sa.Column(
            "extra", sa.dialects.postgresql.JSON, nullable=False, server_default="{}"
        ),
        sa.Column(
            "request", sa.dialects.postgresql.JSON, nullable=False, server_default="{}"
        ),
        sa.Column(
            "tags", sa.dialects.postgresql.JSON, nullable=False, server_default="{}"
        ),
        sa.Column("ip", sa.Unicode(39), nullable=False, server_default=""),
        sa.Column("username", sa.Unicode(255), nullable=False, server_default=""),
        sa.Column("user_agent", sa.Unicode(512), nullable=False, server_default=""),
        sa.Column("url", sa.UnicodeText, nullable=False, server_default=""),
        sa.Column("request_id", sa.Unicode(40), nullable=False, server_default=""),
        sa.Column(
            "request_stats",
            sa.dialects.postgresql.JSON,
            nullable=False,
            server_default="{}",
        ),
        sa.Column(
            "traceback",
            sa.dialects.postgresql.JSON,
            nullable=False,
            server_default="{}",
        ),
        sa.Column("traceback_hash", sa.Unicode(40), nullable=False, server_default=""),
        sa.Column(
            "start_time", sa.DateTime(), nullable=False, server_default=sa.func.now()
        ),
        sa.Column("end_time", sa.DateTime()),
        sa.Column(
            "report_group_time",
            sa.DateTime,
            index=True,
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column("duration", sa.Float(), nullable=False, server_default="0"),
        sa.Column("http_status", sa.Integer, index=True),
        sa.Column("url_domain", sa.Unicode(128)),
        sa.Column("url_path", sa.UnicodeText),
        sa.Column("language", sa.Integer, server_default="0"),
    )
    op.create_index(None, "reports", [sa.text("(tags ->> 'server_name')")])
    op.create_index(None, "reports", [sa.text("(tags ->> 'view_name')")])

    op.create_table(
        "reports_assignments",
        sa.Column("group_id", sa.types.BigInteger, nullable=False, primary_key=True),
        sa.Column(
            "owner_id",
            sa.Integer,
            sa.ForeignKey("users.id", onupdate="cascade", ondelete="cascade"),
            nullable=False,
            primary_key=True,
        ),
        sa.Column("report_time", sa.DateTime, nullable=False),
    )

    op.create_table(
        "reports_comments",
        sa.Column("comment_id", sa.Integer, primary_key=True),
        sa.Column("body", sa.UnicodeText, nullable=False, server_default=""),
        sa.Column(
            "owner_id",
            sa.Integer,
            sa.ForeignKey("users.id", onupdate="cascade", ondelete="set null"),
            nullable=True,
        ),
        sa.Column(
            "created_timestamp",
            sa.DateTime,
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column("report_time", sa.DateTime, nullable=False),
        sa.Column("group_id", sa.types.BigInteger, nullable=False),
    )

    op.create_table(
        "reports_stats",
        sa.Column("resource_id", sa.Integer, nullable=False, index=True),
        sa.Column("start_interval", sa.DateTime, nullable=False, index=True),
        sa.Column("group_id", sa.types.BigInteger, index=True),
        sa.Column(
            "occurences", sa.Integer, nullable=False, server_default="0", index=True
        ),
        sa.Column("owner_user_id", sa.Integer),
        sa.Column("type", sa.Integer, index=True, nullable=False),
        sa.Column("duration", sa.Float(), server_default="0"),
        sa.Column("server_name", sa.Unicode(128), server_default=""),
        sa.Column("view_name", sa.Unicode(128), server_default=""),
        sa.Column("id", sa.BigInteger(), nullable=False, primary_key=True),
    )
    op.create_index(
        "ix_reports_stats_start_interval_group_id",
        "reports_stats",
        ["start_interval", "group_id"],
    )

    op.create_table(
        "slow_calls",
        sa.Column("id", sa.types.BigInteger, primary_key=True),
        sa.Column(
            "report_id",
            sa.types.BigInteger,
            sa.ForeignKey("reports.id", onupdate="cascade", ondelete="cascade"),
            nullable=False,
            index=True,
        ),
        sa.Column(
            "duration", sa.Float(), nullable=False, server_default="0", index=True
        ),
        sa.Column(
            "timestamp",
            sa.DateTime,
            nullable=False,
            server_default=sa.func.now(),
            index=True,
        ),
        sa.Column(
            "report_group_time",
            sa.DateTime,
            index=True,
            nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column("type", sa.Unicode(16), nullable=False, index=True),
        sa.Column("statement", sa.UnicodeText, nullable=False, server_default=""),
        sa.Column("parameters", sa.dialects.postgresql.JSON, nullable=False),
        sa.Column("location", sa.UnicodeText, server_default=""),
        sa.Column("subtype", sa.Unicode(16), nullable=False, index=True),
        sa.Column("resource_id", sa.Integer, nullable=False, index=True),
        sa.Column("statement_hash", sa.Unicode(60), index=True),
    )

    op.create_table(
        "tags",
        sa.Column("id", sa.types.BigInteger, primary_key=True),
        sa.Column(
            "resource_id",
            sa.Integer,
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
        ),
        sa.Column(
            "first_timestamp", sa.DateTime, nullable=False, server_default=sa.func.now()
        ),
        sa.Column(
            "last_timestamp", sa.DateTime, nullable=False, server_default=sa.func.now()
        ),
        sa.Column("name", sa.Unicode(32), nullable=False),
        sa.Column("value", sa.dialects.postgresql.JSON, nullable=False),
        sa.Column("times_seen", sa.Integer, nullable=False, server_default="1"),
    )

    op.create_table(
        "auth_tokens",
        sa.Column("id", sa.Integer, nullable=False, primary_key=True),
        sa.Column("token", sa.Unicode),
        sa.Column(
            "creation_date", sa.DateTime, nullable=False, server_default=sa.func.now()
        ),
        sa.Column("expires", sa.DateTime),
        sa.Column(
            "owner_id",
            sa.Integer,
            sa.ForeignKey("users.id", onupdate="cascade", ondelete="cascade"),
        ),
        sa.Column("description", sa.Unicode),
    )

    op.create_table(
        "channels_actions",
        sa.Column(
            "channel_pkey",
            sa.Integer,
            sa.ForeignKey(
                "alert_channels.pkey", ondelete="CASCADE", onupdate="CASCADE"
            ),
        ),
        sa.Column(
            "action_pkey",
            sa.Integer,
            sa.ForeignKey(
                "alert_channels_actions.pkey", ondelete="CASCADE", onupdate="CASCADE"
            ),
        ),
    )

    op.create_table(
        "config",
        sa.Column("key", sa.Unicode(128), primary_key=True),
        sa.Column("section", sa.Unicode(128), primary_key=True),
        sa.Column("value", sa.dialects.postgresql.JSON, server_default="{}"),
    )

    op.create_table(
        "plugin_configs",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("plugin_name", sa.Unicode(128)),
        sa.Column("section", sa.Unicode(128)),
        sa.Column("config", sa.dialects.postgresql.JSON, server_default="{}"),
        sa.Column(
            "resource_id",
            sa.Integer(),
            sa.ForeignKey(
                "resources.resource_id", onupdate="cascade", ondelete="cascade"
            ),
        ),
        sa.Column(
            "owner_id",
            sa.Integer(),
            sa.ForeignKey("users.id", onupdate="cascade", ondelete="cascade"),
        ),
    )

    op.create_table(
        "rc_versions",
        sa.Column("name", sa.Unicode(40), primary_key=True),
        sa.Column("value", sa.Unicode(40)),
    )
    version_table = sa.table(
        "rc_versions",
        sa.Column("name", sa.Unicode(40)),
        sa.Column("value", sa.Unicode(40)),
    )

    insert = version_table.insert().values(name="es_reports")
    op.execute(insert)
    insert = version_table.insert().values(name="es_reports_groups")
    op.execute(insert)
    insert = version_table.insert().values(name="es_reports_stats")
    op.execute(insert)
    insert = version_table.insert().values(name="es_logs")
    op.execute(insert)
    insert = version_table.insert().values(name="es_metrics")
    op.execute(insert)
    insert = version_table.insert().values(name="es_slow_calls")
    op.execute(insert)

    op.execute(
        """
        CREATE OR REPLACE FUNCTION floor_time_5min(timestamp without time zone)
          RETURNS timestamp without time zone AS
        $BODY$SELECT date_trunc('hour', $1) + INTERVAL '5 min' * FLOOR(date_part('minute', $1) / 5.0)$BODY$
          LANGUAGE sql VOLATILE;
    """
    )

    op.execute(
        """
    CREATE OR REPLACE FUNCTION partition_logs() RETURNS trigger
        LANGUAGE plpgsql SECURITY DEFINER
        AS $$
    DECLARE
    main_table         varchar := 'logs';
    partitioned_table  varchar := '';
    BEGIN

    IF NEW.permanent THEN
        partitioned_table := main_table || '_p_' || date_part('year', NEW.timestamp)::TEXT || '_' || DATE_part('month', NEW.timestamp);
    ELSE
        partitioned_table := main_table || '_p_' || date_part('year', NEW.timestamp)::TEXT || '_' || DATE_part('month', NEW.timestamp) || '_' || DATE_part('day', NEW.timestamp);
    END IF;

    BEGIN
    EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
    EXCEPTION
    WHEN undefined_table THEN
        RAISE NOTICE 'A partition has been created %', partitioned_table;
        IF NEW.permanent THEN
        EXECUTE format('CREATE TABLE  IF NOT EXISTS %s ( CHECK( timestamp >= DATE %s AND timestamp < DATE %s)) INHERITS (%s)',
                partitioned_table,
                quote_literal(date_trunc('month', NEW.timestamp)::date) ,
                quote_literal((date_trunc('month', NEW.timestamp)::date  + interval '1 month')::text),
                main_table);
        EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(log_id);', partitioned_table, partitioned_table);
        EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s (timestamp);', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_namespace_resource_id ON %s (namespace, resource_id);', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s (resource_id);', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_pkey_namespace ON %s (primary_key, namespace);', partitioned_table, partitioned_table);
        ELSE
        EXECUTE format('CREATE TABLE  IF NOT EXISTS %s ( CHECK( timestamp >= DATE %s AND timestamp < DATE %s)) INHERITS (%s)',
                partitioned_table,
                quote_literal(date_trunc('day', NEW.timestamp)::date) ,
                quote_literal((date_trunc('day', NEW.timestamp)::date  + interval '1 day')::text),
                main_table);
        EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s_ PRIMARY KEY(log_id);', partitioned_table, partitioned_table);
        EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s (timestamp);', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_namespace_resource_id ON %s (namespace, resource_id);', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s (resource_id);', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_primary_key_namespace ON %s (primary_key,namespace);', partitioned_table, partitioned_table);
        END IF;


        EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
    END;


    RETURN NULL;
    END
    $$;
    """
    )

    op.execute(
        """
    CREATE TRIGGER partition_logs BEFORE INSERT ON logs FOR EACH ROW EXECUTE PROCEDURE partition_logs();
    """
    )

    op.execute(
        """
    CREATE OR REPLACE FUNCTION partition_metrics() RETURNS trigger
        LANGUAGE plpgsql SECURITY DEFINER
        AS $$
        DECLARE
        main_table         varchar := 'metrics';
        partitioned_table  varchar := '';
        BEGIN

        partitioned_table := main_table || '_p_' || date_part('year', NEW.timestamp)::TEXT || '_' || DATE_part('month', NEW.timestamp) || '_' || DATE_part('day', NEW.timestamp);

    BEGIN
    EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
    EXCEPTION
    WHEN undefined_table THEN
        RAISE NOTICE 'A partition has been created %', partitioned_table;
        EXECUTE format('CREATE TABLE  IF NOT EXISTS %s ( CHECK( timestamp >= DATE %s AND timestamp < DATE %s)) INHERITS (%s)',
                partitioned_table,
                quote_literal(date_trunc('day', NEW.timestamp)::date) ,
                quote_literal((date_trunc('day', NEW.timestamp)::date  + interval '1 day')::text),
                main_table);
        EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(pkey);', partitioned_table, partitioned_table);
        EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s (timestamp);', partitioned_table, partitioned_table);
        EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s (resource_id);', partitioned_table, partitioned_table);
        EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
    END;

    RETURN NULL;
    END
    $$;
    """
    )

    op.execute(
        """
    CREATE TRIGGER partition_metrics BEFORE INSERT ON metrics FOR EACH ROW EXECUTE PROCEDURE partition_metrics();
    """
    )

    op.execute(
        """
    CREATE FUNCTION partition_reports_stats() RETURNS trigger
        LANGUAGE plpgsql SECURITY DEFINER
        AS $$
        DECLARE
            main_table         varchar := 'reports_stats';
            partitioned_table  varchar := '';
        BEGIN

            partitioned_table := main_table || '_p_' || date_part('year', NEW.start_interval)::TEXT || '_' || DATE_part('month', NEW.start_interval);

            BEGIN
                EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
            EXCEPTION
            WHEN undefined_table THEN
                RAISE NOTICE 'A partition has been created %', partitioned_table;
                EXECUTE format('CREATE TABLE  IF NOT EXISTS %s ( CHECK( start_interval >= DATE %s AND start_interval < DATE %s )) INHERITS (%s)',
                    partitioned_table,
                    quote_literal(date_trunc('month', NEW.start_interval)::date) ,
                    quote_literal((date_trunc('month', NEW.start_interval)::date  + interval '1 month')::text),
                    main_table);
                EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
                EXECUTE format('CREATE INDEX ix_%s_start_interval ON %s USING btree (start_interval);', partitioned_table, partitioned_table);
                EXECUTE format('CREATE INDEX ix_%s_type ON %s USING btree (type);', partitioned_table, partitioned_table);
                EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s USING btree (resource_id);', partitioned_table, partitioned_table);
                EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
            END;
            RETURN NULL;
        END
        $$;
    """
    )

    op.execute(
        """
    CREATE TRIGGER partition_reports_stats BEFORE INSERT ON reports_stats FOR EACH ROW EXECUTE PROCEDURE partition_reports_stats();
    """
    )

    op.execute(
        """
    CREATE OR REPLACE FUNCTION partition_reports_groups() RETURNS trigger
        LANGUAGE plpgsql SECURITY DEFINER
        AS $$
    DECLARE
        main_table         varchar := 'reports_groups';
        partitioned_table  varchar := '';
    BEGIN

        partitioned_table := main_table || '_p_' || date_part('year', NEW.first_timestamp)::TEXT || '_' || DATE_part('month', NEW.first_timestamp);

        BEGIN
            EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
        EXCEPTION
        WHEN undefined_table THEN
            RAISE NOTICE 'A partition has been created %', partitioned_table;
            EXECUTE format('CREATE TABLE  IF NOT EXISTS %s ( CHECK( first_timestamp >= DATE %s AND first_timestamp < DATE %s )) INHERITS (%s)',
                partitioned_table,
                quote_literal(date_trunc('month', NEW.first_timestamp)::date) ,
                quote_literal((date_trunc('month', NEW.first_timestamp)::date  + interval '1 month')::text),
                main_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
            EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
        END;
        RETURN NULL;
    END
    $$;
    """
    )

    op.execute(
        """
    CREATE TRIGGER partition_reports_groups BEFORE INSERT ON reports_groups FOR EACH ROW EXECUTE PROCEDURE partition_reports_groups();
    """
    )

    op.execute(
        """
    CREATE OR REPLACE FUNCTION partition_reports() RETURNS trigger
        LANGUAGE plpgsql SECURITY DEFINER
        AS $$
    DECLARE
        main_table         varchar := 'reports';
        partitioned_table  varchar := '';
        partitioned_parent_table  varchar := '';
    BEGIN

        partitioned_table := main_table || '_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);
        partitioned_parent_table := 'reports_groups_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);

        BEGIN
            EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
        EXCEPTION
        WHEN undefined_table THEN
            RAISE NOTICE 'A partition has been created %', partitioned_table;
            EXECUTE format('CREATE TABLE  IF NOT EXISTS %s ( CHECK( report_group_time >= DATE %s AND report_group_time < DATE %s )) INHERITS (%s)',
                partitioned_table,
                quote_literal(date_trunc('month', NEW.report_group_time)::date) ,
                quote_literal((date_trunc('month', NEW.report_group_time)::date  + interval '1 month')::text),
                main_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_group_id FOREIGN KEY (group_id) REFERENCES %s (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table, partitioned_parent_table);
            EXECUTE format('CREATE INDEX ix_%s_report_group_time ON %s USING btree (report_group_time);', partitioned_table, partitioned_table);
            EXECUTE format('CREATE INDEX ix_%s_group_id ON %s USING btree (group_id);', partitioned_table, partitioned_table);
            EXECUTE format('CREATE INDEX ix_%s_resource_id ON %s USING btree (resource_id);', partitioned_table, partitioned_table);
            EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
        END;
        RETURN NULL;
    END
    $$;
    """
    )

    op.execute(
        """
    CREATE TRIGGER partition_reports BEFORE INSERT ON reports FOR EACH ROW EXECUTE PROCEDURE partition_reports();
    """
    )

    op.execute(
        """
    CREATE OR REPLACE FUNCTION partition_slow_calls() RETURNS trigger
        LANGUAGE plpgsql SECURITY DEFINER
        AS $$
    DECLARE
        main_table         varchar := 'slow_calls';
        partitioned_table  varchar := '';
        partitioned_parent_table  varchar := '';
    BEGIN

        partitioned_table := main_table || '_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);
        partitioned_parent_table := 'reports_p_' || date_part('year', NEW.report_group_time)::TEXT || '_' || DATE_part('month', NEW.report_group_time);

        BEGIN
            EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
        EXCEPTION
        WHEN undefined_table THEN
            RAISE NOTICE 'A partition has been created %', partitioned_table;
            EXECUTE format('CREATE TABLE  IF NOT EXISTS %s ( CHECK( report_group_time >= DATE %s AND report_group_time < DATE %s )) INHERITS (%s)',
                partitioned_table,
                quote_literal(date_trunc('month', NEW.report_group_time)::date) ,
                quote_literal((date_trunc('month', NEW.report_group_time)::date  + interval '1 month')::text),
                main_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT pk_%s PRIMARY KEY(id);', partitioned_table, partitioned_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_resource_id FOREIGN KEY (resource_id) REFERENCES resources (resource_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table);
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT fk_%s_report_id FOREIGN KEY (report_id) REFERENCES %s (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE;', partitioned_table, partitioned_table, partitioned_parent_table);
            EXECUTE format('CREATE INDEX ix_%s_resource_id  ON %s USING btree (resource_id);', partitioned_table, partitioned_table);
            EXECUTE format('CREATE INDEX ix_%s_report_id ON %s USING btree (report_id);', partitioned_table, partitioned_table);
            EXECUTE format('CREATE INDEX ix_%s_timestamp ON %s USING btree (timestamp);', partitioned_table, partitioned_table);
            EXECUTE 'INSERT INTO ' || partitioned_table || ' SELECT(' || TG_TABLE_NAME || ' ' || quote_literal(NEW) || ').*;';
        END;
        RETURN NULL;
    END
    $$;
    """
    )

    op.execute(
        """
    CREATE TRIGGER partition_slow_calls BEFORE INSERT ON slow_calls FOR EACH ROW EXECUTE PROCEDURE partition_slow_calls();
    """
    )


def downgrade():
    pass