"""Add events table for internal CalDAV (Radicale) linkage."""

from alembic import op

revision = "0019"
down_revision = "0018"


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS events (
            id          SERIAL PRIMARY KEY,
            user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            project_id  INTEGER REFERENCES projects(id) ON DELETE SET NULL,
            uid         TEXT NOT NULL,
            title       TEXT NOT NULL DEFAULT '',
            start_dt    TIMESTAMP WITH TIME ZONE NOT NULL,
            end_dt      TIMESTAMP WITH TIME ZONE,
            all_day     BOOLEAN NOT NULL DEFAULT FALSE,
            description TEXT NOT NULL DEFAULT '',
            location    TEXT NOT NULL DEFAULT '',
            recurrence  TEXT,
            created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            updated_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
            UNIQUE(user_id, uid)
        )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_events_user_id ON events(user_id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_events_project_id ON events(project_id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_events_start_dt ON events(start_dt)")


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_events_start_dt")
    op.execute("DROP INDEX IF EXISTS ix_events_project_id")
    op.execute("DROP INDEX IF EXISTS ix_events_user_id")
    op.execute("DROP TABLE IF EXISTS events")