"""Add popular names

Revision ID: 953a930ea9e7
Revises: 4c8fc73caeb7
Create Date: 2021-04-25 16:52:59.750980

"""
from alembic import op

# revision identifiers, used by Alembic.
revision = '953a930ea9e7'
down_revision = 'a9e35e4e4f8c'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute("""
CREATE TABLE popular_names(
    name VARCHAR(64) PRIMARY KEY,
    hash VARCHAR(64) UNIQUE NOT NULL,
    views INTEGER NOT NULL DEFAULT 0,
    bid_count INTEGER NOT NULL DEFAULT 0,
    last_bid_height INTEGER NOT NULL DEFAULT 0,
    last_bid_time INTEGER NOT NULL DEFAULT 0
);
    
CREATE FUNCTION refresh_popular_names(min_block_height INTEGER)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    TRUNCATE popular_names;
    
    INSERT INTO popular_names
    WITH bid_counts AS (
        SELECT 
            outputs.covenant_name_hash AS name_hash, 
            COUNT(*) AS bid_count, 
            MAX(block_height) AS max_height
        FROM outputs
        WHERE outputs.covenant_action = 'BID' AND outputs.block_height > min_block_height
        GROUP BY outputs.covenant_name_hash
    )
    SELECT 
        names.name AS name,
        names.hash AS hash, 
        names.views AS VIEWS,  
        bid_counts.bid_count AS bid_count, 
        bid_counts.max_height AS last_bid_height, 
        blocks.time AS last_bid_time
    FROM outputs
    JOIN bid_counts ON bid_counts.name_hash = outputs.covenant_name_hash 
    JOIN names ON names.hash = outputs.covenant_name_hash
    JOIN blocks ON blocks.height = bid_counts.max_height
    WHERE outputs.covenant_action = 'OPEN' AND outputs.block_height > min_block_height
    ORDER BY bid_counts.bid_count DESC, names.views DESC, blocks.time DESC LIMIT 1000;
END;
$$;
    """)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute('DROP FUNCTION refresh_popular_names')
    op.execute('DROP TABLE popular_names')
    # ### end Alembic commands ###
