"""RBAC 006 - Backfill non-owner merchant_users with Staff role

PRD-008 RBAC — Migration 6 of 7

For every merchant_users row where is_owner = FALSE that does not yet have
a user_roles entry, insert a user_roles row assigning the Staff role.

This completes the full backfill so that every existing merchant team member
has an RBAC role assignment before the new RBAC enforcement code is activated.

Revision ID: rbac006f7a8b9c
Revises: rbac005e6f7a8b
Create Date: 2026-03-19 00:00:06.000000

"""
from typing import Sequence, Union

from alembic import op


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


def upgrade() -> None:
    # Backfill non-owner merchant_users with Staff role.
    # Only inserts rows that don't already have a user_roles entry
    # (guards against re-runs and against partial backfills from migration 5).
    op.execute(
        """
        INSERT INTO user_roles (user_id, role_id, merchant_id, is_primary, created_at)
        SELECT
            mu.user_id,
            r.id        AS role_id,
            mu.merchant_id,
            true        AS is_primary,
            NOW()       AS created_at
        FROM   merchant_users mu
        CROSS  JOIN roles r
        WHERE  mu.is_owner = false
          AND  r.slug = 'staff'
          AND  mu.user_id IS NOT NULL
          AND  NOT EXISTS (
                   SELECT 1
                   FROM   user_roles ur
                   WHERE  ur.user_id     = mu.user_id
                     AND  ur.merchant_id = mu.merchant_id
               )
        """
    )


def downgrade() -> None:
    # Remove only the staff-role rows that were backfilled by this migration.
    # Owner rows (inserted by migration 5) are not touched.
    op.execute(
        """
        DELETE FROM user_roles
        WHERE role_id = (SELECT id FROM roles WHERE slug = 'staff' AND is_system = true)
          AND user_id IN (
                  SELECT user_id
                  FROM   merchant_users
                  WHERE  is_owner = false
              )
        """
    )