"""Replaced 'rank' with 'pareto'

Revision ID: f3afd6197391
Revises: 310027ddeea9
Create Date: 2020-12-08 08:41:49.170716

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'f3afd6197391'
down_revision = '310027ddeea9'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('receiver_rankings', sa.Column('global_distance_pareto', sa.Float(precision=2), nullable=True))
    op.add_column('receiver_rankings', sa.Column('local_distance_pareto', sa.Float(precision=2), nullable=True))
    op.drop_column('receiver_rankings', 'global_rank')
    op.drop_column('receiver_rankings', 'longtime_local_rank_delta')
    op.drop_column('receiver_rankings', 'longtime_global_rank_delta')
    op.drop_column('receiver_rankings', 'longtime_global_rank')
    op.drop_column('receiver_rankings', 'longtime_local_rank')
    op.drop_column('receiver_rankings', 'local_rank')
    # ### end Alembic commands ###

    op.execute("""
        UPDATE receiver_rankings AS rr
        SET
            local_distance_pareto = sq.local_distance_pareto,
            global_distance_pareto = sq.global_distance_pareto
        FROM
        (
            SELECT
                date,
                receiver_id,
                1.0 * RANK() OVER (PARTITION BY date, country_id ORDER BY max_distance) / COUNT(*) OVER (PARTITION BY date, country_id) AS local_distance_pareto,
                1.0 * RANK() OVER (PARTITION BY date ORDER BY max_distance) / COUNT(*) OVER (PARTITION BY date) AS global_distance_pareto
            FROM receiver_rankings
        ) AS sq
        WHERE rr.date = sq.date AND rr.receiver_id = sq.receiver_id;
    """)


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('receiver_rankings', sa.Column('local_rank', sa.Float(precision=2), autoincrement=False, nullable=True))
    op.add_column('receiver_rankings', sa.Column('longtime_local_rank', sa.Float(precision=2), autoincrement=False, nullable=True))
    op.add_column('receiver_rankings', sa.Column('longtime_global_rank', sa.Float(precision=2), autoincrement=False, nullable=True))
    op.add_column('receiver_rankings', sa.Column('longtime_global_rank_delta', sa.Float(precision=2), autoincrement=False, nullable=True))
    op.add_column('receiver_rankings', sa.Column('longtime_local_rank_delta', sa.Float(precision=2), autoincrement=False, nullable=True))
    op.add_column('receiver_rankings', sa.Column('global_rank', sa.Float(precision=2), autoincrement=False, nullable=True))
    op.drop_column('receiver_rankings', 'local_distance_pareto')
    op.drop_column('receiver_rankings', 'global_distance_pareto')
    # ### end Alembic commands ###
