"""users perm delete triggers

Revision ID: b65fd7c7aaea
Revises: ad33e57d6e0d
Create Date: 2018-03-22 12:44:33.861723

"""

# revision identifiers, used by Alembic.
revision = 'b65fd7c7aaea'
down_revision = 'ad33e57d6e0d'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.create_table('deleted_users_influencers',
    sa.Column('ts', sa.DateTime(), server_default=sa.text('CURRENT_TIMESTAMP'), nullable=False),
    sa.Column('table_name', sa.String(), nullable=False),
    sa.Column('username', sa.String(), nullable=False),
    sa.Column('identifier', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('ts', 'table_name', 'username', 'identifier')
    )

    op.execute('''
    CREATE OR REPLACE FUNCTION trig_delete_from_table()
      RETURNS trigger AS
    $BODY$
    DECLARE
      pkfield text;
    BEGIN
        IF TG_TABLE_NAME = 'users_tw_influencers' THEN 
          pkfield := OLD.eid;
        ELSIF TG_TABLE_NAME = 'users_sc_influencers' THEN
          pkfield := OLD.scid;
        ELSIF TG_TABLE_NAME = 'users_in_influencers' THEN
          pkfield := OLD.inid;
        ELSIF TG_TABLE_NAME = 'users_spy_playlists' THEN
          pkfield := OLD.spyid;
        ELSEIF TG_TABLE_NAME = 'users_yt_channels' THEN
          pkfield := OLD.ytid;
        ELSE
          RAISE NOTICE 'Unknown users permission table: %s', TG_TABLE_NAME;
        END IF;
        
        EXECUTE 'INSERT INTO deleted_users_influencers '
             || '(ts, table_name, username, identifier) '
             || ' VALUES '
             || '(now(), $1, $2, $3)' 
        USING TG_TABLE_NAME, OLD.username, pkfield;
    RETURN null;
    END;
    $BODY$ LANGUAGE plpgsql
        ''')
    ### end Alembic commands ###
    for table_name in ('users_tw_influencers', 'users_sc_influencers', 'users_in_influencers',
                       'users_spy_playlists', 'users_yt_channels'):
        op.execute("""
           CREATE TRIGGER delete_from_{table_name}
           AFTER DELETE
           ON {table_name}
           FOR EACH ROW
           EXECUTE PROCEDURE trig_delete_from_table();""".format(table_name=table_name))


def downgrade():
    for table_name in ('users_tw_influencers', 'users_sc_influencers', 'users_in_influencers',
                       'users_spy_playlists', 'users_yt_channels'):
        op.execute('DROP trigger delete_from_{0} on {0}'.format(table_name))

    op.execute('drop function trig_delete_from_table()')
    op.drop_table('deleted_users_influencers')