alembic/versions/0001_initial_schema.py

"""Initial schema — drop and rebuild all tables with updated schema.

Revision ID: 0001
Revises:
Create Date: 2026-05-08

"""
from typing import Sequence, Union

from alembic import op

revision: str = "0001"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Drop existing tables in dependency order (routing_results references notifications).
    op.execute("DROP TABLE IF EXISTS routing_results")
    op.execute("DROP TABLE IF EXISTS notifications")
    op.execute("DROP TABLE IF EXISTS subscriptions")
    op.execute("DROP TABLE IF EXISTS daily_brief")

    op.execute("""
        CREATE TABLE subscriptions (
            id               TEXT PRIMARY KEY,
            folder_id        TEXT NOT NULL,
            folder_name      TEXT NOT NULL,
            expiry_datetime  TEXT NOT NULL,
            notification_url TEXT NOT NULL,
            client_state     TEXT NOT NULL,
            is_active        INTEGER NOT NULL DEFAULT 1,
            created_at       TEXT NOT NULL DEFAULT (datetime('now')),
            last_renewed_at  TEXT
        )
    """)

    op.execute("""
        CREATE TABLE notifications (
            id                INTEGER PRIMARY KEY AUTOINCREMENT,
            received_at       TEXT NOT NULL DEFAULT (datetime('now')),
            subscription_id   TEXT,
            change_type       TEXT,
            resource          TEXT,
            message_id        TEXT,
            email_identifier  TEXT,
            processing_status TEXT NOT NULL DEFAULT 'pending',
            error_message     TEXT,
            storage_path      TEXT
        )
    """)

    op.execute("""
        CREATE TABLE routing_results (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            notification_id     INTEGER NOT NULL,
            processed_at        TEXT NOT NULL DEFAULT (datetime('now')),
            email_identifier    TEXT,
            attachment_filename TEXT,
            document_type       TEXT,
            action              TEXT,
            destination_path    TEXT,
            confidence_scores   TEXT,
            error_message       TEXT
        )
    """)

    op.execute("""
        CREATE TABLE daily_brief (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at          TEXT NOT NULL DEFAULT (datetime('now')),
            email_identifier    TEXT NOT NULL,
            notification_id     INTEGER NOT NULL,
            sender_email        TEXT,
            subject             TEXT,
            attachment_filename TEXT,
            reason              TEXT NOT NULL,
            confidence_scores   TEXT,
            error_message       TEXT,
            included_in_report  INTEGER NOT NULL DEFAULT 0,
            report_sent_at      TEXT
        )
    """)

    op.execute("PRAGMA journal_mode=WAL")
    op.execute("PRAGMA foreign_keys=ON")


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS routing_results")
    op.execute("DROP TABLE IF EXISTS notifications")
    op.execute("DROP TABLE IF EXISTS subscriptions")
    op.execute("DROP TABLE IF EXISTS daily_brief")