"""hr_lifecycle_events.trigger_source + composite lookup index.

Revision ID: 242
Revises: 241
Create Date: 2026-06-02

Renumbered 239 → 242 after rebase 2026-06-02: main shipped 239 (PR #671
seed auto_owner_grant_backfill), 240 (PR #676 identity_persons HRIS
extra fields), and 241 (PR #680 re-enable auto_owner_grant_backfill)
between the start of this branch and merge.

Two changes that go together because the event-driven HRIS rollout
needs both:

1.  ``trigger_source`` (String(16), NOT NULL, default ``'sync_confirm'``)
    captures which Helix code path created the lifecycle row. Values:

    * ``'webhook'`` — SF pushed an Intelligent Services / Cloud
      Application Event Hub / CPI iflow event into
      ``/api/v1/hr/successfactors/webhook``. This is the *primary*
      lifecycle trigger in the target architecture.
    * ``'sync_confirm'`` — IT Admin manually confirmed an HRIS sync
      batch through the admin UI. Falls back to this path when the
      webhook hasn't fired (HRIS-side downtime, missed event, brand-new
      tenant not yet wired).
    * ``'initial_seed'`` — first-ever full sync of an HRIS instance,
      before any lifecycle automation should run.
    * ``'manual'`` — admin override path (e.g. emergency manual
      ``ManualMarkLeaverRequest``).

    Default ``'sync_confirm'`` covers every existing row. The application
    layer threads the source through ``_apply_event`` →
    ``_upsert_lifecycle_event`` so future rows reflect their real path
    of origin; this is the source of truth for the
    "skip coordinator hooks when webhook already fired" reconciliation
    logic — both paths converge on the ledger and the second arrival
    short-circuits.

2.  Composite index on ``(person_id, event_type, effective_date)``.
    ``_upsert_lifecycle_event`` issues this exact predicate on every
    event apply (server/app/domains/hr/service.py:3670) so it deserves
    an index — under the event-driven model both webhook and sync hit
    this path, doubling the read volume.

Idempotent: ``ADD COLUMN`` / ``CREATE INDEX`` guarded with
information_schema lookups so re-runs converge. Downgrade drops both.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "242"
down_revision = "241"
branch_labels = None
depends_on = None


def _column_exists(table: str, column: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.columns "
            "WHERE table_schema = DATABASE() "
            "AND table_name = :t AND column_name = :c"
        ),
        {"t": table, "c": column},
    )
    return (result.scalar() or 0) > 0


def _index_exists(table: str, index: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.statistics "
            "WHERE table_schema = DATABASE() "
            "AND table_name = :t AND index_name = :i"
        ),
        {"t": table, "i": index},
    )
    return (result.scalar() or 0) > 0


def upgrade() -> None:
    if not _column_exists("hr_lifecycle_events", "trigger_source"):
        op.add_column(
            "hr_lifecycle_events",
            sa.Column(
                "trigger_source",
                sa.String(length=16),
                nullable=False,
                server_default="sync_confirm",
            ),
        )

    if not _index_exists("hr_lifecycle_events", "ix_hr_lifecycle_events_person_type_date"):
        op.create_index(
            "ix_hr_lifecycle_events_person_type_date",
            "hr_lifecycle_events",
            ["person_id", "event_type", "effective_date"],
        )


def downgrade() -> None:
    if _index_exists("hr_lifecycle_events", "ix_hr_lifecycle_events_person_type_date"):
        op.drop_index(
            "ix_hr_lifecycle_events_person_type_date",
            table_name="hr_lifecycle_events",
        )
    if _column_exists("hr_lifecycle_events", "trigger_source"):
        op.drop_column("hr_lifecycle_events", "trigger_source")