"""add_transactions_notes_table

Creates the transactions_notes join table that links transactions to notes.

Revision ID: txn001_txn_notes
Revises: global001_provider_global_creds
Create Date: 2026-04-23
"""
from alembic import op
import sqlalchemy as sa

revision = "txn001_txn_notes"
down_revision = "global001_provider_global_creds"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS transactions_notes (
            transaction_id INTEGER NOT NULL REFERENCES transactions(id),
            note_id INTEGER NOT NULL REFERENCES notes(id)
        )
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_transactions_notes_transaction_id
        ON transactions_notes (transaction_id)
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_transactions_notes_note_id
        ON transactions_notes (note_id)
    """)


def downgrade() -> None:
    op.drop_index("ix_transactions_notes_note_id", table_name="transactions_notes")
    op.drop_index("ix_transactions_notes_transaction_id", table_name="transactions_notes")
    op.drop_table("transactions_notes")