# coding=utf-8
"""fix identifier prefix

Revision ID: 
Revises: 
Create Date: 2014-10-24 10:59:01.044000

"""

# revision identifiers, used by Alembic.

revision = ''
down_revision = ''

import datetime

from alembic import op
import sqlalchemy as sa


def upgrade():
    strip_prefix = sa.text('UPDATE identifier AS i SET updated = now(), '
        'name = substring(name from :extract) '
        'WHERE name LIKE :match AND NOT EXISTS ('
            'SELECT 1 FROM identifier WHERE type = i.type '
            'AND description = i.description AND lang = i.lang '
            'AND name = substring(i.name from :extract))', conn)

    del_prefixed = sa.text('DELETE FROM identifier AS i '
        'WHERE name LIKE :match AND EXISTS ('
            'SELECT 1 FROM identifier WHERE type = i.type '
            'AND description = i.description AND lang = i.lang '
            'AND name = substring(i.name from :extract))', conn)

    del_prefixed_langident = sa.text('DELETE FROM languageidentifier AS li '
        'WHERE EXISTS (SELECT 1 FROM identifier AS i '
            'WHERE pk = li.identifier_pk AND name LIKE :match AND EXISTS ('
                'SELECT 1 FROM identifier WHERE type = i.type '
                'AND description = i.description AND lang = i.lang '
                'AND name = substring(i.name from :extract)))', conn)
    
    for match, extract in [(': %', ':\s+(.*)'), ('L:%', 'L:(.*)')]:
        for query in [strip_prefix, del_prefixed_langident, del_prefixed]:
            query.execute(match=match, extract=extract)


def downgrade():
    pass

work on alt name prefix strip migration