"""Migrate legacy role strings (admin/editor/viewer) to real Role records with role_id.

⚠️ Legacy Role Bypass entfernt — alle Admins müssen echte role_id haben

This migration creates Role records for each tenant's built-in roles (admin, editor,
viewer) and links UserTenant.role_id to the corresponding Role. After this migration,
the legacy role string on UserTenant.role is no longer used for permission checks —
all permissions come through the Role-based RBAC system.

Revision ID: 0112
Revises: 0111
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB, UUID as PGUUID

revision = "0112"
down_revision = "0111"
branch_labels = None
depends_on = None


# Permission sets for built-in roles
ADMIN_PERMISSIONS = ["*:*"]

EDITOR_PERMISSIONS = [
    "contacts:read", "contacts:write",
    "users:read", "roles:read", "audit:read",
    "attachments:read", "attachments:write",
    "workflows:read", "workflows:write",
    "sequences:read", "sequences:write",
    "addresses:read", "addresses:write",
    "taxes:read", "taxes:write",
    "currencies:read", "currencies:write",
    "notifications:read", "notifications:write",
    "import_export:read", "import_export:write",
    "user_preferences:read", "user_preferences:write",
]

VIEWER_PERMISSIONS = [
    "contacts:read", "users:read", "roles:read",
    "audit:read", "attachments:read", "workflows:read",
    "sequences:read", "addresses:read", "taxes:read",
    "currencies:read", "notifications:read",
    "import_export:read",
    "user_preferences:read", "user_preferences:write",
]

GUEST_PERMISSIONS = [
    "contacts:read",
    "attachments:read",
    "user_preferences:read",
]


def upgrade() -> None:
    # For each tenant, create Role records for built-in roles and link UserTenant.role_id
    op.execute("""
DO $$
DECLARE
    tenant_rec RECORD;
    admin_role_id UUID;
    editor_role_id UUID;
    viewer_role_id UUID;
    guest_role_id UUID;
BEGIN
    FOR tenant_rec IN SELECT id FROM tenants
    LOOP
        -- Create or find admin role for this tenant
        SELECT id INTO admin_role_id
        FROM roles
        WHERE tenant_id = tenant_rec.id
          AND name = 'admin'
          AND deleted_at IS NULL
        LIMIT 1;

        IF admin_role_id IS NULL THEN
            INSERT INTO roles (id, tenant_id, name, permissions, denied_permissions, field_permissions, permission_version, created_at)
            VALUES (
                gen_random_uuid(),
                tenant_rec.id,
                'admin',
                '["*:*"]'::jsonb,
                '[]'::jsonb,
                '{}'::jsonb,
                1,
                now()
            )
            RETURNING id INTO admin_role_id;
        END IF;

        -- Create or find editor role for this tenant
        SELECT id INTO editor_role_id
        FROM roles
        WHERE tenant_id = tenant_rec.id
          AND name = 'editor'
          AND deleted_at IS NULL
        LIMIT 1;

        IF editor_role_id IS NULL THEN
            INSERT INTO roles (id, tenant_id, name, permissions, denied_permissions, field_permissions, permission_version, created_at)
            VALUES (
                gen_random_uuid(),
                tenant_rec.id,
                'editor',
                '["contacts:read","contacts:write","users:read","roles:read","audit:read","attachments:read","attachments:write","workflows:read","workflows:write","sequences:read","sequences:write","addresses:read","addresses:write","taxes:read","taxes:write","currencies:read","currencies:write","notifications:read","notifications:write","import_export:read","import_export:write","user_preferences:read","user_preferences:write"]'::jsonb,
                '[]'::jsonb,
                '{}'::jsonb,
                1,
                now()
            )
            RETURNING id INTO editor_role_id;
        END IF;

        -- Create or find viewer role for this tenant
        SELECT id INTO viewer_role_id
        FROM roles
        WHERE tenant_id = tenant_rec.id
          AND name = 'viewer'
          AND deleted_at IS NULL
        LIMIT 1;

        IF viewer_role_id IS NULL THEN
            INSERT INTO roles (id, tenant_id, name, permissions, denied_permissions, field_permissions, permission_version, created_at)
            VALUES (
                gen_random_uuid(),
                tenant_rec.id,
                'viewer',
                '["contacts:read","users:read","roles:read","audit:read","attachments:read","workflows:read","sequences:read","addresses:read","taxes:read","currencies:read","notifications:read","import_export:read","user_preferences:read","user_preferences:write"]'::jsonb,
                '[]'::jsonb,
                '{}'::jsonb,
                1,
                now()
            )
            RETURNING id INTO viewer_role_id;
        END IF;

        -- Create or find guest role for this tenant
        SELECT id INTO guest_role_id
        FROM roles
        WHERE tenant_id = tenant_rec.id
          AND name = 'guest'
          AND deleted_at IS NULL
        LIMIT 1;

        IF guest_role_id IS NULL THEN
            INSERT INTO roles (id, tenant_id, name, permissions, denied_permissions, field_permissions, permission_version, created_at)
            VALUES (
                gen_random_uuid(),
                tenant_rec.id,
                'guest',
                '["contacts:read","attachments:read","user_preferences:read"]'::jsonb,
                '[]'::jsonb,
                '{}'::jsonb,
                1,
                now()
            )
            RETURNING id INTO guest_role_id;
        END IF;

        -- Link UserTenant records to the appropriate Role based on legacy role string
        UPDATE user_tenants SET role_id = admin_role_id
        WHERE tenant_id = tenant_rec.id
          AND role = 'admin'
          AND role_id IS NULL;

        UPDATE user_tenants SET role_id = editor_role_id
        WHERE tenant_id = tenant_rec.id
          AND role = 'editor'
          AND role_id IS NULL;

        UPDATE user_tenants SET role_id = viewer_role_id
        WHERE tenant_id = tenant_rec.id
          AND role = 'viewer'
          AND role_id IS NULL;

        UPDATE user_tenants SET role_id = guest_role_id
        WHERE tenant_id = tenant_rec.id
          AND role = 'guest'
          AND role_id IS NULL;
    END LOOP;
END $$;
    """)


def downgrade() -> None:
    # Unlink role_id for built-in role mappings (keep the Role records)
    op.execute("""
    UPDATE user_tenants SET role_id = NULL
    WHERE role IN ('admin', 'editor', 'viewer', 'guest')
      AND role_id IS NOT NULL
      AND EXISTS (
          SELECT 1 FROM roles r
          WHERE r.id = user_tenants.role_id
            AND r.name IN ('admin', 'editor', 'viewer', 'guest')
      );
    """)