work-club-manager/backend/WorkClub.Infrastructure/Seed/SeedDataService.cs

using System.Security.Cryptography;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using WorkClub.Domain.Entities;
using WorkClub.Domain.Enums;
using WorkClub.Infrastructure.Data;

namespace WorkClub.Infrastructure.Seed;

public class SeedDataService
{
    private readonly IServiceScopeFactory _serviceScopeFactory;

    public SeedDataService(IServiceScopeFactory serviceScopeFactory)
    {
        _serviceScopeFactory = serviceScopeFactory;
    }

    public async Task SeedAsync()
    {
        using var scope = _serviceScopeFactory.CreateScope();
        var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        
        await context.Database.MigrateAsync();
        
        using var transaction = await context.Database.BeginTransactionAsync();

        // Enable RLS on all tenant tables
        await context.Database.ExecuteSqlRawAsync(@"
            ALTER TABLE clubs ENABLE ROW LEVEL SECURITY;
            ALTER TABLE clubs FORCE ROW LEVEL SECURITY;
            ALTER TABLE members ENABLE ROW LEVEL SECURITY;
            ALTER TABLE members FORCE ROW LEVEL SECURITY;
            ALTER TABLE work_items ENABLE ROW LEVEL SECURITY;
            ALTER TABLE work_items FORCE ROW LEVEL SECURITY;
            ALTER TABLE shifts ENABLE ROW LEVEL SECURITY;
            ALTER TABLE shifts FORCE ROW LEVEL SECURITY;
            ALTER TABLE shift_signups ENABLE ROW LEVEL SECURITY;
            ALTER TABLE shift_signups FORCE ROW LEVEL SECURITY;
        ");

        // Create tenant isolation policies (idempotent)
        await context.Database.ExecuteSqlRawAsync(@"
            DO $$ BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='clubs' AND policyname='tenant_isolation_policy') THEN
              CREATE POLICY tenant_isolation_policy ON clubs FOR ALL USING ((""TenantId"")::text = current_setting('app.current_tenant_id', true));
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='members' AND policyname='tenant_isolation_policy') THEN
              CREATE POLICY tenant_isolation_policy ON members FOR ALL USING ((""TenantId"")::text = current_setting('app.current_tenant_id', true));
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='work_items' AND policyname='tenant_isolation_policy') THEN
              CREATE POLICY tenant_isolation_policy ON work_items FOR ALL USING ((""TenantId"")::text = current_setting('app.current_tenant_id', true));
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='shifts' AND policyname='tenant_isolation_policy') THEN
              CREATE POLICY tenant_isolation_policy ON shifts FOR ALL USING ((""TenantId"")::text = current_setting('app.current_tenant_id', true));
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='shift_signups' AND policyname='tenant_isolation_policy') THEN
              CREATE POLICY tenant_isolation_policy ON shift_signups FOR ALL USING (""ShiftId"" IN (SELECT ""Id"" FROM shifts WHERE (""TenantId"")::text = current_setting('app.current_tenant_id', true)));
            END IF;
            END $$;
        ");

        // Create admin bypass policies (idempotent)
        await context.Database.ExecuteSqlRawAsync(@"
            DO $$
            BEGIN
              IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_admin') THEN
                CREATE ROLE app_admin;
              END IF;
            END
            $$;
            GRANT app_admin TO app;
            GRANT USAGE ON SCHEMA public TO app_admin;
            GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admin;
            GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_admin;
            ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public GRANT ALL ON TABLES TO app_admin;
            ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public GRANT ALL ON SEQUENCES TO app_admin;
        ");

        await context.Database.ExecuteSqlRawAsync(@"
            DO $$ BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='clubs' AND policyname='bypass_rls_policy') THEN
              CREATE POLICY bypass_rls_policy ON clubs FOR ALL TO app_admin USING (true);
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='members' AND policyname='bypass_rls_policy') THEN
              CREATE POLICY bypass_rls_policy ON members FOR ALL TO app_admin USING (true);
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='work_items' AND policyname='bypass_rls_policy') THEN
              CREATE POLICY bypass_rls_policy ON work_items FOR ALL TO app_admin USING (true);
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='shifts' AND policyname='bypass_rls_policy') THEN
              CREATE POLICY bypass_rls_policy ON shifts FOR ALL TO app_admin USING (true);
            END IF;
            IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='shift_signups' AND policyname='bypass_rls_policy') THEN
              CREATE POLICY bypass_rls_policy ON shift_signups FOR ALL TO app_admin USING (true);
            END IF;
             END $$;
         ");

        await context.Database.ExecuteSqlRawAsync("SET LOCAL ROLE app_admin");

        // Seed clubs
        if (!context.Clubs.Any())
        {
            var clubs = new List<Club>
            {
                new Club
                {
                    Id = Guid.NewGuid(),
                    TenantId = GenerateDeterministicGuid("Sunrise Tennis Club"),
                    Name = "Sunrise Tennis Club",
                    SportType = SportType.Tennis,
                    Description = "Community tennis club for all skill levels",
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new Club
                {
                    Id = Guid.NewGuid(),
                    TenantId = GenerateDeterministicGuid("Valley Cycling Club"),
                    Name = "Valley Cycling Club",
                    SportType = SportType.Cycling,
                    Description = "Cycling enthusiasts community",
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                }
            };

            context.Clubs.AddRange(clubs);
            await context.SaveChangesAsync();
        }

        // Get clubs for member seeding
        var tennisClub = context.Clubs.First(c => c.Name == "Sunrise Tennis Club");
        var cyclingClub = context.Clubs.First(c => c.Name == "Valley Cycling Club");

        // Seed members
        if (!context.Members.Any())
        {
            var members = new List<Member>
            {
                // admin@test.com: Admin in Club 1, Member in Club 2
                new Member
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    ExternalUserId = "admin-user-id",
                    DisplayName = "Admin User",
                    Email = "admin@test.com",
                    Role = ClubRole.Admin,
                    ClubId = tennisClub.Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new Member
                {
                    Id = Guid.NewGuid(),
                    TenantId = cyclingClub.TenantId,
                    ExternalUserId = "admin-user-id",
                    DisplayName = "Admin User",
                    Email = "admin@test.com",
                    Role = ClubRole.Member,
                    ClubId = cyclingClub.Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                // manager@test.com: Manager in Club 1
                new Member
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    ExternalUserId = "manager-user-id",
                    DisplayName = "Manager User",
                    Email = "manager@test.com",
                    Role = ClubRole.Manager,
                    ClubId = tennisClub.Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                // member1@test.com: Member in Club 1 and Club 2
                new Member
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    ExternalUserId = "member1-user-id",
                    DisplayName = "Member One",
                    Email = "member1@test.com",
                    Role = ClubRole.Member,
                    ClubId = tennisClub.Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new Member
                {
                    Id = Guid.NewGuid(),
                    TenantId = cyclingClub.TenantId,
                    ExternalUserId = "member1-user-id",
                    DisplayName = "Member One",
                    Email = "member1@test.com",
                    Role = ClubRole.Member,
                    ClubId = cyclingClub.Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                // member2@test.com: Member in Club 1
                new Member
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    ExternalUserId = "member2-user-id",
                    DisplayName = "Member Two",
                    Email = "member2@test.com",
                    Role = ClubRole.Member,
                    ClubId = tennisClub.Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                // viewer@test.com: Viewer in Club 1
                new Member
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    ExternalUserId = "viewer-user-id",
                    DisplayName = "Viewer User",
                    Email = "viewer@test.com",
                    Role = ClubRole.Viewer,
                    ClubId = tennisClub.Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                }
            };

            context.Members.AddRange(members);
            await context.SaveChangesAsync();
        }

        // Get admin member IDs for work item creation
        var adminMembers = context.Members.Where(m => m.Email == "admin@test.com").ToList();
        var managerMember = context.Members.First(m => m.Email == "manager@test.com");
        var member1Members = context.Members.Where(m => m.Email == "member1@test.com").ToList();
        var member2Member = context.Members.First(m => m.Email == "member2@test.com");

        // Seed work items
        if (!context.WorkItems.Any())
        {
            var workItems = new List<WorkItem>
            {
                // Club 1 - Tennis Club (5 items, all states)
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Court renovation",
                    Description = "Resurface main court",
                    Status = WorkItemStatus.Open,
                    AssigneeId = null,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    ClubId = tennisClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(14),
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Equipment order",
                    Description = "Purchase new tennis rackets and balls",
                    Status = WorkItemStatus.Assigned,
                    AssigneeId = managerMember.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    ClubId = tennisClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(7),
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Tournament planning",
                    Description = "Organize annual summer tournament",
                    Status = WorkItemStatus.InProgress,
                    AssigneeId = member1Members.First(m => m.ClubId == tennisClub.Id).Id,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    ClubId = tennisClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(30),
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Member handbook review",
                    Description = "Update and review club rules handbook",
                    Status = WorkItemStatus.Review,
                    AssigneeId = member2Member.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    ClubId = tennisClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(21),
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Website update",
                    Description = "Update club website with new photos",
                    Status = WorkItemStatus.Done,
                    AssigneeId = managerMember.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    ClubId = tennisClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(-5),
                    CreatedAt = DateTimeOffset.UtcNow.AddDays(-10),
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                // Club 2 - Cycling Club (3 items)
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = cyclingClub.TenantId,
                    Title = "Route mapping",
                    Description = "Create new cycling routes for summer",
                    Status = WorkItemStatus.Open,
                    AssigneeId = null,
                    CreatedById = adminMembers.First(m => m.ClubId == cyclingClub.Id).Id,
                    ClubId = cyclingClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(21),
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = cyclingClub.TenantId,
                    Title = "Safety training",
                    Description = "Organize safety and maintenance training",
                    Status = WorkItemStatus.Assigned,
                    AssigneeId = member1Members.First(m => m.ClubId == cyclingClub.Id).Id,
                    CreatedById = adminMembers.First(m => m.ClubId == cyclingClub.Id).Id,
                    ClubId = cyclingClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(14),
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new WorkItem
                {
                    Id = Guid.NewGuid(),
                    TenantId = cyclingClub.TenantId,
                    Title = "Group ride coordination",
                    Description = "Schedule and coordinate weekly group rides",
                    Status = WorkItemStatus.InProgress,
                    AssigneeId = adminMembers.First(m => m.ClubId == cyclingClub.Id).Id,
                    CreatedById = adminMembers.First(m => m.ClubId == cyclingClub.Id).Id,
                    ClubId = cyclingClub.Id,
                    DueDate = DateTimeOffset.UtcNow.AddDays(7),
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                }
            };

            context.WorkItems.AddRange(workItems);
            await context.SaveChangesAsync();
        }

        // Seed shifts
        if (!context.Shifts.Any())
        {
            var now = DateTimeOffset.UtcNow;
            var shifts = new List<Shift>
            {
                // Club 1 - Tennis Club (3 shifts)
                new Shift
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Court Maintenance - Yesterday",
                    Description = "Daily court cleaning and maintenance",
                    Location = "Main Court",
                    StartTime = now.AddDays(-1).Date.ToLocalTime().AddHours(8),
                    EndTime = now.AddDays(-1).Date.ToLocalTime().AddHours(12),
                    Capacity = 2,
                    ClubId = tennisClub.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new Shift
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Court Maintenance - Today",
                    Description = "Daily court cleaning and maintenance",
                    Location = "Main Court",
                    StartTime = now.Date.ToLocalTime().AddHours(14),
                    EndTime = now.Date.ToLocalTime().AddHours(18),
                    Capacity = 3,
                    ClubId = tennisClub.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new Shift
                {
                    Id = Guid.NewGuid(),
                    TenantId = tennisClub.TenantId,
                    Title = "Tournament Setup - Next Week",
                    Description = "Setup and preparation for summer tournament",
                    Location = "All Courts",
                    StartTime = now.AddDays(7).Date.ToLocalTime().AddHours(9),
                    EndTime = now.AddDays(7).Date.ToLocalTime().AddHours(17),
                    Capacity = 5,
                    ClubId = tennisClub.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == tennisClub.Id).Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                // Club 2 - Cycling Club (2 shifts)
                new Shift
                {
                    Id = Guid.NewGuid(),
                    TenantId = cyclingClub.TenantId,
                    Title = "Group Ride - Today",
                    Description = "Weekly morning group ride",
                    Location = "Park entrance",
                    StartTime = now.Date.ToLocalTime().AddHours(7),
                    EndTime = now.Date.ToLocalTime().AddHours(9),
                    Capacity = 10,
                    ClubId = cyclingClub.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == cyclingClub.Id).Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                },
                new Shift
                {
                    Id = Guid.NewGuid(),
                    TenantId = cyclingClub.TenantId,
                    Title = "Maintenance Workshop - Next Week",
                    Description = "Bike maintenance and repair workshop",
                    Location = "Club shed",
                    StartTime = now.AddDays(7).Date.ToLocalTime().AddHours(10),
                    EndTime = now.AddDays(7).Date.ToLocalTime().AddHours(14),
                    Capacity = 4,
                    ClubId = cyclingClub.Id,
                    CreatedById = adminMembers.First(m => m.ClubId == cyclingClub.Id).Id,
                    CreatedAt = DateTimeOffset.UtcNow,
                    UpdatedAt = DateTimeOffset.UtcNow
                }
            };

            context.Shifts.AddRange(shifts);
            await context.SaveChangesAsync();
        }

        // Seed shift signups
        if (!context.ShiftSignups.Any())
        {
            var shifts = context.Shifts.ToList();
            var signups = new List<ShiftSignup>();

            // Add some signups for Tennis Club shifts
            var tennisShifts = shifts.Where(s => s.ClubId == tennisClub.Id).ToList();
            if (tennisShifts.Count > 0)
            {
                var tennyMembers = context.Members.Where(m => m.ClubId == tennisClub.Id).ToList();
                if (tennyMembers.Count > 0)
                {
                    signups.Add(new ShiftSignup
                    {
                        Id = Guid.NewGuid(),
                        TenantId = tennisClub.TenantId,
                        ShiftId = tennisShifts[0].Id,
                        MemberId = tennyMembers[0].Id,
                        SignedUpAt = DateTimeOffset.UtcNow
                    });

                    if (tennyMembers.Count > 1)
                    {
                        signups.Add(new ShiftSignup
                        {
                            Id = Guid.NewGuid(),
                            TenantId = tennisClub.TenantId,
                            ShiftId = tennisShifts[0].Id,
                            MemberId = tennyMembers[1].Id,
                            SignedUpAt = DateTimeOffset.UtcNow
                        });
                    }
                }
            }

            // Add some signups for Cycling Club shifts
            var cyclingShifts = shifts.Where(s => s.ClubId == cyclingClub.Id).ToList();
            if (cyclingShifts.Count > 0)
            {
                var cyclingMembers = context.Members.Where(m => m.ClubId == cyclingClub.Id).ToList();
                if (cyclingMembers.Count > 0)
                {
                    signups.Add(new ShiftSignup
                    {
                        Id = Guid.NewGuid(),
                        TenantId = cyclingClub.TenantId,
                        ShiftId = cyclingShifts[0].Id,
                        MemberId = cyclingMembers[0].Id,
                        SignedUpAt = DateTimeOffset.UtcNow
                    });
                }
            }

            if (signups.Count > 0)
            {
                context.ShiftSignups.AddRange(signups);
                await context.SaveChangesAsync();
            }
        }

        await context.Database.ExecuteSqlRawAsync("RESET ROLE");
        await transaction.CommitAsync();
    }

    private static string GenerateDeterministicGuid(string input)
    {
        // Generate a deterministic GUID from a string using MD5
        var hash = MD5.HashData(Encoding.UTF8.GetBytes(input));
        return new Guid(hash.Take(16).ToArray()).ToString();
    }
}