"""backfill_penerimaan_shift_id

Revision ID: f4386bca7ccf
Revises: f8g9h0i1j2k3
Create Date: 2026-05-07 22:20:06.182541

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'f4386bca7ccf'
down_revision: Union[str, Sequence[str], None] = 'f8g9h0i1j2k3'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Backfill shift_id on existing penerimaan rows where jam_tiba is known.
    # For each row, pick the active shift of the same SPBU whose time range
    # contains jam_tiba.  When multiple shifts match (rare overlap edge case)
    # we pick the one with the smallest shift id (deterministic).
    #
    # Overnight shifts have jam_selesai < jam_mulai, so the containment check
    # splits into two arms:
    #   normal  : jam_tiba >= jam_mulai  AND jam_tiba < jam_selesai
    #   overnight: jam_tiba >= jam_mulai  OR  jam_tiba < jam_selesai
    op.execute("""
        UPDATE penerimaan p
        SET shift_id = (
            SELECT s.id
            FROM master_spbu_shift s
            WHERE s.spbu_id    = p.spbu_id
              AND s.is_active  = true
              AND s.deleted_at IS NULL
              AND (
                CASE
                  WHEN s.jam_selesai > s.jam_mulai THEN
                    p.jam_tiba >= s.jam_mulai AND p.jam_tiba < s.jam_selesai
                  ELSE
                    p.jam_tiba >= s.jam_mulai OR  p.jam_tiba < s.jam_selesai
                END
              )
            ORDER BY s.id
            LIMIT 1
        )
        WHERE p.shift_id IS NULL
          AND p.jam_tiba IS NOT NULL
    """)


def downgrade() -> None:
    # No safe way to reverse a data backfill — leave as-is
    pass