using Microsoft.EntityFrameworkCore.Migrations;

namespace SmartStack.Infrastructure.Data.Migrations.Core;

/// <summary>
/// Seed default RBAC roles for SmartStack
/// Generated on {{timestamp}}
///
/// All default roles have Scope = 'Core' (protected, cannot be deleted by clients)
/// Default Roles (aligned with Microsoft Fabric):
/// - Admin: Full access (read, create, update, delete)
/// - Member: Can write data (read, create, update) + manage users (add, update permissions) - no delete users, cannot assign roles >= own role
/// - Contributor: Can write data (read, create, update) - no delete, no user management
/// - Viewer: Read-only access (read)
///
/// Permission Matrix:
/// | Capability              | Admin | Member | Contributor | Viewer |
/// |-------------------------|-------|--------|-------------|--------|
/// | Delete data             | ✅    | ❌     | ❌          | ❌     |
/// | Delete users            | ✅    | ❌     | ❌          | ❌     |
/// | Add/update users        | ✅    | ✅     | ❌          | ❌     |
/// | Change permissions      | ✅    | ✅*    | ❌          | ❌     |
/// | Write data              | ✅    | ✅     | ✅          | ❌     |
/// | Create items            | ✅    | ✅     | ✅          | ❌     |
/// | Read data               | ✅    | ✅     | ✅          | ✅     |
///
/// * Member cannot assign roles >= own role
/// </summary>
public partial class {{migrationName}} : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // ============================================================================
        // DEFAULT ROLES SEED (Microsoft Fabric Model)
        // ============================================================================
        // These roles follow Microsoft Fabric RBAC conventions
        // Permissions are customizable per module via NavRoute
        // ============================================================================

        // Admin Role - Full Access
        migrationBuilder.Sql(@"
            DECLARE @AdminRoleId UNIQUEIDENTIFIER = NEWID();

            IF NOT EXISTS (SELECT 1 FROM core.auth_Roles WHERE Code = 'admin')
            BEGIN
                INSERT INTO core.auth_Roles (
                    Id,
                    Code,
                    Scope,
                    Name,
                    Description,
                    IsDeleted,
                    CreatedAt,
                    UpdatedAt
                )
                VALUES (
                    @AdminRoleId,
                    'admin',
                    'Core',
                    'Admin',
                    'Full access to all features (read, create, update, delete)',
                    0,
                    GETUTCDATE(),
                    GETUTCDATE()
                );

                PRINT 'Role created: Admin (Scope: Core)';

                -- Grant ALL permissions to Admin
                INSERT INTO core.auth_RolePermissions (RoleId, PermissionId)
                SELECT @AdminRoleId, Id
                FROM core.auth_Permissions
                WHERE IsDeleted = 0;

                PRINT 'All permissions granted to Admin';
            END
        ");

        // Member Role - Can Write and Manage Users (with restrictions)
        migrationBuilder.Sql(@"
            DECLARE @MemberRoleId UNIQUEIDENTIFIER = NEWID();

            IF NOT EXISTS (SELECT 1 FROM core.auth_Roles WHERE Code = 'member')
            BEGIN
                INSERT INTO core.auth_Roles (
                    Id,
                    Code,
                    Scope,
                    Name,
                    Description,
                    IsDeleted,
                    CreatedAt,
                    UpdatedAt
                )
                VALUES (
                    @MemberRoleId,
                    'member',
                    'Core',
                    'Member',
                    'Can read, create, and update content + manage users (add, update) - no delete',
                    0,
                    GETUTCDATE(),
                    GETUTCDATE()
                );

                PRINT 'Role created: Member (Scope: Core)';

                -- Grant read, create, update permissions (excluding delete)
                INSERT INTO core.auth_RolePermissions (RoleId, PermissionId)
                SELECT @MemberRoleId, Id
                FROM core.auth_Permissions
                WHERE IsDeleted = 0
                  AND Code NOT LIKE '%.delete';

                PRINT 'Read, create, update permissions granted to Member (including user management)';
                PRINT 'Note: Business logic must enforce Member cannot assign roles >= own role';
            END
        ");

        // Contributor Role - Can Write Content (no user management, no delete)
        migrationBuilder.Sql(@"
            DECLARE @ContributorRoleId UNIQUEIDENTIFIER = NEWID();

            IF NOT EXISTS (SELECT 1 FROM core.auth_Roles WHERE Code = 'contributor')
            BEGIN
                INSERT INTO core.auth_Roles (
                    Id,
                    Code,
                    Scope,
                    Name,
                    Description,
                    IsDeleted,
                    CreatedAt,
                    UpdatedAt
                )
                VALUES (
                    @ContributorRoleId,
                    'contributor',
                    'Core',
                    'Contributor',
                    'Can read, create, and update content (no user management, no delete)',
                    0,
                    GETUTCDATE(),
                    GETUTCDATE()
                );

                PRINT 'Role created: Contributor (Scope: Core)';

                -- Grant read, create, update permissions (excluding delete and user management)
                INSERT INTO core.auth_RolePermissions (RoleId, PermissionId)
                SELECT @ContributorRoleId, Id
                FROM core.auth_Permissions
                WHERE IsDeleted = 0
                  AND Code NOT LIKE '%.delete'
                  AND Code NOT LIKE 'platform.administration.users.%'
                  AND Code NOT LIKE 'platform.administration.roles.%';

                PRINT 'Read, create, update permissions granted to Contributor (excluding user management)';
            END
        ");

        // Viewer Role - Read-Only Access
        migrationBuilder.Sql(@"
            DECLARE @ViewerRoleId UNIQUEIDENTIFIER = NEWID();

            IF NOT EXISTS (SELECT 1 FROM core.auth_Roles WHERE Code = 'viewer')
            BEGIN
                INSERT INTO core.auth_Roles (
                    Id,
                    Code,
                    Scope,
                    Name,
                    Description,
                    IsDeleted,
                    CreatedAt,
                    UpdatedAt
                )
                VALUES (
                    @ViewerRoleId,
                    'viewer',
                    'Core',
                    'Viewer',
                    'Read-only access to all assigned features',
                    0,
                    GETUTCDATE(),
                    GETUTCDATE()
                );

                PRINT 'Role created: Viewer (Scope: Core)';

                -- Grant only read permissions
                INSERT INTO core.auth_RolePermissions (RoleId, PermissionId)
                SELECT @ViewerRoleId, Id
                FROM core.auth_Permissions
                WHERE IsDeleted = 0
                  AND Code LIKE '%.read';

                PRINT 'Read permissions granted to Viewer';
            END
        ");

        // ============================================================================
        // SUMMARY
        // ============================================================================
        migrationBuilder.Sql(@"
            DECLARE @RoleCount INT = (SELECT COUNT(*) FROM core.auth_Roles WHERE IsDeleted = 0);
            DECLARE @PermissionCount INT = (SELECT COUNT(*) FROM core.auth_Permissions WHERE IsDeleted = 0);
            DECLARE @RolePermissionCount INT = (SELECT COUNT(*) FROM core.auth_RolePermissions);

            PRINT '========================================';
            PRINT 'Default Roles Seed Complete (Fabric Model)';
            PRINT 'Roles: ' + CAST(@RoleCount AS NVARCHAR(10));
            PRINT 'Permissions: ' + CAST(@PermissionCount AS NVARCHAR(10));
            PRINT 'Role-Permission Assignments: ' + CAST(@RolePermissionCount AS NVARCHAR(10));
            PRINT '========================================';
            PRINT '';
            PRINT 'Permission Matrix:';
            PRINT '| Capability              | Admin | Member | Contributor | Viewer |';
            PRINT '|-------------------------|-------|--------|-------------|--------|';
            PRINT '| Delete data             | ✅    | ❌     | ❌          | ❌     |';
            PRINT '| Delete users            | ✅    | ❌     | ❌          | ❌     |';
            PRINT '| Add/update users        | ✅    | ✅     | ❌          | ❌     |';
            PRINT '| Change permissions      | ✅    | ✅*    | ❌          | ❌     |';
            PRINT '| Write data              | ✅    | ✅     | ✅          | ❌     |';
            PRINT '| Create items            | ✅    | ✅     | ✅          | ❌     |';
            PRINT '| Read data               | ✅    | ✅     | ✅          | ✅     |';
            PRINT '';
            PRINT '* Member cannot assign roles >= own role (enforced in business logic)';
            PRINT '========================================';
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // ============================================================================
        // ROLLBACK - Remove default roles
        // ============================================================================
        // WARNING: This will remove system roles and all associated permissions.
        // User assignments to these roles will be orphaned.
        // ============================================================================

        // Remove role-permission assignments
        migrationBuilder.Sql(@"
            DELETE rp
            FROM core.auth_RolePermissions rp
            INNER JOIN core.auth_Roles r ON rp.RoleId = r.Id
            WHERE r.Code IN ('admin', 'member', 'contributor', 'viewer');
        ");

        // Soft delete roles
        migrationBuilder.Sql(@"
            UPDATE core.auth_Roles
            SET IsDeleted = 1, UpdatedAt = GETUTCDATE()
            WHERE Code IN ('admin', 'member', 'contributor', 'viewer');
        ");

        migrationBuilder.Sql(@"
            PRINT 'Default roles rollback complete';
        ");
    }
}