"""RBAC 005 - Seed system roles and backfill owner user_roles rows

PRD-008 RBAC — Migration 5 of 7

1. Insert 3 system roles: Owner (rank=3), Admin (rank=2), Staff (rank=1).
2. Assign permissions to each system role via roles_permissions junction table:
   - Owner:  ALL 32 permissions
   - Admin:  ALL except users:manage_admins, users:transfer_ownership, billing:manage
   - Staff:  transactions:view, transactions:create, invoices:view, customers:view, reports:view
3. Backfill user_roles for all existing merchant_users rows where is_owner = TRUE.

Revision ID: rbac005e6f7a8b
Revises: rbac004d5e6f7a
Create Date: 2026-03-19 00:00:05.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    # ── 1. Insert 3 system roles ──────────────────────────────────────────────
    # is_system=true, is_default=false, merchant_id=NULL (platform-wide).
    # created_by_id=NULL because these are seeded by the system, not a human.
    op.execute(
        """
        INSERT INTO roles (label, slug, description, is_default, is_system, role_rank, created_at)
        VALUES
        (
            'Owner',
            'owner',
            'Full access to all features and settings. Can transfer ownership and manage billing.',
            false,
            true,
            3,
            NOW()
        ),
        (
            'Admin',
            'admin',
            'Full operational access. Cannot manage owner-level roles or billing.',
            false,
            true,
            2,
            NOW()
        ),
        (
            'Staff',
            'staff',
            'Basic access to day-to-day operational tasks.',
            true,
            true,
            1,
            NOW()
        )
        """
    )

    # ── 2. Assign permissions to each system role ─────────────────────────────

    # Owner → ALL permissions
    op.execute(
        """
        INSERT INTO roles_permissions (role_id, permission_id)
        SELECT r.id, p.id
        FROM   roles r
        CROSS  JOIN permissions p
        WHERE  r.slug = 'owner'
        """
    )

    # Admin → ALL except users:manage_admins, users:transfer_ownership, billing:manage
    op.execute(
        """
        INSERT INTO roles_permissions (role_id, permission_id)
        SELECT r.id, p.id
        FROM   roles r
        CROSS  JOIN permissions p
        WHERE  r.slug = 'admin'
          AND  p.slug NOT IN (
                   'users:manage_admins',
                   'users:transfer_ownership',
                   'billing:manage'
               )
        """
    )

    # Staff → 5 permissions only
    op.execute(
        """
        INSERT INTO roles_permissions (role_id, permission_id)
        SELECT r.id, p.id
        FROM   roles r
        CROSS  JOIN permissions p
        WHERE  r.slug = 'staff'
          AND  p.slug IN (
                   'transactions:view',
                   'transactions:create',
                   'invoices:view',
                   'customers:view',
                   'reports:view'
               )
        """
    )

    # ── 3. Backfill user_roles for existing owners ────────────────────────────
    # For every merchant_users row where is_owner=TRUE, insert a user_roles row
    # with the Owner role. Skip any that already have a user_roles entry to make
    # this idempotent against re-runs.
    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 = true
          AND  r.slug = 'owner'
          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 backfilled owner user_roles rows (those assigned owner slug role)
    op.execute(
        """
        DELETE FROM user_roles
        WHERE role_id IN (SELECT id FROM roles WHERE slug IN ('owner', 'admin', 'staff'))
        """
    )

    # Remove roles_permissions for system roles
    op.execute(
        """
        DELETE FROM roles_permissions
        WHERE role_id IN (SELECT id FROM roles WHERE slug IN ('owner', 'admin', 'staff'))
        """
    )

    # Remove system roles
    op.execute(
        """
        DELETE FROM roles
        WHERE slug IN ('owner', 'admin', 'staff') AND is_system = true
        """
    )