"""empty message

Revision ID: 0039_fix_notifications
Revises: 0038_test_api_key_type
Create Date: 2016-07-06 13:28:48.381278

"""

# revision identifiers, used by Alembic.
revision = "0039_fix_notifications"
down_revision = "0038_test_api_key_type"

import sqlalchemy as sa
from alembic import op


def upgrade():
    op.execute(
        "update notifications set notification_type = (select cast(cast(template_type as text) as notification_type) from templates where templates.id= notifications.template_id)"
    )
    conn = op.get_bind()
    reset_counts = (
        "update notification_statistics set emails_requested = 0, emails_delivered = 0, emails_failed=0,"
        "sms_requested = 0, sms_delivered = 0, sms_failed=0 where day > '2016-06-30'"
    )
    op.execute(reset_counts)
    all_notifications = "select * from notifications where date(created_at) > '2016-06-30' order by created_at;"

    results = conn.execute(all_notifications)
    res = results.fetchall()

    for x in res:
        print(" in loop {} {}".format(x.notification_type, x.created_at))
        created = x.created_at.strftime("%Y-%m-%d")
        if x.notification_type == "email" and x.status == "delivered":
            sql = (
                "update notification_statistics set emails_requested = emails_requested + 1, "
                "emails_delivered = emails_delivered + 1 where day = date('{}') and service_id = '{}'".format(
                    created, x.service_id
                )
            )
        if x.notification_type == "sms" and x.status == "delivered":
            sql = (
                "update notification_statistics set sms_requested = sms_requested + 1, "
                "sms_delivered = sms_delivered + 1 where day = date('{}') and service_id = '{}'".format(
                    created, x.service_id
                )
            )
        if x.notification_type == "email" and x.status in [
            "technical-failure",
            "temporary-failure",
            "permanent-failure",
        ]:
            sql = (
                "update notification_statistics set emails_requested = emails_requested + 1, "
                "emails_failed = emails_failed + 1 where day = date('{}') and service_id = '{}'".format(
                    created, x.service_id
                )
            )
        if x.notification_type == "sms" and x.status in ["technical-failure", "temporary-failure", "permanent-failure"]:
            sql = (
                "update notification_statistics set sms_requested = sms_requested + 1, "
                "sms_failed = sms_failed + 1 where day = date('{}') and service_id = '{}'".format(created, x.service_id)
            )
        if x.notification_type == "email" and x.status in ["created", "sending", "pending"]:
            sql = (
                "update notification_statistics set emails_requested = emails_requested + 1 "
                " where day = date('{}') and service_id = '{}'".format(created, x.service_id)
            )
        if x.notification_type == "sms" and x.status in ["created", "sending", "pending"]:
            sql = (
                "update notification_statistics set sms_requested = sms_requested + 1 "
                " where day = date('{}') and service_id = '{}'".format(created, x.service_id)
            )
        print(sql)
        conn.execute(sql)


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    pass
    ### end Alembic commands ###
