This folder contains SQL scripts for managing the ShiftIQ database.

Purpose: Reset database and populate with comprehensive test data

What it does:

• Preserves the [email protected] admin account
• Removes all other existing data (users, teams, locations, shifts, assignments)
• Creates 6 fictional healthcare locations
• Creates 13 teams with specialized departments
• Creates 44 users (13 team leads + 30 staff members + 1 admin preserved)
• Creates 47 shifts (41 one-time + 6 recurring) spanning November-December 2025
• Assigns some staff to shifts
• Fixes user role enum values (TeamMember=1, TeamLead=2, TeamAdmin=3)

How to run:

cd C:\ROTA\src\StaffRota.Web
Get-Content ..\..\database-scripts\reset_and_populate_db.sql | sqlite3 StaffRotaDb.sqlite

Test Data Included:

Locations:

1. Downtown Medical Center (50 capacity)
2. Westside Clinic (25 capacity)
3. Northgate Hospital (75 capacity)
4. Riverside Practice (15 capacity)
5. Central Emergency Unit (40 capacity)
6. Suburban Health Hub (30 capacity)

Teams:

1. Emergency Response
2. ICU Care
3. General Ward
4. Outpatient Services
5. Diagnostics
6. Surgical Unit
7. Pediatrics
8. Maternity
9. Primary Care
10. Trauma Team
11. Ambulance Service
12. Wellness Center
13. Mental Health

User Accounts:

• Admin: [email protected] / Password123! (preserved)
• All test users: Password is Password123!
• Team Leads: 13 managers with role TeamLead (2)
• Staff Members: 30 staff with role TeamMember (1)

Shifts:

• 41 one-time shifts (various departments, times, dates)
• 6 recurring shifts with patterns:
  • Weekly Monday/Wednesday/Friday
  • Weekly Tuesday/Thursday
  • Weekend coverage (Saturday/Sunday)
  • Daily night shifts
  • Bi-weekly patterns

The archive/ folder contains old SQL scripts that were used during development but are no longer needed:

• add_location_column.sql - Migration script (superseded by EF migrations)
• add_location_to_teams.sql - Migration script (superseded by EF migrations)
• create_admin_account.sql - Old admin creation (now in reset script)
• create_shift_assignments_table.sql - Migration script (superseded by EF migrations)
• create_test_user.sql - Old test user creation (now in reset script)
• delete_admin.sql - Utility script (archived)
• shift_focused_test_data.sql - Old test data (superseded by reset_and_populate_db.sql)
• test_data_setup.sql - Old test data (superseded)
• test_data_setup_corrected.sql - Old test data (superseded)
• update_shift_teamids.sql - Migration script (superseded)
• update_users.sql - Migration script (superseded)

Note: These archived scripts are kept for reference only and should not be used.

cd C:\ROTA\src\StaffRota.Web
sqlite3 StaffRotaDb.sqlite

# Inside SQLite shell:
.tables                          # List all tables
.schema Users                    # Show table structure
SELECT * FROM Users;             # Query data
SELECT COUNT(*) FROM Shifts;     # Count records
.exit                            # Exit SQLite

-- View all users with their teams
SELECT u.FirstName || ' ' || u.LastName as Name, 
       u.Email, 
       u.JobTitle, 
       t.Name as Team,
       CASE u.Role 
         WHEN 1 THEN 'TeamMember' 
         WHEN 2 THEN 'TeamLead' 
         WHEN 3 THEN 'TeamAdmin' 
       END as Role
FROM Users u 
LEFT JOIN Teams t ON u.TeamId = t.Id;

-- View upcoming shifts
SELECT s.Title, 
       s.StartDateTime, 
       s.EndDateTime, 
       l.Name as Location, 
       t.Name as Team
FROM Shifts s 
LEFT JOIN Locations l ON s.LocationId = l.Id 
LEFT JOIN Teams t ON s.TeamId = t.Id 
WHERE s.StartDateTime > datetime('now')
ORDER BY s.StartDateTime;

-- Check shift assignments
SELECT s.Title,
       u.FirstName || ' ' || u.LastName as AssignedTo,
       sa.IsConfirmed
FROM ShiftAssignments sa
JOIN Shifts s ON sa.ShiftId = s.Id
JOIN Users u ON sa.UserId = u.Id;

1. Always backup before running scripts: Copy StaffRotaDb.sqlite before making changes
2. Stop the backend first: Close the .NET application before running scripts
3. Test in development: Don't run these scripts in production
4. Use Entity Framework migrations: For schema changes, use EF migrations instead of raw SQL

Database locked error:

• Close all SQLite connections
• Stop the backend application
• Close any open SQLite browser tools

Foreign key constraint errors:

• The reset script temporarily disables FK constraints with PRAGMA foreign_keys = OFF
• Make sure the script completes in full

Role enum errors:

• Ensure Role values are 1, 2, or 3 (not 0)
• Run: UPDATE Users SET Role = 1 WHERE Role = 0; if needed