"""application-scoped short links

Revision ID: 000000000024
Revises: 000000000018
Create Date: 2026-05-03

Adds app_links, a reusable per-application short-link table for browser
applications that need stable public URLs for compressed state.
"""

from alembic import op

revision = "000000000024"
down_revision = "000000000018"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE IF NOT EXISTS app_links (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL
                REFERENCES applications(id) ON DELETE CASCADE,
            owner_user_id UUID NOT NULL
                REFERENCES users(id) ON DELETE CASCADE,
            owner_username VARCHAR(64) NOT NULL,
            slug VARCHAR(48) NOT NULL,
            app_slug VARCHAR(64) NOT NULL,
            resource_kind VARCHAR(64) NOT NULL,
            title VARCHAR(160),
            target_path TEXT NOT NULL,
            metadata JSONB DEFAULT '{}'::jsonb,
            click_count INTEGER NOT NULL DEFAULT 0,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            CONSTRAINT uq_app_links_application_owner_slug
                UNIQUE (application_id, owner_username, slug)
        );
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_app_links_application_owner_created
        ON app_links (application_id, owner_user_id, created_at DESC);
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_app_links_public_lookup
        ON app_links (application_id, owner_username, slug);
    """)


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_app_links_public_lookup;")
    op.execute("DROP INDEX IF EXISTS ix_app_links_application_owner_created;")
    op.execute("DROP TABLE IF EXISTS app_links;")