'''Fix role permission wildcard patterns to canonical 2-segment schema

Revision ID: 0141
Revises: 0140
Create Date: 2026-08-23

Migration 0019 seeded default roles with 3-segment permission patterns
(core:*:read etc.). The runtime matcher (_matches_permission) compares
segment counts strictly, so those patterns could never match any
2-segment requirement - editor/viewer roles were silently dead.

Canonical schema is module:action (2 segments, * wildcards allowed).
core:*:X means all modules with action X, so it converts to *:X.
'''

from alembic import op

# revision identifiers, used by Alembic.
revision = '0141'
down_revision = '0140'
branch_labels = None
depends_on = None

# Rebuild the permissions JSONB object, rewriting every key that starts
# with the dead 'core:' prefix to its 2-segment equivalent ('*:X').
_UPGRADE_SQL = '''
UPDATE roles
SET permissions = sub.new_perms,
    permission_version = permission_version + 1
FROM (
    SELECT
        r.id AS role_id,
        jsonb_object_agg(
            CASE WHEN k LIKE 'core:%'
                 THEN '*:' || split_part(k, ':', 3)
                 ELSE k END,
            v
        ) AS new_perms
    FROM roles r,
         jsonb_each(r.permissions) AS e(k, v)
    GROUP BY r.id
) AS sub
WHERE roles.id = sub.role_id
  AND EXISTS (
      SELECT 1 FROM jsonb_object_keys(roles.permissions) k
      WHERE k LIKE 'core:%'
  )
'''

# Reverse: map '*:X' back to 'core:*:X' only for keys that came from the
# original seeding pattern. Roles that legitimately use '*:X' without a
# matching 'core:*:X' history are left untouched (best-effort downgrade).
_DOWNGRADE_SQL = '''
UPDATE roles
SET permissions = sub.new_perms,
    permission_version = permission_version + 1
FROM (
    SELECT
        r.id AS role_id,
        jsonb_object_agg(
            CASE WHEN k = '*:' || split_part(k, ':', 2)
                  AND k <> '*:*'
                 THEN 'core:*:' || split_part(k, ':', 2)
                 ELSE k END,
            v
        ) AS new_perms
    FROM roles r,
         jsonb_each(r.permissions) AS e(k, v)
    GROUP BY r.id
) AS sub
WHERE roles.id = sub.role_id
  AND EXISTS (
      SELECT 1 FROM jsonb_object_keys(roles.permissions) k
      WHERE k = '*:' || split_part(k, ':', 2) AND k <> '*:*'
  )
'''


def upgrade() -> None:
    op.execute(_UPGRADE_SQL)


def downgrade() -> None:
    op.execute(_DOWNGRADE_SQL)