"""create virtual table for Full-Text Search

Revision ID: 129ac51807e4
Revises: 8974efc51d22
Create Date: 2021-08-13 19:13:54.112538

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '129ac51807e4'
down_revision = '8974efc51d22'
branch_labels = None
depends_on = None


def upgrade():
    queries = [
        "CREATE VIRTUAL TABLE pubsub_items_fts "
        "USING fts5(data, content=pubsub_items, content_rowid=id)",
        "CREATE TRIGGER pubsub_items_fts_sync_ins AFTER INSERT ON pubsub_items BEGIN"
        "  INSERT INTO pubsub_items_fts(rowid, data) VALUES (new.id, new.data);"
        "END",
        "CREATE TRIGGER pubsub_items_fts_sync_del AFTER DELETE ON pubsub_items BEGIN"
        "  INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) "
        "VALUES('delete', old.id, old.data);"
        "END",
        "CREATE TRIGGER pubsub_items_fts_sync_upd AFTER UPDATE ON pubsub_items BEGIN"
        "  INSERT INTO pubsub_items_fts(pubsub_items_fts, rowid, data) VALUES"
        "('delete', old.id, old.data);"
        "  INSERT INTO pubsub_items_fts(rowid, data) VALUES(new.id, new.data);"
        "END",
        "INSERT INTO pubsub_items_fts(rowid, data) SELECT id, data from pubsub_items"
    ]
    for q in queries:
        op.execute(sa.DDL(q))


def downgrade():
    queries = [
        "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_ins",
        "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_del",
        "DROP TRIGGER IF EXISTS pubsub_items_fts_sync_upd",
        "DROP TABLE IF EXISTS pubsub_items_fts",
    ]
    for q in queries:
        op.execute(sa.DDL(q))