"""checkin call booking

Revision ID: 000000000011
Revises: 000000000010
Create Date: 2026-02-13

Creates booking_policies table and adds new columns to dayrate_bookings
for the checkin_call_booking domain slice:
  - booking_policies: policy configuration for free booking allotments
  - dayrate_bookings: enrollment_id, replaces_booking_id, policy_key, is_free
"""

from alembic import op

revision = "000000000011"
down_revision = "000000000010"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # -----------------------------------------------------------------------
    # booking_policies table (new)
    # -----------------------------------------------------------------------
    op.execute("""
        CREATE TABLE IF NOT EXISTS booking_policies (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
            policy_key TEXT NOT NULL,
            entitlement_key TEXT NOT NULL,
            free_bookings_per_period INTEGER NOT NULL DEFAULT 0,
            period_days INTEGER NOT NULL DEFAULT 28,
            overage_rate INTEGER,
            overage_currency TEXT NOT NULL DEFAULT 'usd',
            overage_product_name TEXT,
            reschedule_is_free BOOLEAN NOT NULL DEFAULT true,
            is_active BOOLEAN NOT NULL DEFAULT true,
            metadata JSONB DEFAULT '{}',
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            CONSTRAINT uq_booking_policies_app_key UNIQUE (application_id, policy_key)
        );
    """)

    # Index for policy lookups
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_booking_policies_app_active
        ON booking_policies (application_id)
        WHERE is_active = true;
    """)

    # -----------------------------------------------------------------------
    # dayrate_bookings: new columns (all nullable/defaulted for backward compat)
    # -----------------------------------------------------------------------

    # enrollment_id: cross-system reference to HTMA enrollment
    op.execute("""
        ALTER TABLE dayrate_bookings
        ADD COLUMN IF NOT EXISTS enrollment_id TEXT;
    """)

    # replaces_booking_id: self-referencing FK for reschedule chain
    op.execute("""
        ALTER TABLE dayrate_bookings
        ADD COLUMN IF NOT EXISTS replaces_booking_id UUID
        REFERENCES dayrate_bookings(id) ON DELETE SET NULL;
    """)

    # policy_key: which booking policy was applied (text, no FK)
    op.execute("""
        ALTER TABLE dayrate_bookings
        ADD COLUMN IF NOT EXISTS policy_key TEXT;
    """)

    # is_free: whether this booking was a free allotment booking
    op.execute("""
        ALTER TABLE dayrate_bookings
        ADD COLUMN IF NOT EXISTS is_free BOOLEAN NOT NULL DEFAULT false;
    """)

    # -----------------------------------------------------------------------
    # Indexes for new query patterns
    # -----------------------------------------------------------------------

    # Allotment counting: non-cancelled, non-reschedule bookings by user
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_dayrate_bookings_allotment
        ON dayrate_bookings (application_id, user_id, created_at)
        WHERE status != 'cancelled' AND replaces_booking_id IS NULL;
    """)

    # Email fallback for allotment counting
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_dayrate_bookings_email_allotment
        ON dayrate_bookings (application_id, client_email, created_at)
        WHERE status != 'cancelled' AND replaces_booking_id IS NULL;
    """)

    # Enrollment lookup (for admin bookings list filter)
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_dayrate_bookings_enrollment
        ON dayrate_bookings (enrollment_id)
        WHERE enrollment_id IS NOT NULL;
    """)

    # Upcoming bookings by user_id (for batch profile)
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_dayrate_bookings_upcoming_user
        ON dayrate_bookings (application_id, user_id, date)
        WHERE status != 'cancelled';
    """)

    # Upcoming bookings by email (for legacy batch profile fallback)
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_dayrate_bookings_upcoming_email
        ON dayrate_bookings (application_id, client_email, date)
        WHERE status != 'cancelled' AND user_id IS NULL;
    """)


def downgrade() -> None:
    # Drop indexes
    op.execute("DROP INDEX IF EXISTS idx_dayrate_bookings_upcoming_email;")
    op.execute("DROP INDEX IF EXISTS idx_dayrate_bookings_upcoming_user;")
    op.execute("DROP INDEX IF EXISTS idx_dayrate_bookings_enrollment;")
    op.execute("DROP INDEX IF EXISTS idx_dayrate_bookings_email_allotment;")
    op.execute("DROP INDEX IF EXISTS idx_dayrate_bookings_allotment;")

    # Drop new columns from dayrate_bookings
    op.execute("ALTER TABLE dayrate_bookings DROP COLUMN IF EXISTS is_free;")
    op.execute("ALTER TABLE dayrate_bookings DROP COLUMN IF EXISTS policy_key;")
    op.execute("ALTER TABLE dayrate_bookings DROP COLUMN IF EXISTS replaces_booking_id;")
    op.execute("ALTER TABLE dayrate_bookings DROP COLUMN IF EXISTS enrollment_id;")

    # Drop booking_policies table
    op.execute("DROP INDEX IF EXISTS idx_booking_policies_app_active;")
    op.execute("DROP TABLE IF EXISTS booking_policies;")