"""Add note_drafts and note_versions tables."""

from alembic import op

revision = "0022"
down_revision = "0021"


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS note_drafts (
            id              SERIAL PRIMARY KEY,
            note_id         INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
            user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            proposed_body   TEXT NOT NULL,
            original_body   TEXT NOT NULL,
            instruction     TEXT NOT NULL DEFAULT '',
            scope           TEXT NOT NULL DEFAULT 'document',
            created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
            UNIQUE (note_id, user_id)
        )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_note_drafts_note_id ON note_drafts(note_id)")

    op.execute("""
        CREATE TABLE IF NOT EXISTS note_versions (
            id          SERIAL PRIMARY KEY,
            note_id     INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
            user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            body        TEXT NOT NULL,
            title       TEXT NOT NULL DEFAULT '',
            created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
        )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_note_versions_note_id ON note_versions(note_id)")


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_note_versions_note_id")
    op.execute("DROP TABLE IF EXISTS note_versions")
    op.execute("DROP INDEX IF EXISTS ix_note_drafts_note_id")
    op.execute("DROP TABLE IF EXISTS note_drafts")