/*
===============================================================================
HealtchareDatabse - SQL Server 2025 Healthcare / Mirth Connect Training Database
SYNTHETIC TRAINING DATA ONLY - NO REAL PHI

Enhanced training design:
- 1,000 synthetic patients with 1,000 unique FirstName values
- Beginner-friendly physical column order in dbo.Patient
- Core patient identifiers, names, DOB, country and language kept together
- Additional healthcare fields for insurance, encounters, HL7, labs and auditing
- Birth years span 1930-2026; encounter admissions span 1995-2026
- Data-quality enrichment for coherent City / ProvinceState / Country values
- 16 normalized tables, indexes, teaching views and stored procedures
- Designed for SQL Server + SSMS + Mirth Connect / JDBC demonstrations

WARNING: Drops and rebuilds HealtchareDatabse if it already exists.
===============================================================================
*/

USE master;
GO
IF DB_ID(N'HealtchareDatabse') IS NOT NULL
BEGIN
    ALTER DATABASE HealtchareDatabse SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE HealtchareDatabse;
END;
GO
CREATE DATABASE HealtchareDatabse;
GO
ALTER DATABASE HealtchareDatabse SET RECOVERY SIMPLE;
GO
USE HealtchareDatabse;
GO

CREATE TABLE dbo.Facility(
    FacilityID int IDENTITY PRIMARY KEY,
    FacilityCode varchar(10) NOT NULL UNIQUE,
    FacilityName nvarchar(120) NOT NULL,
    FacilityType varchar(40) NULL,
    City nvarchar(80) NOT NULL,
    ProvinceState nvarchar(80) NOT NULL,
    Country nvarchar(80) NOT NULL,
    TimeZone varchar(80) NULL,
    ActiveFlag bit NOT NULL CONSTRAINT DF_Facility_ActiveFlag DEFAULT 1
);

CREATE TABLE dbo.Department(
    DepartmentID int IDENTITY PRIMARY KEY,
    FacilityID int NOT NULL REFERENCES dbo.Facility(FacilityID),
    DepartmentCode varchar(15) NOT NULL UNIQUE,
    DepartmentName nvarchar(120) NOT NULL,
    ServiceLine nvarchar(80) NOT NULL,
    CostCenterCode varchar(20) NULL,
    DepartmentPhone varchar(30) NULL,
    ActiveFlag bit NOT NULL CONSTRAINT DF_Department_ActiveFlag DEFAULT 1
);

CREATE TABLE dbo.Location(
    LocationID int IDENTITY PRIMARY KEY,
    DepartmentID int NOT NULL REFERENCES dbo.Department(DepartmentID),
    UnitCode varchar(20) NOT NULL,
    RoomCode varchar(20) NOT NULL,
    BedCode varchar(20) NOT NULL,
    LocationDescription nvarchar(150) NOT NULL,
    LocationType varchar(30) NOT NULL,
    Building nvarchar(80) NULL,
    FloorNumber int NULL,
    ActiveFlag bit NOT NULL CONSTRAINT DF_Location_ActiveFlag DEFAULT 1
);

CREATE TABLE dbo.Provider(
    ProviderID int IDENTITY PRIMARY KEY,
    ProviderNumber varchar(20) NOT NULL UNIQUE,
    FirstName nvarchar(60) NOT NULL,
    LastName nvarchar(60) NOT NULL,
    FullName AS (LTRIM(RTRIM(CONCAT(FirstName,N' ',LastName)))),
    ProviderType varchar(40) NULL,
    Credentials varchar(30) NULL,
    Specialty nvarchar(100) NOT NULL,
    DepartmentID int NOT NULL REFERENCES dbo.Department(DepartmentID),
    Phone varchar(30) NULL,
    Email varchar(150) NULL,
    LicenseNumber varchar(30) NOT NULL,
    ActiveFlag bit NOT NULL CONSTRAINT DF_Provider_ActiveFlag DEFAULT 1
);

CREATE TABLE dbo.Patient(
    -- Core identifiers and frequently taught demographics are intentionally first
    -- so SELECT * is easier to demonstrate in beginner YouTube lessons.
    PatientID int IDENTITY PRIMARY KEY,
    EnterpriseMRN varchar(20) NOT NULL UNIQUE,
    FacilityMRN varchar(20) NOT NULL,

    FirstName nvarchar(60) NOT NULL,
    MiddleName nvarchar(60) NULL,
    LastName nvarchar(60) NOT NULL,
    FullName AS (CONCAT(FirstName,N' ',COALESCE(NULLIF(LTRIM(RTRIM(MiddleName)),N'')+N' ',N''),LastName)),
    PreferredName nvarchar(60) NULL,

    DateOfBirth date NOT NULL,
    AgeYears int NULL,
    AgeGroup varchar(30) NOT NULL,
    SexAtBirth varchar(10) NOT NULL,
    GenderIdentity varchar(40) NOT NULL,
    Pronouns varchar(30) NULL,
    MaritalStatus varchar(30) NULL,

    CountryOfBirth nvarchar(80) NOT NULL,
    Citizenship nvarchar(80) NOT NULL,
    Country nvarchar(80) NOT NULL,
    ProvinceState nvarchar(80) NOT NULL,
    City nvarchar(80) NOT NULL,
    PostalCode varchar(20) NOT NULL,

    PrimaryLanguage nvarchar(50) NOT NULL,
    SecondaryLanguage nvarchar(50) NULL,
    InterpreterRequired bit NOT NULL,

    MobilePhone varchar(30) NULL,
    HomePhone varchar(30) NULL,
    EmailAddress varchar(150) NULL,
    PreferredContactMethod varchar(20) NOT NULL,
    AddressLine1 nvarchar(120) NOT NULL,
    AddressLine2 nvarchar(120) NULL,

    HealthCardNumber varchar(25) NULL,
    HealthCardVersion varchar(5) NULL,
    HealthCardExpiry date NULL,
    LegacyMRN varchar(20) NULL,
    NationalIDToken varchar(64) NULL,

    PrimaryCareProviderID int NULL REFERENCES dbo.Provider(ProviderID),
    EmergencyContactName nvarchar(120) NULL,
    EmergencyContactRelationship varchar(40) NULL,
    EmergencyContactPhone varchar(30) NULL,

    BloodType varchar(5) NULL,
    RhFactor char(1) NULL,
    OrganDonorFlag bit NOT NULL,
    SmokingStatus varchar(30) NOT NULL,
    AlcoholUse varchar(30) NOT NULL,
    HeightCm decimal(5,2) NULL,
    WeightKg decimal(6,2) NULL,
    BMI decimal(5,2) NULL,
    PregnancyStatus varchar(30) NOT NULL,
    DisabilityStatus varchar(50) NOT NULL,
    MobilityStatus varchar(50) NOT NULL,
    CommunicationNeeds varchar(100) NULL,

    PrivacyLevel varchar(20) NOT NULL,
    ConsentStatus varchar(30) NOT NULL,
    PatientStatus varchar(30) NOT NULL,
    DeceasedFlag bit NOT NULL,
    DeathDateTime datetime2(0) NULL,
    RegistrationSource varchar(40) NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    UpdatedAt datetime2(0) NOT NULL,
    LastSeenAt datetime2(0) NULL,

    CONSTRAINT CK_Patient_AgeYears CHECK(AgeYears IS NULL OR AgeYears BETWEEN 0 AND 125),
    CONSTRAINT CK_Patient_Deceased CHECK(
        (DeceasedFlag=0 AND DeathDateTime IS NULL) OR
        (DeceasedFlag=1 AND DeathDateTime IS NOT NULL)
    )
);

CREATE TABLE dbo.InsurancePolicy(
    InsurancePolicyID bigint IDENTITY PRIMARY KEY,
    PatientID int NOT NULL REFERENCES dbo.Patient(PatientID),
    PayerCode varchar(20) NULL,
    PayerName nvarchar(120) NOT NULL,
    PlanName nvarchar(120) NOT NULL,
    MemberNumber varchar(40) NOT NULL,
    GroupNumber varchar(40) NULL,
    RelationshipToSubscriber varchar(30) NULL,
    CoverageType varchar(40) NULL,
    FinancialClass varchar(30) NOT NULL,
    EffectiveDate date NOT NULL,
    ExpiryDate date NULL,
    PrimaryFlag bit NOT NULL
);

CREATE TABLE dbo.Encounter(
    EncounterID bigint IDENTITY PRIMARY KEY,
    VisitNumber varchar(25) NOT NULL UNIQUE,
    AccountNumber varchar(30) NULL,
    PatientID int NOT NULL REFERENCES dbo.Patient(PatientID),
    FacilityID int NOT NULL REFERENCES dbo.Facility(FacilityID),
    DepartmentID int NOT NULL REFERENCES dbo.Department(DepartmentID),
    LocationID int NULL REFERENCES dbo.Location(LocationID),
    PatientClass varchar(10) NOT NULL,
    EncounterType varchar(40) NOT NULL,
    EncounterStatus varchar(30) NOT NULL,
    ServiceCode varchar(20) NOT NULL,
    AdmissionType varchar(30) NULL,
    ReasonForVisit nvarchar(120) NULL,
    ChiefComplaint nvarchar(200) NULL,
    AdmitDateTime datetime2(0) NOT NULL,
    DischargeDateTime datetime2(0) NULL,
    AttendingProviderID int NOT NULL REFERENCES dbo.Provider(ProviderID),
    AdmittingProviderID int NULL REFERENCES dbo.Provider(ProviderID),
    ReferringProviderID int NULL REFERENCES dbo.Provider(ProviderID),
    AdmissionSource varchar(40) NOT NULL,
    DischargeDisposition varchar(60) NULL,
    AssignedUnit varchar(20) NULL,
    AssignedRoom varchar(20) NULL,
    AssignedBed varchar(20) NULL,
    PriorUnit varchar(20) NULL,
    PriorRoom varchar(20) NULL,
    PriorBed varchar(20) NULL,
    FinancialClass varchar(30) NOT NULL,
    CreatedAt datetime2(0) NOT NULL,
    UpdatedAt datetime2(0) NOT NULL,
    CONSTRAINT CK_Encounter_Dates CHECK(DischargeDateTime IS NULL OR DischargeDateTime>=AdmitDateTime)
);

CREATE TABLE dbo.ADTEvent(
    ADTEventID bigint IDENTITY PRIMARY KEY,
    EncounterID bigint NOT NULL REFERENCES dbo.Encounter(EncounterID),
    PatientID int NOT NULL REFERENCES dbo.Patient(PatientID),
    TriggerEvent varchar(5) NOT NULL,
    MessageType varchar(15) NOT NULL,
    HL7Version varchar(10) NULL,
    SourceSystem varchar(60) NULL,
    DestinationSystem varchar(60) NULL,
    EventReasonCode varchar(30) NULL,
    EventDateTime datetime2(0) NOT NULL,
    FromUnit varchar(20) NULL,
    FromRoom varchar(20) NULL,
    FromBed varchar(20) NULL,
    ToUnit varchar(20) NULL,
    ToRoom varchar(20) NULL,
    ToBed varchar(20) NULL,
    MessageControlID varchar(50) NOT NULL UNIQUE,
    ProcessingStatus varchar(20) NOT NULL
);

CREATE TABLE dbo.Diagnosis(
    DiagnosisID bigint IDENTITY PRIMARY KEY,
    EncounterID bigint NOT NULL REFERENCES dbo.Encounter(EncounterID),
    PatientID int NOT NULL REFERENCES dbo.Patient(PatientID),
    DiagnosisCode varchar(20) NOT NULL,
    CodeSystem varchar(30) NULL,
    DiagnosisDescription nvarchar(200) NOT NULL,
    DiagnosisCategory nvarchar(100) NOT NULL,
    DiagnosisType varchar(30) NOT NULL,
    DiagnosisRank int NULL,
    PresentOnAdmission bit NOT NULL,
    DiagnosisDate date NOT NULL,
    ActiveFlag bit NOT NULL
);

CREATE TABLE dbo.Allergy(
    AllergyID bigint IDENTITY PRIMARY KEY,
    PatientID int NOT NULL REFERENCES dbo.Patient(PatientID),
    AllergenCode varchar(30) NULL,
    CodeSystem varchar(30) NULL,
    Allergen nvarchar(120) NOT NULL,
    AllergyType varchar(40) NOT NULL,
    Reaction nvarchar(150) NOT NULL,
    Severity varchar(20) NOT NULL,
    VerificationStatus varchar(30) NULL,
    Status varchar(20) NOT NULL,
    OnsetDate date NULL
);

CREATE TABLE dbo.MedicationOrder(
    MedicationOrderID bigint IDENTITY PRIMARY KEY,
    EncounterID bigint NOT NULL REFERENCES dbo.Encounter(EncounterID),
    PatientID int NOT NULL REFERENCES dbo.Patient(PatientID),
    OrderNumber varchar(30) NOT NULL UNIQUE,
    MedicationCode varchar(30) NOT NULL,
    MedicationCodeSystem varchar(40) NULL,
    MedicationName nvarchar(150) NOT NULL,
    Dose decimal(10,2) NOT NULL,
    DoseUnit varchar(20) NOT NULL,
    Route varchar(30) NOT NULL,
    Frequency varchar(30) NOT NULL,
    PRNFlag bit NULL,
    OrderStatus varchar(20) NOT NULL,
    OrderDateTime datetime2(0) NOT NULL,
    StartDateTime datetime2(0) NULL,
    EndDateTime datetime2(0) NULL,
    OrderingProviderID int NOT NULL REFERENCES dbo.Provider(ProviderID)
);

CREATE TABLE dbo.LabOrder(
    LabOrderID bigint IDENTITY PRIMARY KEY,
    EncounterID bigint NOT NULL REFERENCES dbo.Encounter(EncounterID),
    PatientID int NOT NULL REFERENCES dbo.Patient(PatientID),
    AccessionNumber varchar(30) NOT NULL UNIQUE,
    PlacerOrderNumber varchar(40) NULL,
    FillerOrderNumber varchar(40) NULL,
    OrderCode varchar(20) NOT NULL,
    OrderName nvarchar(120) NOT NULL,
    SpecimenType varchar(50) NULL,
    Priority varchar(20) NOT NULL,
    OrderStatus varchar(20) NOT NULL,
    OrderDateTime datetime2(0) NOT NULL,
    CollectedDateTime datetime2(0) NULL,
    ResultedDateTime datetime2(0) NULL,
    OrderingProviderID int NOT NULL REFERENCES dbo.Provider(ProviderID)
);

CREATE TABLE dbo.LabResult(
    LabResultID bigint IDENTITY PRIMARY KEY,
    LabOrderID bigint NOT NULL REFERENCES dbo.LabOrder(LabOrderID),
    TestCode varchar(20) NOT NULL,
    LOINCCode varchar(20) NULL,
    TestName nvarchar(120) NOT NULL,
    ValueType varchar(10) NULL,
    ResultValue varchar(50) NOT NULL,
    ResultUnit varchar(30) NULL,
    ReferenceRange varchar(50) NULL,
    AbnormalFlag varchar(10) NULL,
    ResultStatus varchar(20) NOT NULL,
    ObservationDateTime datetime2(0) NOT NULL
);

CREATE TABLE dbo.InterfaceMessage(
    InterfaceMessageID bigint IDENTITY PRIMARY KEY,
    PatientID int NULL REFERENCES dbo.Patient(PatientID),
    EncounterID bigint NULL REFERENCES dbo.Encounter(EncounterID),
    ChannelName varchar(120) NOT NULL,
    MessageType varchar(20) NOT NULL,
    TriggerEvent varchar(10) NULL,
    HL7Version varchar(10) NULL,
    MessageControlID varchar(50) NOT NULL UNIQUE,
    SourceSystem varchar(60) NOT NULL,
    DestinationSystem varchar(60) NOT NULL,
    Direction varchar(20) NULL,
    TransportProtocol varchar(20) NULL,
    ReceivedDateTime datetime2(0) NOT NULL,
    ProcessedDateTime datetime2(0) NULL,
    ProcessingTimeMs int NULL,
    ProcessingStatus varchar(20) NOT NULL,
    AckStatus varchar(20) NULL,
    RetryCount int NOT NULL DEFAULT 0,
    PayloadFormat varchar(20) NOT NULL
);

CREATE TABLE dbo.InterfaceError(
    InterfaceErrorID bigint IDENTITY PRIMARY KEY,
    InterfaceMessageID bigint NOT NULL REFERENCES dbo.InterfaceMessage(InterfaceMessageID),
    ErrorDateTime datetime2(0) NOT NULL,
    Severity varchar(20) NULL,
    ErrorCode varchar(40) NOT NULL,
    ErrorCategory varchar(60) NOT NULL,
    SegmentName varchar(10) NULL,
    FieldPosition varchar(20) NULL,
    ErrorMessage nvarchar(500) NOT NULL,
    ResolvedFlag bit NOT NULL,
    ResolutionNotes nvarchar(500) NULL
);

CREATE TABLE dbo.AuditLog(
    AuditLogID bigint IDENTITY PRIMARY KEY,
    EventDateTime datetime2(0) NOT NULL,
    UserOrSystem varchar(100) NOT NULL,
    ApplicationName varchar(100) NULL,
    WorkstationName varchar(100) NULL,
    ActionType varchar(30) NOT NULL,
    EntityType varchar(50) NOT NULL,
    EntityID varchar(50) NOT NULL,
    PatientID int NULL REFERENCES dbo.Patient(PatientID),
    Description nvarchar(500) NOT NULL,
    SourceIP varchar(45) NULL
);
GO

INSERT dbo.Facility(FacilityCode,FacilityName,City,ProvinceState,Country) VALUES
('MCTH',N'MirthCare University Hospital',N'Toronto',N'Ontario',N'Canada'),
('MCRC',N'MirthCare Rehabilitation Centre',N'Brampton',N'Ontario',N'Canada'),
('MCCL',N'MirthCare Community Clinic',N'Mississauga',N'Ontario',N'Canada');

INSERT dbo.Department(FacilityID,DepartmentCode,DepartmentName,ServiceLine) VALUES
(1,'ED',N'Emergency Department',N'Emergency'),(1,'CARD',N'Cardiology',N'Medicine'),
(1,'NEUR',N'Neurology',N'Medicine'),(1,'ONC',N'Oncology',N'Cancer Care'),
(1,'ORTH',N'Orthopedics',N'Surgery'),(1,'GENS',N'General Surgery',N'Surgery'),
(1,'ICU',N'Intensive Care Unit',N'Critical Care'),(1,'MED',N'General Internal Medicine',N'Medicine'),
(1,'OBS',N'Observation Unit',N'Emergency'),(1,'MH',N'Mental Health',N'Mental Health'),
(1,'PEDS',N'Pediatrics',N'Child Health'),(1,'OBGYN',N'Obstetrics and Gynecology',N'Women''s Health'),
(1,'RENAL',N'Nephrology',N'Medicine'),(1,'RESP',N'Respiratory Medicine',N'Medicine'),
(2,'REHAB',N'Inpatient Rehabilitation',N'Rehabilitation'),(2,'CCC',N'Complex Continuing Care',N'Continuing Care'),
(2,'PT',N'Physiotherapy',N'Rehabilitation'),(2,'OT',N'Occupational Therapy',N'Rehabilitation'),
(3,'FAM',N'Family Medicine Clinic',N'Primary Care'),(3,'DIAB',N'Diabetes Education Clinic',N'Ambulatory');
GO

;WITH n AS(
 SELECT TOP(125) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
 FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT dbo.Location(DepartmentID,UnitCode,RoomCode,BedCode,LocationDescription,LocationType)
SELECT 1+((n-1)%20),CONCAT('U',RIGHT('00'+CAST(1+((n-1)%20) AS varchar(2)),2)),
       CONCAT('R',100+((n-1)%80)),CONCAT('B',1+((n-1)%2)),
       CONCAT('Unit ',1+((n-1)%20),' Room ',100+((n-1)%80),' Bed ',1+((n-1)%2)),
       CHOOSE(1+((n-1)%5),'Inpatient','Outpatient','Emergency','Rehabilitation','Clinic')
FROM n;

;WITH n AS(
 SELECT TOP(80) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n FROM sys.all_objects
)
INSERT dbo.Provider(ProviderNumber,FirstName,LastName,Specialty,DepartmentID,Phone,Email,LicenseNumber)
SELECT CONCAT('PRV',RIGHT('00000'+CAST(n AS varchar(5)),5)),
       CHOOSE(1+((n-1)%20),N'Alex',N'Maya',N'Daniel',N'Sofia',N'Arjun',N'Layla',N'Noah',N'Emma',N'Omar',N'Anika',N'Lucas',N'Isabella',N'Ethan',N'Amina',N'Leo',N'Nora',N'Ryan',N'Zara',N'Adrian',N'Priya'),
       CHOOSE(1+((n*7-1)%20),N'Newton',N'Santos',N'Rahman',N'Chen',N'Patel',N'Martin',N'Okafor',N'Kim',N'Rossi',N'Singh',N'Garcia',N'Brown',N'Hassan',N'Kowalski',N'Nguyen',N'Wilson',N'Khan',N'Murphy',N'Silva',N'Ito'),
       CHOOSE(1+((n-1)%15),N'Emergency Medicine',N'Cardiology',N'Neurology',N'Oncology',N'Orthopedic Surgery',N'General Surgery',N'Critical Care',N'Internal Medicine',N'Psychiatry',N'Pediatrics',N'Obstetrics',N'Nephrology',N'Respiratory Medicine',N'Rehabilitation Medicine',N'Family Medicine'),
       1+((n-1)%20),CONCAT('+1-416-555-',RIGHT('0000'+CAST(1000+n AS varchar(4)),4)),
       CONCAT('provider',n,'@mirthcare.example'),CONCAT('CPSO-SYN-',100000+n)
FROM n;
GO

;WITH n AS(
 SELECT TOP(1000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
 FROM sys.all_objects a CROSS JOIN sys.all_objects b
), p0 AS(
 SELECT n,1930+((n-1)%97) AS BirthYear
 FROM n
), p AS(
 SELECT n,
        DATEFROMPARTS(
            BirthYear,
            CASE WHEN BirthYear=2026 THEN 1+((n*7)%7) ELSE 1+((n*7)%12) END,
            1+((n*13)%28)
        ) AS DOB
 FROM p0
)
INSERT dbo.Patient(
 EnterpriseMRN,FacilityMRN,LegacyMRN,HealthCardNumber,HealthCardVersion,HealthCardExpiry,NationalIDToken,
 FirstName,MiddleName,LastName,PreferredName,DateOfBirth,AgeGroup,SexAtBirth,GenderIdentity,Pronouns,
 MaritalStatus,BloodType,RhFactor,PrimaryLanguage,SecondaryLanguage,CountryOfBirth,Citizenship,
 AddressLine1,AddressLine2,City,ProvinceState,PostalCode,Country,HomePhone,MobilePhone,EmailAddress,
 PreferredContactMethod,EmergencyContactName,EmergencyContactRelationship,EmergencyContactPhone,
 PrimaryCareProviderID,OrganDonorFlag,SmokingStatus,AlcoholUse,HeightCm,WeightKg,BMI,PregnancyStatus,
 DisabilityStatus,MobilityStatus,CommunicationNeeds,InterpreterRequired,PrivacyLevel,ConsentStatus,
 PatientStatus,DeceasedFlag,DeathDateTime,RegistrationSource,CreatedAt,UpdatedAt,LastSeenAt)
SELECT
 CONCAT('MCU',RIGHT('0000000'+CAST(n AS varchar(7)),7)),
 CONCAT(CHOOSE(1+((n-1)%3),'MCTH','MCRC','MCCL'),RIGHT('000000'+CAST(200000+n AS varchar(6)),6)),
 CASE WHEN n%5=0 THEN CONCAT('LEG',500000+n) END,
 CASE WHEN n%11=0 THEN NULL ELSE CONCAT('SYN',7000000000+n) END,
 CASE WHEN n%11=0 THEN NULL ELSE CHAR(65+n%26)+CHAR(65+(n*3)%26) END,
 CASE WHEN n%11=0 THEN NULL ELSE DATEFROMPARTS(2027+n%5,12,31) END,
 CONVERT(varchar(64),HASHBYTES('SHA2_256',CONCAT('MIRTHCARE-',n)),2),
 CHOOSE(1+((n-1)%40),N'Aarav',N'Mateo',N'Leona',N'Amara',N'Kai',N'Zoya',N'Noel',N'Mira',N'Diego',N'Anaya',N'Rafael',N'Ada',N'Nikhil',N'Selene',N'Karim',N'Elena',N'Theo',N'Aisha',N'Rian',N'Mei',N'Kabir',N'Lina',N'Samir',N'Nadia',N'Marco',N'Ivy',N'Arlo',N'Sana',N'Jonas',N'Kiara',N'Emil',N'Rhea',N'Yusuf',N'Clara',N'Andre',N'Noor',N'Dev',N'Alina',N'Milan',N'Tara'),
 CASE WHEN n%3=0 THEN CHOOSE(1+((n-1)%10),N'James',N'Rose',N'Kumar',N'Marie',N'Lee',N'Anne',N'Ray',N'Jade',N'Luis',N'Grace') END,
 CHOOSE(1+((n*7-1)%40),N'Santos',N'Rahman',N'Chen',N'Patel',N'Martin',N'Okafor',N'Kim',N'Rossi',N'Singh',N'Garcia',N'Brown',N'Hassan',N'Kowalski',N'Nguyen',N'Wilson',N'Khan',N'Murphy',N'Silva',N'Ito',N'Das',N'Bennett',N'Costa',N'Malik',N'Park',N'Shah',N'Lopez',N'Young',N'Nakamura',N'Roy',N'Ahmed',N'Fernandez',N'Bose',N'Campbell',N'Jensen',N'Sato',N'Ibrahim',N'Taylor',N'Mehta',N'Clark',N'Pereira'),
 CASE WHEN n%4=0 THEN CHOOSE(1+((n-1)%10),N'Champ',N'Nova',N'Sunny',N'Rocket',N'Jazz',N'Max',N'Sky',N'Ace',N'Rio',N'Joy') END,
 DOB,
 CASE WHEN DATEDIFF(year,DOB,'2026-08-01')<1 THEN 'Infant' WHEN DATEDIFF(year,DOB,'2026-08-01')<13 THEN 'Child' WHEN DATEDIFF(year,DOB,'2026-08-01')<18 THEN 'Adolescent' WHEN DATEDIFF(year,DOB,'2026-08-01')<65 THEN 'Adult' ELSE 'Older Adult' END,
 CHOOSE(1+((n-1)%3),'Male','Female','Intersex'),
 CHOOSE(1+((n-1)%6),'Man','Woman','Non-binary','Transgender Man','Transgender Woman','Prefer not to say'),
 CHOOSE(1+((n-1)%6),'he/him','she/her','they/them','he/him','she/her','not specified'),
 CHOOSE(1+((n-1)%5),'Single','Married','Divorced','Widowed','Common-law'),
 CHOOSE(1+((n-1)%8),'A','A','B','B','AB','AB','O','O'),CASE WHEN n%2=0 THEN '+' ELSE '-' END,
 CHOOSE(1+((n-1)%12),N'English',N'French',N'Hindi',N'Telugu',N'Spanish',N'Arabic',N'Mandarin',N'Punjabi',N'Portuguese',N'Korean',N'Italian',N'Tamil'),
 CASE WHEN n%4=0 THEN CHOOSE(1+((n-1)%8),N'English',N'French',N'Hindi',N'Spanish',N'Arabic',N'Mandarin',N'Punjabi',N'Portuguese') END,
 CHOOSE(1+((n-1)%20),N'Canada',N'India',N'Brazil',N'Portugal',N'Argentina',N'France',N'Nigeria',N'China',N'Japan',N'South Korea',N'Egypt',N'Pakistan',N'Italy',N'Germany',N'Mexico',N'Philippines',N'United Kingdom',N'Australia',N'Kenya',N'United States'),
 CHOOSE(1+((n*3-1)%20),N'Canadian',N'Indian',N'Brazilian',N'Portuguese',N'Argentinian',N'French',N'Nigerian',N'Chinese',N'Japanese',N'South Korean',N'Egyptian',N'Pakistani',N'Italian',N'German',N'Mexican',N'Filipino',N'British',N'Australian',N'Kenyan',N'American'),
 CONCAT(100+n%8900,N' Training Avenue'),CASE WHEN n%5=0 THEN CONCAT(N'Unit ',1+n%30) END,
 CHOOSE(1+((n-1)%15),N'Toronto',N'Brampton',N'Mississauga',N'Ottawa',N'Vancouver',N'Calgary',N'Montreal',N'Hyderabad',N'Lisbon',N'Rio de Janeiro',N'Paris',N'Lagos',N'Tokyo',N'Manila',N'Mexico City'),
 CHOOSE(1+((n-1)%12),N'Ontario',N'Ontario',N'Ontario',N'Quebec',N'British Columbia',N'Alberta',N'Telangana',N'Lisbon',N'Rio de Janeiro',N'Île-de-France',N'Lagos',N'Tokyo'),
 CONCAT(CHAR(65+n%26),n%10,CHAR(65+(n*2)%26),' ',(n+3)%10,CHAR(65+(n*5)%26),(n+7)%10),
 CHOOSE(1+((n-1)%20),N'Canada',N'Canada',N'Canada',N'Canada',N'Canada',N'Canada',N'India',N'Portugal',N'Brazil',N'France',N'Nigeria',N'Japan',N'Philippines',N'Mexico',N'United States',N'China',N'Italy',N'Germany',N'Australia',N'Pakistan'),
 CASE WHEN n%4<>0 THEN CONCAT('+1-416-555-',RIGHT('0000'+CAST(1000+n%8999 AS varchar(4)),4)) END,
 CONCAT('+1-647-555-',RIGHT('0000'+CAST(1000+(n*7)%8999 AS varchar(4)),4)),CONCAT('patient',n,'@training.mirthcare.example'),
 CHOOSE(1+((n-1)%4),'Mobile','Email','Home Phone','Patient Portal'),CONCAT(N'Emergency Contact ',n),
 CHOOSE(1+((n-1)%6),'Spouse','Parent','Sibling','Child','Friend','Guardian'),
 CONCAT('+1-905-555-',RIGHT('0000'+CAST(1000+(n*11)%8999 AS varchar(4)),4)),1+((n-1)%80),
 CASE WHEN n%3=0 THEN 1 ELSE 0 END,CHOOSE(1+((n-1)%5),'Never','Former','Current','Unknown','Passive exposure'),
 CHOOSE(1+((n-1)%5),'None','Occasional','Moderate','Frequent','Unknown'),
 CAST(145+(n*13)%55 AS decimal(5,2)),CAST(45+(n*17)%80 AS decimal(6,2)),
 CAST((45+(n*17)%80)/POWER((145+(n*13)%55)/100.0,2) AS decimal(5,2)),
 CASE WHEN ((n-1)%3)=1 AND DATEDIFF(year,DOB,'2026-08-01') BETWEEN 15 AND 50 THEN CHOOSE(1+((n-1)%4),'Not pregnant','Pregnant','Unknown','Not documented') ELSE 'Not applicable' END,
 CHOOSE(1+((n-1)%6),'None','Visual impairment','Hearing impairment','Cognitive impairment','Physical disability','Multiple'),
 CHOOSE(1+((n-1)%6),'Independent','Cane','Walker','Wheelchair','Two-person assist','Bedbound'),
 CASE WHEN n%7=0 THEN CHOOSE(1+((n-1)%5),'Large print','Hearing assistance','Communication board','Interpreter','Caregiver support') END,
 CASE WHEN n%6=0 THEN 1 ELSE 0 END,CHOOSE(1+((n-1)%4),'Standard','Restricted','VIP','Research'),
 CHOOSE(1+((n-1)%4),'Full consent','Limited consent','Pending','Declined optional sharing'),
 CASE WHEN n%37=0 THEN 'Deceased' ELSE 'Active' END,CASE WHEN n%37=0 THEN 1 ELSE 0 END,
 CASE WHEN n%37=0 THEN DATEADD(day,-(n%300),CAST('2026-07-31' AS datetime2)) END,
 CHOOSE(1+((n-1)%6),'Emergency','Registration Desk','Clinic','Referral','Transfer','Patient Portal'),
 DATEADD(day,-(365+n%1200),CAST('2026-08-01' AS datetime2)),DATEADD(day,-n%90,CAST('2026-08-01' AS datetime2)),
 CASE WHEN n%37=0 THEN NULL ELSE DATEADD(day,-n%180,CAST('2026-08-01' AS datetime2)) END
FROM p;
GO

/*
===============================================================================
UNIQUE PATIENT NAME + TEMPORAL DISTRIBUTION PASS
- Exactly 1,000 patients
- Every FirstName is unique (no FirstName repeats)
- Every LastName is also unique for cleaner beginner demonstrations
- Birth years span 1930 through 2026
- Patient registration timestamps span 1995 through 2026 where age permits
- Names are synthetic training combinations; no medical facts represent real people
===============================================================================
*/
;WITH NameMap(Seq,FirstName,LastName) AS (
    SELECT * FROM (VALUES
        (1,N'Cristiano',N'Cain'),
        (2,N'Lionel',N'Marshall'),
        (3,N'Neymar',N'Forbes'),
        (4,N'Kylian',N'Bender'),
        (5,N'Erling',N'Guha'),
        (6,N'Mohamed',N'Normand'),
        (7,N'Luka',N'Gierschner'),
        (8,N'Kevin',N'Salcedo'),
        (9,N'Virat',N'Molina'),
        (10,N'Sachin',N'Rios'),
        (11,N'Rohit',N'Oliveira'),
        (12,N'Mahendra',N'Speri'),
        (13,N'Hardik',N'Dachgelt'),
        (14,N'Jasprit',N'Lundin'),
        (15,N'Shubman',N'Dahl'),
        (16,N'Serena',N'Andreasen'),
        (17,N'Roger',N'Laukkanen'),
        (18,N'Novak',N'Gwara'),
        (19,N'Rafael',N'Doležalová'),
        (20,N'LeBron',N'Mikulík'),
        (21,N'Michael',N'Dinu'),
        (22,N'Stephen',N'Deák'),
        (23,N'ShahRukh',N'Güçlü'),
        (24,N'Amitabh',N'Красильников'),
        (25,N'Priyanka',N'Лемешко'),
        (26,N'Deepika',N'中村'),
        (27,N'Leonardo',N'최'),
        (28,N'Denzel',N'商'),
        (29,N'Meryl',N'المؤقت'),
        (30,N'Tom',N'אלוש'),
        (31,N'Keanu',N'Thamrin'),
        (32,N'Zendaya',N'ดุริยพันธุ์'),
        (33,N'Barack',N'Garcia'),
        (34,N'Narendra',N'Davies'),
        (35,N'Justin',N'Norman'),
        (36,N'Emmanuel',N'Mitchell'),
        (37,N'Keir',N'Munshi'),
        (38,N'Volodymyr',N'Navarro'),
        (39,N'Claudia',N'Hartung'),
        (40,N'Giorgia',N'Noguera'),
        (41,N'Becky',N'Arreola'),
        (42,N'Fiona',N'Nunes'),
        (43,N'Amy',N'Martins'),
        (44,N'Joel',N'Manunta'),
        (45,N'Upasna',N'van Vliet'),
        (46,N'Guillaume',N'Hellström'),
        (47,N'Ingried',N'Karlsen'),
        (48,N'Valerio',N'Lauritsen'),
        (49,N'Ofelia',N'Mikkonen'),
        (50,N'Natália',N'Duras'),
        (51,N'Manuel',N'Kolář'),
        (52,N'Paride',N'Blahová'),
        (53,N'Jelte',N'Ardelean'),
        (54,N'Christer',N'Németh'),
        (55,N'Elise',N'Силин'),
        (56,N'Dennis',N'Матяш'),
        (57,N'Elli',N'岡田'),
        (58,N'Ryszard',N'이'),
        (59,N'Ctibor',N'胡'),
        (60,N'Vincent',N'تغلب بن وائل'),
        (61,N'Maia',N'נחמיאס'),
        (62,N'Richárd',N'Yuliarti'),
        (63,N'Arıkol',N'งามพิเชษฐ์'),
        (64,N'Галактион',N'Hampton'),
        (65,N'Федір',N'Yates'),
        (66,N'淳',N'Alvarez'),
        (67,N'종수',N'Morgan'),
        (68,N'淑华',N'Sekhon'),
        (69,N'لتين',N'Hervé'),
        (70,N'שירה',N'Wernecke'),
        (71,N'Wadi',N'Márquez'),
        (72,N'ภานิณี',N'Sanches'),
        (73,N'Brandon',N'Cirino'),
        (74,N'Mandy',N'Figueiredo'),
        (75,N'Randy',N'Fogazzaro'),
        (76,N'Todd',N'van Stralen'),
        (77,N'Xiti',N'Söderlund'),
        (78,N'Justina',N'Berntsen'),
        (79,N'Susana',N'Johnsen'),
        (80,N'Clemente',N'Haavisto'),
        (81,N'Ravi Lucca',N'Naglik'),
        (82,N'Eduardo',N'Pavlíková'),
        (83,N'Dino',N'Masaríková'),
        (84,N'Kristina',N'Florea'),
        (85,N'Gro',N'Tóth'),
        (86,N'Yda',N'Akça'),
        (87,N'Ilmari',N'Костин'),
        (88,N'Bartek',N'Гузь'),
        (89,N'Lýdie',N'藤田'),
        (90,N'Vasil',N'박'),
        (91,N'Melania',N'刘'),
        (92,N'Noémi',N'بنو عدي'),
        (93,N'Yekda',N'דאוד'),
        (94,N'Панас',N'Haryanto'),
        (95,N'明美',N'สังข์กรด'),
        (96,N'영진',N'Smith'),
        (97,N'旭',N'Harris'),
        (98,N'رشدي',N'Cooper'),
        (99,N'תהילה',N'Malhotra'),
        (100,N'Cengkal',N'Leclercq'),
        (101,N'ดวงสมร',N'Kaul'),
        (102,N'Christine',N'Tejedor'),
        (103,N'Clifford',N'Bonilla'),
        (104,N'Tyler',N'Vasconcelos'),
        (105,N'Jose',N'Jesus'),
        (106,N'Qadim',N'Venier'),
        (107,N'Monique',N'van Dillen'),
        (108,N'Berndt',N'Johansson'),
        (109,N'Cesar',N'Danielsen'),
        (110,N'Norma',N'Holm'),
        (111,N'Asafe',N'Järvinen'),
        (112,N'Gabriel',N'Andrys'),
        (113,N'Amedeo',N'Tůmová'),
        (114,N'Indy',N'Ondráš'),
        (115,N'Johan',N'Stan'),
        (116,N'Ulrike',N'Vass'),
        (117,N'Marketta',N'Durmuş'),
        (118,N'Miłosz',N'Петрова'),
        (119,N'Vasyl',N'Тригуб'),
        (120,N'Dobroslav',N'吉田'),
        (121,N'Elena',N'송'),
        (122,N'Mária',N'周'),
        (123,N'Yazgül',N'بارق'),
        (124,N'Ираида',N'ח''טיב'),
        (125,N'Святослав',N'Pertiwi'),
        (126,N'涼平',N'ถนอมพลกรัง'),
        (127,N'도현',N'Clarke'),
        (128,N'娟',N'John'),
        (129,N'مهران',N'Snow'),
        (130,N'אחמד',N'Glass'),
        (131,N'Yoga',N'Sabharwal'),
        (132,N'ทินพร',N'Prévost'),
        (133,N'Leslie',N'Etzler'),
        (134,N'Reece',N'Querol'),
        (135,N'Sherri',N'Vigil'),
        (136,N'Sarah',N'Camargo'),
        (137,N'Reyansh',N'Santos'),
        (138,N'Alix',N'Lovato'),
        (139,N'Wolf',N'Broekhoven'),
        (140,N'Eutropio',N'Wiklund'),
        (141,N'Carmen',N'Kristiansen'),
        (142,N'Cauã',N'Christiansen'),
        (143,N'Vitória',N'Mattila'),
        (144,N'Ignazio',N'Dawidczyk'),
        (145,N'Mart',N'Vítek'),
        (146,N'Wilhelm',N'Palková'),
        (147,N'Emil',N'Preda'),
        (148,N'Jean',N'Varga'),
        (149,N'Esa',N'Şener'),
        (150,N'Nataniel',N'Казакова'),
        (151,N'Denis',N'Демиденко'),
        (152,N'Ľudomila',N'渡辺'),
        (153,N'Roxelana',N'성'),
        (154,N'Bence',N'韦'),
        (155,N'Kibar',N'المتولي'),
        (156,N'Георгий',N'אוזן'),
        (157,N'Святослава',N'Setiawan'),
        (158,N'春香',N'ทองเนื้อดี'),
        (159,N'예진',N'Hunt'),
        (160,N'畅',N'Warner'),
        (161,N'مرتقي',N'Howard'),
        (162,N'אלכס',N'Jones'),
        (163,N'Rafi',N'Soman'),
        (164,N'ศดิศ',N'Le Gall'),
        (165,N'Beverley',N'Ebert'),
        (166,N'Danielle',N'Iñiguez'),
        (167,N'Stéphanie',N'Elizondo'),
        (168,N'Sergej',N'Câmara'),
        (169,N'Marianela',N'Ferreira'),
        (170,N'Catalina',N'Broggini'),
        (171,N'Aurora',N'Lommert'),
        (172,N'Luchino',N'Andersson'),
        (173,N'Kyano',N'Pettersen'),
        (174,N'Abdul',N'Kjær'),
        (175,N'Benedicte',N'Liukkonen'),
        (176,N'Kristian',N'Karmelita'),
        (177,N'Andrzej',N'Benešová'),
        (178,N'Ctirad',N'Hanuska'),
        (179,N'Gustáv',N'Barbu'),
        (180,N'Mirabela',N'Fehér'),
        (181,N'Rozália',N'Çorlu'),
        (182,N'Rahiye',N'Григорьева'),
        (183,N'Пелагея',N'Полтавець'),
        (184,N'Валентин',N'藤原'),
        (185,N'桃子',N'권'),
        (186,N'伟',N'郭'),
        (187,N'كاظم',N'جبيلي'),
        (188,N'נועם',N'כץ'),
        (189,N'Mujur',N'Saputra'),
        (190,N'อชิตะวีร์',N'กุมารบุญ'),
        (191,N'Tina',N'Daniels'),
        (192,N'Frances',N'Watson'),
        (193,N'Jermaine',N'Acosta'),
        (194,N'Dan',N'Gross'),
        (195,N'Ekalinga',N'Prakash'),
        (196,N'Marthe',N'Meunier'),
        (197,N'Willibald',N'Scheuermann'),
        (198,N'Seve',N'Abril'),
        (199,N'Marcos',N'Laureano'),
        (200,N'Anthony',N'Vargas'),
        (201,N'Kévim',N'Costa'),
        (202,N'Giulio',N'Ceci'),
        (203,N'Joey',N'Briere'),
        (204,N'Daniel',N'Simonsen'),
        (205,N'Gunn',N'Friis'),
        (206,N'Jan',N'Pennanen'),
        (207,N'Aapo',N'Golan'),
        (208,N'Karol',N'Svoboda'),
        (209,N'Dalibor',N'Obšut'),
        (210,N'Libor',N'Dima'),
        (211,N'Cazimir',N'Orosz'),
        (212,N'Levente',N'Akçay'),
        (213,N'Alaz',N'Воронов'),
        (214,N'Викентий',N'Девдюк'),
        (215,N'Єлисей',N'佐藤'),
        (216,N'知実',N'정'),
        (217,N'성호',N'秦'),
        (218,N'玉华',N'طوطح'),
        (219,N'ماجد',N'נבון'),
        (220,N'ריתאל',N'Januar'),
        (221,N'Aswani',N'ธูปหอม'),
        (222,N'นวรรษนันท์',N'Ellis'),
        (223,N'Diane',N'Fraser'),
        (224,N'Carla',N'Terrell'),
        (225,N'Cassandra',N'Sood'),
        (226,N'Chameli',N'Arnaud'),
        (227,N'Nathalie',N'Killer'),
        (228,N'Coral',N'Gras'),
        (229,N'Lorenzo',N'Farías'),
        (230,N'Aylla',N'Sá'),
        (231,N'Márcio',N'Guerreiro'),
        (232,N'Arnaldo',N'Surian'),
        (233,N'Aylin',N'le Guellec'),
        (234,N'Johanna',N'Engen'),
        (235,N'Anna',N'Schmidt'),
        (236,N'Mia',N'Hytönen'),
        (237,N'Maaria',N'Kozubal'),
        (238,N'Nicole',N'Ševčík'),
        (239,N'Bohumír',N'Gavorník'),
        (240,N'Marek',N'Ababei'),
        (241,N'Sinică',N'Balogh'),
        (242,N'Erzsébet',N'Моисеев'),
        (243,N'Divan',N'Пушкар'),
        (244,N'Валерій',N'青木'),
        (245,N'直人',N'류'),
        (246,N'영수',N'黄'),
        (247,N'强',N'عدوان'),
        (248,N'فهمي',N'סופר'),
        (249,N'שוהם',N'Nasyidah'),
        (250,N'Kenes',N'Stokes'),
        (251,N'กุลปรียา',N'Middleton'),
        (252,N'Jennifer',N'Cohen'),
        (253,N'Lucy',N'Shukla'),
        (254,N'Robin',N'Duval'),
        (255,N'Kelly',N'Löffler'),
        (256,N'Veer',N'Rosell'),
        (257,N'Alex',N'Orosco'),
        (258,N'Vanessa',N'Rocha'),
        (259,N'Nayara',N'Fermi'),
        (260,N'Paola',N'Stichter'),
        (261,N'Teresa',N'Olsson'),
        (262,N'Damiano',N'Gundersen'),
        (263,N'Mason',N'Thorsen'),
        (264,N'Thea',N'Raatikainen'),
        (265,N'Ensio',N'Śmieja'),
        (266,N'Tobiasz',N'Murínová'),
        (267,N'Romana',N'Dumitrescu'),
        (268,N'Etela',N'Szőke'),
        (269,N'Luiza',N'Васильева'),
        (270,N'Uyanser',N'Гайдабура'),
        (271,N'Мина',N'山下'),
        (272,N'Ярослава',N'郑'),
        (273,N'陽一',N'ارناؤوط'),
        (274,N'승현',N'כהן'),
        (275,N'彬',N'Wulandari'),
        (276,N'فاضل',N'พงศ์ฉบับนภา'),
        (277,N'אלמה',N'Hill'),
        (278,N'Zulaikha',N'Hussain'),
        (279,N'อับดุลเลาะห์',N'Wright'),
        (280,N'George',N'Mandal'),
        (281,N'Beth',N'Bertin'),
        (282,N'Shannon',N'Tschentscher'),
        (283,N'Nathan',N'Mora'),
        (284,N'Brinda',N'Ojeda'),
        (285,N'Robby',N'Fioravanti'),
        (286,N'Gregorio',N'Geneart'),
        (287,N'Silvano',N'Lindström'),
        (288,N'Ana Laura',N'Ahmed'),
        (289,N'Bruno',N'Mogensen'),
        (290,N'Graziano',N'Pesonen'),
        (291,N'Tirza',N'Roczniak'),
        (292,N'Lene',N'Marešová'),
        (293,N'Johnny',N'Tomečka'),
        (294,N'Eliisa',N'Szabó'),
        (295,N'Jędrzej',N'Karadeniz'),
        (296,N'Karel',N'Терентьева'),
        (297,N'Erika',N'Пелех'),
        (298,N'Profira',N'김'),
        (299,N'Péter',N'邓'),
        (300,N'Verim',N'البديري'),
        (301,N'Варвара',N'וייס'),
        (302,N'Георгій',N'Agustina'),
        (303,N'和也',N'นาควงษ์'),
        (304,N'채원',N'Thomas'),
        (305,N'健',N'Goodwin'),
        (306,N'جوانا',N'Davidson'),
        (307,N'איתן',N'Jenkins'),
        (308,N'Jumadi',N'Chanda'),
        (309,N'มนรัตน์',N'Tessier'),
        (310,N'Charlie',N'Bärer'),
        (311,N'David',N'Escobar'),
        (312,N'Betty',N'Venegas'),
        (313,N'Murat',N'Henriques'),
        (314,N'Salvador',N'Montessori'),
        (315,N'Reina',N'van Duvenvoirde'),
        (316,N'Salomé',N'Sjögren'),
        (317,N'Gabriele',N'Jenssen'),
        (318,N'Dion',N'Lund'),
        (319,N'William',N'Karhu'),
        (320,N'Lise',N'Szczot'),
        (321,N'Harri',N'Bártová'),
        (322,N'Janina',N'Rosa'),
        (323,N'Hubert',N'Nemeș'),
        (324,N'Irena',N'Horváth'),
        (325,N'Rita',N'Sakarya'),
        (326,N'Sittik',N'Евдокимов'),
        (327,N'Аггей',N'Юхименко'),
        (328,N'Любов',N'佐々木'),
        (329,N'真綾',N'曾'),
        (330,N'상호',N'قليبو'),
        (331,N'凤英',N'גדיר'),
        (332,N'بثينة',N'Hutapea'),
        (333,N'איילה',N'หอมสิน'),
        (334,N'พาดีล๊ะ',N'Kelly'),
        (335,N'Iain',N'Kane'),
        (336,N'Kyle',N'Barad'),
        (337,N'Elizabeth',N'Grégoire'),
        (338,N'Qasim',N'Stolze'),
        (339,N'Johann',N'Núñez'),
        (340,N'Jerónimo',N'Ibarra'),
        (341,N'Lara',N'Gomes'),
        (342,N'Fortunata',N'Loureiro'),
        (343,N'Linn',N'Siffredi'),
        (344,N'Marie',N'Schouten'),
        (345,N'Kristine',N'Petersson'),
        (346,N'Stephan',N'Birkeland'),
        (347,N'Adrian',N'Nieminen'),
        (348,N'Světlana',N'Krzysiek'),
        (349,N'Ladislav',N'Beránek'),
        (350,N'Agata',N'Sedliak'),
        (351,N'János',N'Tudor'),
        (352,N'Monis',N'Fazekas'),
        (353,N'Севастьян',N'Sezer'),
        (354,N'Миколай',N'Родионова'),
        (355,N'充',N'Василевич'),
        (356,N'건우',N'高橋'),
        (357,N'丽娟',N'屈'),
        (358,N'مهدي',N'بنو ذي أصبح'),
        (359,N'Eka',N'פרץ'),
        (360,N'ญาณพันธุ์',N'Firmansyah'),
        (361,N'James',N'บุตดา'),
        (362,N'Christopher',N'Hale'),
        (363,N'Linda',N'O''Connor'),
        (364,N'Steven',N'Thompson'),
        (365,N'Charles',N'Meadows'),
        (366,N'Claudine',N'Chacko'),
        (367,N'Leonore',N'Diaz'),
        (368,N'Amando',N'Fiebig'),
        (369,N'José Emilio',N'Llamas'),
        (370,N'Ana Cecília',N'Ramón'),
        (371,N'Marco',N'Andrade'),
        (372,N'Ettore',N'Miranda'),
        (373,N'Merle',N'Borromeo'),
        (374,N'Leif',N'Weijters'),
        (375,N'Eivind',N'Ekberg'),
        (376,N'Øyvind',N'Eriksen'),
        (377,N'Hannu',N'Ryynänen'),
        (378,N'Kamila',N'Korzeniak'),
        (379,N'Ester',N'Macháčková'),
        (380,N'Kazimír',N'Kyseľa'),
        (381,N'Mihnea',N'Aanei'),
        (382,N'Jolán',N'Lengyel'),
        (383,N'Zebirce',N'Zengin'),
        (384,N'Иосиф',N'Александрова'),
        (385,N'Лесь',N'Комар'),
        (386,N'영식',N'林'),
        (387,N'燕',N'陈'),
        (388,N'رمحي',N'الدجاني'),
        (389,N'מחמוד',N'פולק'),
        (390,N'Kasiyah',N'นิษประปัญจ์'),
        (391,N'ธารา',N'Clark'),
        (392,N'Karen',N'Nixon'),
        (393,N'Joseph',N'Phillips'),
        (394,N'Darren',N'Boyer'),
        (395,N'Vasatika',N'Bawa'),
        (396,N'Pénélope',N'Dumas'),
        (397,N'Mirja',N'Kramer'),
        (398,N'Baudelio',N'Serrano'),
        (399,N'Irma',N'Corrales'),
        (400,N'Joaquim',N'Faria'),
        (401,N'Tatiana',N'Rusticucci'),
        (402,N'Lisa',N'Godfrey van Alemannië'),
        (403,N'Eva',N'Bergström'),
        (404,N'Laus',N'Jacobsen'),
        (405,N'Sebastian',N'Bertelsen'),
        (406,N'Vilém',N'Kuusela'),
        (407,N'Gertrúda',N'Żero'),
        (408,N'Sandu',N'Malý'),
        (409,N'Róbert',N'Líšková'),
        (410,N'Fidaye',N'Pop'),
        (411,N'Софон',N'Molnár'),
        (412,N'Климент',N'Bilir'),
        (413,N'현우',N'Кулакова'),
        (414,N'柳',N'Рубан'),
        (415,N'زهرة',N'孙'),
        (416,N'ח''אלד',N'القرجولي'),
        (417,N'Bakiman',N'פרידמן'),
        (418,N'Sandra',N'Mahendra'),
        (419,N'Ashley',N'ศรีอุ่น'),
        (420,N'Brad',N'Cardenas'),
        (421,N'Pallavi',N'Chavez'),
        (422,N'Jacqueline',N'Warren'),
        (423,N'Reza',N'Samra'),
        (424,N'Aarón',N'Marin'),
        (425,N'Tania',N'Rogge'),
        (426,N'Vicente',N'Almansa'),
        (427,N'Inês',N'Marrero'),
        (428,N'Nicoletta',N'Pires'),
        (429,N'Owen',N'Caironi'),
        (430,N'Marianne',N'Scharroo'),
        (431,N'Jon',N'Eklund'),
        (432,N'Pekka',N'Berg'),
        (433,N'Nela',N'Johansen'),
        (434,N'Květoslava',N'Lindroos'),
        (435,N'Margita',N'Gomuła'),
        (436,N'Grațian',N'Žák'),
        (437,N'Krisztina',N'Zubka'),
        (438,N'Aytuna',N'Dochioiu'),
        (439,N'Владлен',N'Juhász'),
        (440,N'Устим',N'Çetin'),
        (441,N'拓真',N'Захарова'),
        (442,N'시우',N'Радченко'),
        (443,N'秀英',N'لخم'),
        (444,N'مُتيّم',N'הורוביץ'),
        (445,N'הילי',N'Marbun'),
        (446,N'Elon',N'ไสยกิจ'),
        (447,N'มารุด',N'Robinson'),
        (448,N'Louis',N'Finch'),
        (449,N'Jordan',N'Powell'),
        (450,N'Patrick',N'Bennett'),
        (451,N'Benjamin',N'Zacharia'),
        (452,N'Traugott',N'Schuchhardt'),
        (453,N'Hilda',N'Villena'),
        (454,N'Leticia',N'Viera'),
        (455,N'Isis',N'Pacheco'),
        (456,N'Íris',N'Coelho'),
        (457,N'Martino',N'Marinetti'),
        (458,N'Imran',N'Gnodde'),
        (459,N'Kerstin',N'Næss'),
        (460,N'Arild',N'Sørensen'),
        (461,N'Lucas',N'Simola'),
        (462,N'Juhani',N'Tomaszuk'),
        (463,N'Tadeusz',N'Kollárová'),
        (464,N'Miloslav',N'Voinea'),
        (465,N'Drahomír',N'Kovács'),
        (466,N'Flavia',N'Bilgin'),
        (467,N'Gizella',N'Сысоев'),
        (468,N'Almast',N'Дашенко'),
        (469,N'Екатерина',N'손'),
        (470,N'裕樹',N'פז'),
        (471,N'성민',N'Pudjiastuti'),
        (472,N'桂英',N'ยาปะโลหิต'),
        (473,N'فيصل',N'Hall'),
        (474,N'רפיף',N'Reid'),
        (475,N'Mahdi',N'Martin'),
        (476,N'ณภัทร',N'Anand'),
        (477,N'Dorothy',N'Maillot'),
        (478,N'Melissa',N'Davids'),
        (479,N'Hemal',N'Quesada'),
        (480,N'Corinne',N'Santana'),
        (481,N'Hartwig',N'Carvalho'),
        (482,N'Dafne',N'Tavares'),
        (483,N'Elvira',N'Salvini'),
        (484,N'Gael',N'Strijker'),
        (485,N'Jorge',N'Nyberg'),
        (486,N'Gaetano',N'Lahtinen'),
        (487,N'Rens',N'Kiryluk'),
        (488,N'Hans',N'Slavík'),
        (489,N'Mikael',N'Šimonová'),
        (490,N'Krystian',N'Чернов'),
        (491,N'Arnošt',N'Романенко'),
        (492,N'Bronislava',N'서'),
        (493,N'Eszter',N'徐'),
        (494,N'Duruk',N'نجم'),
        (495,N'Боян',N'גוטמן'),
        (496,N'裕太',N'Iswahyudi'),
        (497,N'상현',N'ทองสีไพล'),
        (498,N'楠',N'Greene'),
        (499,N'كبير',N'Begum'),
        (500,N'הילה',N'Taylor'),
        (501,N'Ratih',N'Mcintosh'),
        (502,N'วรรณชนะชัย',N'Agarwal'),
        (503,N'Colleen',N'Bourgeois'),
        (504,N'Leah',N'Klingelhöfer'),
        (505,N'Debbie',N'Alberdi'),
        (506,N'Ekavir',N'Alonzo'),
        (507,N'Irene',N'Gonçalves'),
        (508,N'Gabino',N'Torres'),
        (509,N'Maria Julia',N'Missoni'),
        (510,N'Luigina',N'Post'),
        (511,N'Morris',N'Melin'),
        (512,N'Ebba',N'Amundsen'),
        (513,N'Kjersti',N'Østergaard'),
        (514,N'Ron',N'Pitkänen'),
        (515,N'Tuulikki',N'Ciećko'),
        (516,N'Olivie',N'Čermáková'),
        (517,N'Ema',N'Štefánik'),
        (518,N'Consuela',N'Nistor'),
        (519,N'József',N'Türk'),
        (520,N'Ayvas',N'Котова'),
        (521,N'Тит',N'Ярема'),
        (522,N'Надія',N'西村'),
        (523,N'정수',N'吴'),
        (524,N'鹏',N'طسم'),
        (525,N'هادي',N'אסולין'),
        (526,N'אדיר',N'แถมธน'),
        (527,N'Putu',N'Hayes'),
        (528,N'สุภัทริดา',N'Hancock'),
        (529,N'Rachel',N'Houston'),
        (530,N'Oliver',N'Dunlap'),
        (531,N'Amber',N'Roy'),
        (532,N'Yashasvi',N'Leroy'),
        (533,N'Martine',N'Hecker'),
        (534,N'Rosalie',N'Clemente'),
        (535,N'Macaria',N'Villalpando'),
        (536,N'Arthur',N'Guerra'),
        (537,N'Joana',N'Cunha'),
        (538,N'Silvia',N'Ponti'),
        (539,N'Rowan',N'van Kuijc van Malsen'),
        (540,N'Yngve',N'Vestin'),
        (541,N'Magne',N'Mortensen'),
        (542,N'Tapani',N'Tuominen'),
        (543,N'Raul',N'Kluj'),
        (544,N'Károly',N'Matějka'),
        (545,N'Beriye',N'Medvecký'),
        (546,N'Панфил',N'Oprea'),
        (547,N'Захар',N'Gulyás'),
        (548,N'太郎',N'Akdeniz'),
        (549,N'慧',N'Третьяков'),
        (550,N'مرتجي',N'Валенко'),
        (551,N'אלרואי',N'藤井'),
        (552,N'Darmana',N'叶'),
        (553,N'รัฐพงษ์',N'نور الدين'),
        (554,N'Alan',N'פחימה'),
        (555,N'Tracy',N'Kuswandari'),
        (556,N'Darpan',N'ตั้งกุลงาม'),
        (557,N'Guy',N'Campbell'),
        (558,N'Angelique',N'Walsh'),
        (559,N'Nadia',N'Hensley'),
        (560,N'Beatriz',N'Avila'),
        (561,N'Lúcia',N'Patel'),
        (562,N'Cecilia',N'Leblanc'),
        (563,N'Pleun',N'Reinhardt'),
        (564,N'Nils',N'Velázquez'),
        (565,N'Frode',N'Leite'),
        (566,N'Olfert',N'Onio'),
        (567,N'Eino',N'Goderts'),
        (568,N'Fabian',N'Persson'),
        (569,N'Jolana',N'Kristensen'),
        (570,N'František',N'Gregersen'),
        (571,N'Flora',N'Lehtonen'),
        (572,N'Cindoruk',N'Prokopiak'),
        (573,N'Макар',N'Kříž'),
        (574,N'Анастасія',N'Šimková'),
        (575,N'結衣',N'Nagy'),
        (576,N'순자',N'Филатова'),
        (577,N'建国',N'Данилюк'),
        (578,N'فهد',N'池田'),
        (579,N'עופרי',N'장'),
        (580,N'Gaiman',N'بكر بن وائل'),
        (581,N'อารีย์',N'זילברמן'),
        (582,N'Samantha',N'Pratama'),
        (583,N'Lawrence',N'ทหารแท้'),
        (584,N'Jonathan',N'Hart'),
        (585,N'Gayathri',N'Brown'),
        (586,N'Alain',N'Bates'),
        (587,N'Nina',N'Sule'),
        (588,N'Leonor',N'Cousin'),
        (589,N'Diego',N'Kitzmann'),
        (590,N'Gonçalo',N'Manzano'),
        (591,N'Franco',N'Cruz'),
        (592,N'Sep',N'Fonseca'),
        (593,N'Mathilda',N'Rodrigues'),
        (594,N'Eirik',N'Caetani'),
        (595,N'Andrea',N'de Wit'),
        (596,N'Olaf',N'Eriksson'),
        (597,N'Vlastimil',N'Fredriksen'),
        (598,N'Klaudia',N'Jääskeläinen'),
        (599,N'Iasmina',N'Filus'),
        (600,N'Katalin',N'Hrubý'),
        (601,N'Musafet',N'Mihálik'),
        (602,N'Сидор',N'Major'),
        (603,N'Анжела',N'Yüksel'),
        (604,N'直樹',N'Фролов'),
        (605,N'지훈',N'Ґерус'),
        (606,N'敏',N'한'),
        (607,N'لمّاح',N'王'),
        (608,N'אביגיל',N'الحسيني'),
        (609,N'Emin',N'קרן'),
        (610,N'เรืองเกียรติ',N'Rahmawati'),
        (611,N'Dean',N'คำลือ'),
        (612,N'Trevor',N'Medina'),
        (613,N'Nitesh',N'Foster'),
        (614,N'Alexandria',N'Gole'),
        (615,N'Rosa-Maria',N'Barbe'),
        (616,N'Esther',N'Riehl'),
        (617,N'Patricia',N'Casal'),
        (618,N'Yan',N'Bravo'),
        (619,N'Bruna',N'Lopes'),
        (620,N'Jolanda',N'Altera'),
        (621,N'Chloé',N'Kuijpers'),
        (622,N'Åsa',N'Ødegård'),
        (623,N'Pål',N'Jespersen'),
        (624,N'Tapio',N'Tervo'),
        (625,N'Ernest',N'Drygała'),
        (626,N'Bohumil',N'Vaňková'),
        (627,N'Felix',N'Kubička'),
        (628,N'Atatöre',N'Yorulmaz'),
        (629,N'Олимпиада',N'Максимов'),
        (630,N'Семен',N'Колесниченко'),
        (631,N'花子',N'中島'),
        (632,N'لفيف',N'许'),
        (633,N'אסתר',N'اليوزباشي'),
        (634,N'Teguh',N'אבו סנינה'),
        (635,N'จิม',N'Haryanti'),
        (636,N'Troy',N'ถิรสวัสดิ์'),
        (637,N'Travis',N'Collier'),
        (638,N'Andrew',N'Proctor'),
        (639,N'Ethan',N'French'),
        (640,N'Jonas',N'Varghese'),
        (641,N'Margarita',N'Lelièvre'),
        (642,N'Yeni',N'Schleich'),
        (643,N'Theodoro',N'Andreu'),
        (644,N'Mariana',N'Lucio'),
        (645,N'Temistocle',N'Leão'),
        (646,N'Juliette',N'Branco'),
        (647,N'Victoria',N'Caffarelli'),
        (648,N'Weena',N'Phillipsen'),
        (649,N'Henri',N'Lindblad'),
        (650,N'Marcin',N'Edvardsen'),
        (651,N'Milena',N'Christensen'),
        (652,N'Ľuboslava',N'Väisänen'),
        (653,N'Eugen',N'Hajdukiewicz'),
        (654,N'Ákos',N'Šťastný'),
        (655,N'Gülfari',N'Plavec'),
        (656,N'Данила',N'Ene'),
        (657,N'Прохір',N'Лихачев'),
        (658,N'香織',N'Овсієнко'),
        (659,N'수민',N'주'),
        (660,N'兰英',N'乔'),
        (661,N'هاني',N'مرازيق البقوم'),
        (662,N'רוני',N'לוי'),
        (663,N'Ayu',N'Zulkarnain'),
        (664,N'อินทรีย์',N'ดิสกะประกาย'),
        (665,N'Stacey',N'Webster'),
        (666,N'Matthew',N'Graham'),
        (667,N'Ekaja',N'Patterson'),
        (668,N'Timothée',N'Sen'),
        (669,N'Tiburcio',N'Dupont'),
        (670,N'Alfonso',N'Bolander'),
        (671,N'Rhavi',N'Naranjo'),
        (672,N'Álvaro',N'Palacios'),
        (673,N'Fredo',N'Antunes'),
        (674,N'Oskar',N'Galvani'),
        (675,N'Hege',N'Bos'),
        (676,N'Marja-Leena',N'Pihl'),
        (677,N'Wiktor',N'Kristoffersen'),
        (678,N'Miroslava',N'Andresen'),
        (679,N'Metod',N'Toivonen'),
        (680,N'Jenel',N'Gleba'),
        (681,N'Lütfi',N'Vojtko'),
        (682,N'Эрнест',N'Шашкова'),
        (683,N'Гаврило',N'Карпа'),
        (684,N'晃',N'بجيلة'),
        (685,N'경숙',N'שלמה'),
        (686,N'رمضان',N'Pranowo'),
        (687,N'טליה',N'ศรีวงค์'),
        (688,N'Kemal',N'Short'),
        (689,N'ชัญญานุนาย',N'Williams'),
        (690,N'Thomas',N'Adams'),
        (691,N'Devika',N'Lejeune'),
        (692,N'Constance',N'Mälzer'),
        (693,N'Sofia',N'Oliver'),
        (694,N'Bárbara',N'Colón'),
        (695,N'Ravy',N'da Rosa'),
        (696,N'Guido',N'Mota'),
        (697,N'Collin',N'Gigli'),
        (698,N'Karl',N'van der Maath'),
        (699,N'Ivar',N'Palm'),
        (700,N'Mikkel',N'Frederiksen'),
        (701,N'Elisabeth',N'Uusitalo'),
        (702,N'Maksymilian',N'Łabaj'),
        (703,N'Ilona',N'Danko'),
        (704,N'Slavomíra',N'Somogyi'),
        (705,N'Francesca',N'Мухина'),
        (706,N'Zoltán',N'Захарченко'),
        (707,N'Nurcan',N'غامد'),
        (708,N'Изот',N'אברהם'),
        (709,N'Амвросій',N'นุ่มกัน'),
        (710,N'숙자',N'Savage'),
        (711,N'娜',N'Gonzales'),
        (712,N'حيّان',N'Small'),
        (713,N'דביר',N'Kara'),
        (714,N'Hasim',N'Andre'),
        (715,N'ธีร์ธวันาย',N'Kroker'),
        (716,N'Kathryn',N'Francisco'),
        (717,N'Jason',N'Rodríguez'),
        (718,N'Wishi',N'Legnante'),
        (719,N'Victoire',N'van Hamaland'),
        (720,N'Hans-Ludwig',N'Magnusson'),
        (721,N'Emiliana',N'Ruud'),
        (722,N'Stella',N'Christoffersen'),
        (723,N'Luna',N'Kemppainen'),
        (724,N'Michele',N'Miksza'),
        (725,N'Cornelia',N'Kopecká'),
        (726,N'Kenn',N'Podkonická'),
        (727,N'Augustín',N'Tabacu'),
        (728,N'Anghel',N'Tarhan'),
        (729,N'Laura',N'Антонова'),
        (730,N'Benice',N'Дараган'),
        (731,N'Авксентий',N'조'),
        (732,N'Веніямин',N'贾'),
        (733,N'陽子',N'بنو أسد'),
        (734,N'丽丽',N'Narpati'),
        (735,N'فلاح',N'นกทอง'),
        (736,N'מאיה',N'Knight'),
        (737,N'Rahmi',N'Perez'),
        (738,N'ทับทิม',N'Miller'),
        (739,N'Heidi',N'Hebert'),
        (740,N'Nancy',N'Kostolzin'),
        (741,N'Danny',N'Tello'),
        (742,N'Nicholas',N'Ruelas'),
        (743,N'Georges',N'Dias'),
        (744,N'Alicja',N'Ceravolo'),
        (745,N'Candela',N'Rijn'),
        (746,N'Celia',N'Bakke'),
        (747,N'Julia',N'Mikkelsen'),
        (748,N'Isaac',N'Jäntti'),
        (749,N'Kent',N'Kycia'),
        (750,N'Magnus',N'Kratochvílová'),
        (751,N'Daniella',N'Timeková'),
        (752,N'Kalle',N'Albu'),
        (753,N'Borys',N'Hançer'),
        (754,N'Krištof',N'Осипова'),
        (755,N'Revza',N'Ісаєнко'),
        (756,N'Эммануил',N'後藤'),
        (757,N'Юхим',N'차'),
        (758,N'우진',N'张'),
        (759,N'宇',N'بنو حنيفة'),
        (760,N'قاسم',N'חזן'),
        (761,N'Paiman',N'Tarihoran'),
        (762,N'วีรชัย',N'ตราชู'),
        (763,N'Ryan',N'Nguyen'),
        (764,N'Katy',N'Pritchard'),
        (765,N'Donna',N'Becker'),
        (766,N'Capucine',N'Baria'),
        (767,N'Tilly',N'Lefort'),
        (768,N'Aurelio',N'Hamann'),
        (769,N'Olivia',N'Ariza'),
        (770,N'Mateus',N'Soliz'),
        (771,N'Catarina',N'Zichichi'),
        (772,N'Jacopo',N'van Herstal'),
        (773,N'Siv',N'Gunnarsson'),
        (774,N'Timmy',N'Arnesen'),
        (775,N'Johannes',N'Karvonen'),
        (776,N'Fryderyk',N'Grzelec'),
        (777,N'Bedřich',N'Dostál'),
        (778,N'Adela',N'Hagara'),
        (779,N'Stela',N'Papp'),
        (780,N'Süvari',N'Arsoy'),
        (781,N'Вениамин',N'Горбачев'),
        (782,N'Олекса',N'Дзюба'),
        (783,N'裕美子',N'الفتياني'),
        (784,N'정훈',N'בכר'),
        (785,N'璐',N'Rajasa'),
        (786,N'جعفر',N'มนทอง'),
        (787,N'יעל',N'Byrne'),
        (788,N'Endah',N'Banik'),
        (789,N'หฤทัย',N'Lebreton'),
        (790,N'Suzanne',N'Boucsein'),
        (791,N'Rajeshri',N'Hurtado'),
        (792,N'Marc',N'Arellano'),
        (793,N'Alberto',N'Vieira'),
        (794,N'Víctor',N'Freitas'),
        (795,N'Ana María',N'Simeoni'),
        (796,N'Carminho',N'van Limburg'),
        (797,N'Milou',N'Lundqvist'),
        (798,N'Steve',N'Konttinen'),
        (799,N'Jussi',N'Kamieniarz'),
        (800,N'Wojciech',N'Černá'),
        (801,N'Ivona',N'Bukovská'),
        (802,N'Oldrich',N'Тимченко'),
        (803,N'Marioara',N'松本'),
        (804,N'Géza',N'顾'),
        (805,N'Asiman',N'عتيبة'),
        (806,N'Юрий',N'שורץ'),
        (807,N'Богодар',N'Riyanti'),
        (808,N'秀荣',N'ทวีเดช'),
        (809,N'دريد',N'Martinez'),
        (810,N'יונתן',N'Balakrishnan'),
        (811,N'Pangeran',N'Pruvost'),
        (812,N'กฤตพร',N'Finke'),
        (813,N'Bethan',N'Pedroza'),
        (814,N'Frederick',N'Esteves'),
        (815,N'Joshua',N'Treccani'),
        (816,N'Harita',N'van der Klein'),
        (817,N'Adélaïde',N'Norberg'),
        (818,N'Kriemhild',N'Lunde'),
        (819,N'Manu',N'Erkkilä'),
        (820,N'Luisa',N'Huczek'),
        (821,N'Léo',N'Krausová'),
        (822,N'Piermaria',N'Dekýšová'),
        (823,N'Nick',N'Diaconescu'),
        (824,N'Maj-Britt',N'Bálint'),
        (825,N'Villads',N'Ülker'),
        (826,N'Matti',N'Талан'),
        (827,N'Ľuboš',N'مشعشع'),
        (828,N'Ada',N'Mayasari'),
        (829,N'Piroska',N'ธัญเสถียร'),
        (830,N'Ergül',N'Young'),
        (831,N'Венедикт',N'Bond'),
        (832,N'Максим',N'Wallace'),
        (833,N'زيدان',N'Ravel'),
        (834,N'אסיל',N'Turpin'),
        (835,N'Paramita',N'Franke'),
        (836,N'ฟิรยา',N'Piñeiro'),
        (837,N'Katherine',N'Campos'),
        (838,N'Sian',N'Malacarne'),
        (839,N'Aahana',N'Arts'),
        (840,N'Odette',N'Jonsson'),
        (841,N'Frithjof',N'Strøm'),
        (842,N'Gastón',N'Klausen'),
        (843,N'Andrés',N'Perälä'),
        (844,N'Francisco',N'Rajda'),
        (845,N'Kiki',N'Novotný'),
        (846,N'Alicia',N'Moric'),
        (847,N'Martin',N'Seven'),
        (848,N'Vera',N'Потапов'),
        (849,N'Pauliina',N'Слюсар'),
        (850,N'Liwia',N'李'),
        (851,N'Ferdinand',N'المهرة'),
        (852,N'Juliana',N'עיסא'),
        (853,N'István',N'Hariyah'),
        (854,N'Oldağ',N'นักสำหรวจ'),
        (855,N'Мария',N'Johnson'),
        (856,N'Феофан',N'Peters'),
        (857,N'준혁',N'Gill'),
        (858,N'زاكي',N'George'),
        (859,N'עטרה',N'Bumb'),
        (860,N'Banawa',N'Bonnin'),
        (861,N'พีรพัฒน์',N'Weimer'),
        (862,N'Debra',N'Mesa'),
        (863,N'Saumya',N'Menéndez'),
        (864,N'René',N'da Conceição'),
        (865,N'Giovanna',N'Leal'),
        (866,N'Maricruz',N'Comencini'),
        (867,N'Héctor',N'Lensen'),
        (868,N'Rafaela',N'Åkesson'),
        (869,N'Amina',N'Hiltunen'),
        (870,N'Aina',N'Heinrich'),
        (871,N'Marte',N'Němeček'),
        (872,N'Philip',N'Ján'),
        (873,N'Oskari',N'Erdoğan'),
        (874,N'Juliusz',N'Нестеров'),
        (875,N'Dita',N'Нестеренко'),
        (876,N'Magdaléna',N'山本'),
        (877,N'Amedeu',N'اسطمبولي'),
        (878,N'Nurbanu',N'חסן'),
        (879,N'Влас',N'Morris'),
        (880,N'Данна',N'Carpenter'),
        (881,N'영숙',N'Carter'),
        (882,N'تالا',N'Hernandez'),
        (883,N'פאטמה',N'Dua'),
        (884,N'Radit',N'Nicolas'),
        (885,N'ดารุนี',N'Stiebitz'),
        (886,N'Adam',N'Casals'),
        (887,N'Jill',N'Uribe'),
        (888,N'Diana',N'Fogaça'),
        (889,N'Pavani',N'Magalhães'),
        (890,N'Pierre',N'Botta'),
        (891,N'Dorotea',N'Köster'),
        (892,N'Rosalia',N'Ali'),
        (893,N'Ayla',N'Bøe'),
        (894,N'Rosa',N'Olsen'),
        (895,N'Julius',N'Voutilainen'),
        (896,N'Ellen',N'Osica'),
        (897,N'Sys',N'Sedláková'),
        (898,N'Mateusz',N'Baška'),
        (899,N'Kateřina',N'Pușcașu'),
        (900,N'Rastislav',N'Pap'),
        (901,N'Axenia',N'Zorlu'),
        (902,N'Dóra',N'Ершова'),
        (903,N'Sevindik',N'Ященко'),
        (904,N'Епифан',N'曹'),
        (905,N'Павло',N'بحمدوني'),
        (906,N'太一',N'אשכנזי'),
        (907,N'영호',N'Mulyani'),
        (908,N'颖',N'ภูภักดี'),
        (909,N'شعلان',N'Dean'),
        (910,N'אביטל',N'Porter'),
        (911,N'Balijan',N'Mohanty'),
        (912,N'คำ',N'Fouquet'),
        (913,N'Eric',N'Junken'),
        (914,N'Roy',N'Maestre'),
        (915,N'Kristy',N'Mendoza'),
        (916,N'Fiyaz',N'Castro'),
        (917,N'Ingetraut',N'Campano'),
        (918,N'Lino',N'van de Velde'),
        (919,N'Pierpaolo',N'Svahn'),
        (920,N'Jordy',N'Sandvik'),
        (921,N'Simon',N'Poulsen'),
        (922,N'Mona',N'Hautala'),
        (923,N'Bertil',N'Kolbusz'),
        (924,N'Helena',N'Machová'),
        (925,N'Karina',N'Slobodník'),
        (926,N'Lenka',N'Manole'),
        (927,N'Aleš',N'Akgündüz'),
        (928,N'Petre',N'Белоусова'),
        (929,N'Hunalp',N'Чумак'),
        (930,N'Савелий',N'بنو النجار'),
        (931,N'Маруся',N'רביבו'),
        (932,N'修平',N'Saptono'),
        (933,N'서영',N'ตั้งรบ'),
        (934,N'俊',N'Reynolds'),
        (935,N'جوليا',N'Gardner'),
        (936,N'יצחק',N'Narasimhan'),
        (937,N'Asmuni',N'Gauthier'),
        (938,N'พรเบญญา',N'Reising'),
        (939,N'Mark',N'Alarcón'),
        (940,N'Gareth',N'Barela'),
        (941,N'Veronica',N'Cardoso'),
        (942,N'Gesche',N'Barcella'),
        (943,N'Gil',N'van Rheineck'),
        (944,N'Romina',N'Engman'),
        (945,N'Sophia',N'Seppälä'),
        (946,N'Bo',N'Mitoraj'),
        (947,N'Merete',N'Musilová'),
        (948,N'Natan',N'Roháčová'),
        (949,N'Zikmund',N'Bodnár'),
        (950,N'Ramona',N'Гаврюшенко'),
        (951,N'Ferenc',N'長谷川'),
        (952,N'Eriş',N'季'),
        (953,N'Гордей',N'شهران'),
        (954,N'Аарон',N'Nashiruddin'),
        (955,N'지아',N'Walker'),
        (956,N'洁',N'Poole'),
        (957,N'صاحب',N'Murphy'),
        (958,N'משה',N'Murthy'),
        (959,N'Empluk',N'Buisson'),
        (960,N'ศศิรินทร์',N'Schmidtke'),
        (961,N'Lauren',N'Valbuena'),
        (962,N'Shane',N'Franco'),
        (963,N'Juan',N'Correia'),
        (964,N'Brijesh',N'Morabito'),
        (965,N'Andrée',N'van Dongen'),
        (966,N'Friedrich-Karl',N'Lindahl'),
        (967,N'Guiomar',N'Haugen'),
        (968,N'María Luisa',N'Tiainen'),
        (969,N'João Lucas',N'Witas'),
        (970,N'Fabiana',N'Havran'),
        (971,N'Raffaele',N'Dobre'),
        (972,N'Catharina',N'Kısakürek'),
        (973,N'Margaretha',N'Стрелкова'),
        (974,N'Espen',N'Штепа'),
        (975,N'Frans',N'山田'),
        (976,N'Annikki',N'الشاويش'),
        (977,N'Kazimierz',N'נעים'),
        (978,N'Miluše',N'Mandala'),
        (979,N'Lică',N'นิลวิมล'),
        (980,N'Ünsever',N'Jackson'),
        (981,N'Фома',N'Mullen'),
        (982,N'Марко',N'Rana'),
        (983,N'直子',N'Merle'),
        (984,N'서연',N'Schacht'),
        (985,N'杨',N'Oquendo'),
        (986,N'سلام',N'Macedo'),
        (987,N'ירדן',N'Seddio'),
        (988,N'Caraka',N'Keltenie'),
        (989,N'ณัฐพิชา',N'Josefsson'),
        (990,N'Brittany',N'Nielsen'),
        (991,N'Deborah',N'Kankaanpää'),
        (992,N'Erin',N'Steciuk'),
        (993,N'Shivansh',N'Karul'),
        (994,N'Renée',N'Mocanu'),
        (995,N'Hansgeorg',N'Biró'),
        (996,N'Anabel',N'Николаева'),
        (997,N'Miguel Ángel',N'Ващенко'),
        (998,N'Angelica',N'成'),
        (999,N'Esmee',N'دوبلال'),
        (1000,N'Rigmor',N'אליהו')
    ) v(Seq,FirstName,LastName)
)
UPDATE p
SET p.FirstName = nm.FirstName,
    p.LastName = nm.LastName
FROM dbo.Patient p
JOIN NameMap nm ON nm.Seq=p.PatientID;
GO

IF EXISTS (
    SELECT FirstName
    FROM dbo.Patient
    GROUP BY FirstName
    HAVING COUNT(*) > 1
)
    THROW 51010,'Duplicate FirstName detected after synthetic name generation.',1;
GO

IF EXISTS (
    SELECT FullName
    FROM dbo.Patient
    GROUP BY FullName
    HAVING COUNT(*) > 1
)
    THROW 51011,'Duplicate FullName detected after synthetic name generation.',1;
GO

DECLARE @TrainingAsOfDate_PatientTimeline date='2026-08-01';

;WITH s AS(
    SELECT
        PatientID,
        CASE WHEN DateOfBirth>CAST('1995-01-01' AS date)
             THEN DateOfBirth ELSE CAST('1995-01-01' AS date) END AS RegistrationStart
    FROM dbo.Patient
), r AS(
    SELECT
        PatientID,
        DATEADD(
            day,
            (PatientID*137) % (DATEDIFF(day,RegistrationStart,@TrainingAsOfDate_PatientTimeline)+1),
            CAST(RegistrationStart AS datetime2(0))
        ) AS RegistrationDateTime
    FROM s
)
UPDATE p
SET p.CreatedAt = r.RegistrationDateTime,
    p.UpdatedAt = DATEADD(
        day,
        (p.PatientID*17) % (DATEDIFF(day,CAST(r.RegistrationDateTime AS date),@TrainingAsOfDate_PatientTimeline)+1),
        r.RegistrationDateTime
    ),
    p.LastSeenAt = CASE WHEN p.DeceasedFlag=1 THEN NULL ELSE
        DATEADD(
            day,
            (p.PatientID*23) % (DATEDIFF(day,CAST(r.RegistrationDateTime AS date),@TrainingAsOfDate_PatientTimeline)+1),
            r.RegistrationDateTime
        )
    END
FROM dbo.Patient p
JOIN r ON r.PatientID=p.PatientID;
GO

DECLARE @TrainingAsOfDate_Death date='2026-08-01';
UPDATE p
SET DeathDateTime = CASE WHEN DeceasedFlag=1 THEN
        DATEADD(
            day,
            (PatientID*29) % (DATEDIFF(day,CAST(UpdatedAt AS date),@TrainingAsOfDate_Death)+1),
            UpdatedAt
        )
        ELSE NULL END
FROM dbo.Patient p;
GO

UPDATE dbo.Patient
SET LastSeenAt = DeathDateTime
WHERE DeceasedFlag=1;
GO

INSERT dbo.InsurancePolicy(PatientID,PayerName,PlanName,MemberNumber,FinancialClass,EffectiveDate,ExpiryDate,PrimaryFlag)
SELECT PatientID,
 CHOOSE(1+((PatientID-1)%8),N'Ontario Health',N'MirthCare Benefits',N'Blue Training Shield',N'Global Student Health',N'Workers Compensation Training',N'Federal Health Program',N'Private International Plan',N'Self Pay'),
 CHOOSE(1+((PatientID-1)%6),N'OHIP Synthetic',N'Extended Health',N'Basic Hospital',N'International Visitor',N'Workplace Injury',N'Self Pay'),
 CONCAT('MEM',900000000+PatientID),CHOOSE(1+((PatientID-1)%6),'PROV','PRIVATE','SELF','WSIB','FED','INTL'),
 CASE WHEN DateOfBirth>CAST('2024-01-01' AS date) THEN DateOfBirth ELSE CAST('2024-01-01' AS date) END,'2028-12-31',1
FROM dbo.Patient;
GO

;WITH e0 AS(
 SELECT p.PatientID,p.DateOfBirth,v.Seq
 FROM dbo.Patient p
 CROSS JOIN(VALUES(1),(2),(3))v(Seq)
 WHERE p.DeceasedFlag=0 OR v.Seq<3
), e1 AS(
 SELECT
    PatientID,
    Seq,
    CASE WHEN DateOfBirth>CAST('1995-01-01' AS date)
         THEN DateOfBirth ELSE CAST('1995-01-01' AS date) END AS EarliestAdmitDate
 FROM e0
), e2 AS(
 SELECT
    PatientID,
    Seq,
    DATEADD(
        day,
        (PatientID*97 + Seq*389) % (DATEDIFF(day,EarliestAdmitDate,CAST('2026-08-01' AS date))+1),
        CAST(EarliestAdmitDate AS datetime2(0))
    ) AS AdmitBase
 FROM e1
), e AS(
 SELECT
    PatientID,
    Seq,
    DATEADD(hour,(PatientID+Seq)%24,AdmitBase) AS AdmitDateTime
 FROM e2
)
INSERT dbo.Encounter(VisitNumber,PatientID,FacilityID,DepartmentID,LocationID,PatientClass,EncounterType,EncounterStatus,
 ServiceCode,AdmitDateTime,DischargeDateTime,AttendingProviderID,AdmittingProviderID,ReferringProviderID,AdmissionSource,
 DischargeDisposition,AssignedUnit,AssignedRoom,AssignedBed,PriorUnit,PriorRoom,PriorBed,FinancialClass,CreatedAt,UpdatedAt)
SELECT TOP (2147483647) CONCAT('VIS',RIGHT('0000000000'+CAST(PatientID*10+Seq AS varchar(10)),10)),PatientID,1+((PatientID+Seq-2)%3),
 1+((PatientID*3+Seq-2)%20),1+((PatientID*5+Seq-2)%125),CHOOSE(1+((PatientID+Seq-2)%4),'I','O','E','R'),
 CHOOSE(1+((PatientID+Seq-2)%6),'Inpatient','Outpatient','Emergency','Rehabilitation','Clinic','Observation'),'Discharged',
 CHOOSE(1+((PatientID+Seq-2)%15),'ED','CARD','NEUR','ONC','ORTH','GENS','ICU','MED','MH','PEDS','OBGYN','RENAL','RESP','REHAB','CCC'),
 AdmitDateTime,
 DATEADD(day,1+((PatientID+Seq)%5),AdmitDateTime),
 1+((PatientID*7+Seq-2)%80),1+((PatientID*11+Seq-2)%80),1+((PatientID*13+Seq-2)%80),
 CHOOSE(1+((PatientID+Seq-2)%6),'Emergency','Clinic Referral','Transfer','Scheduled','Physician Referral','Self Referral'),
 CHOOSE(1+((PatientID+Seq-2)%6),'Home','Rehabilitation','Long Term Care','Transfer','Against Medical Advice','Expired'),
 CONCAT('U',RIGHT('00'+CAST(1+(PatientID+Seq)%20 AS varchar(2)),2)),CONCAT('R',100+(PatientID+Seq)%80),CONCAT('B',1+(PatientID+Seq)%2),
 CONCAT('U',RIGHT('00'+CAST(1+(PatientID+Seq+3)%20 AS varchar(2)),2)),CONCAT('R',100+(PatientID+Seq+7)%80),CONCAT('B',1+(PatientID+Seq+1)%2),
 CHOOSE(1+((PatientID+Seq-2)%6),'PROV','PRIVATE','SELF','WSIB','FED','INTL'),
 AdmitDateTime,DATEADD(day,1+((PatientID+Seq)%5),AdmitDateTime)
FROM e
ORDER BY PatientID,Seq;
GO

;WITH a AS(SELECT TOP(180) PatientID FROM dbo.Patient WHERE DeceasedFlag=0 ORDER BY PatientID)
INSERT dbo.Encounter(VisitNumber,PatientID,FacilityID,DepartmentID,LocationID,PatientClass,EncounterType,EncounterStatus,
 ServiceCode,AdmitDateTime,DischargeDateTime,AttendingProviderID,AdmittingProviderID,ReferringProviderID,AdmissionSource,
 DischargeDisposition,AssignedUnit,AssignedRoom,AssignedBed,PriorUnit,PriorRoom,PriorBed,FinancialClass,CreatedAt,UpdatedAt)
SELECT CONCAT('ACT',RIGHT('0000000000'+CAST(PatientID AS varchar(10)),10)),PatientID,1,1+((PatientID-1)%20),1+((PatientID*9-1)%125),
 CASE WHEN PatientID%5=0 THEN 'R' ELSE 'I' END,CASE WHEN PatientID%5=0 THEN 'Rehabilitation' ELSE 'Inpatient' END,
 'Active',CHOOSE(1+((PatientID-1)%12),'CARD','NEUR','ONC','ORTH','ICU','MED','MH','PEDS','OBGYN','RENAL','RESP','REHAB'),
 DATEADD(day,-(1+PatientID%25),DATEADD(hour,PatientID%24,CAST('2026-08-01' AS datetime2))),NULL,
 1+((PatientID*7-1)%80),1+((PatientID*11-1)%80),1+((PatientID*13-1)%80),
 CHOOSE(1+((PatientID-1)%4),'Emergency','Transfer','Scheduled','Clinic Referral'),NULL,
 CONCAT('U',RIGHT('00'+CAST(1+PatientID%20 AS varchar(2)),2)),CONCAT('R',100+PatientID%80),CONCAT('B',1+PatientID%2),
 NULL,NULL,NULL,CHOOSE(1+((PatientID-1)%6),'PROV','PRIVATE','SELF','WSIB','FED','INTL'),
 DATEADD(day,-(2+PatientID%25),CAST('2026-08-01' AS datetime2)),CAST('2026-08-01' AS datetime2)
FROM a;
GO

INSERT dbo.ADTEvent(EncounterID,PatientID,TriggerEvent,MessageType,EventDateTime,ToUnit,ToRoom,ToBed,MessageControlID,ProcessingStatus)
SELECT EncounterID,PatientID,'A01','ADT^A01',AdmitDateTime,AssignedUnit,AssignedRoom,AssignedBed,CONCAT('A01-',EncounterID),'PROCESSED' FROM dbo.Encounter;
INSERT dbo.ADTEvent(EncounterID,PatientID,TriggerEvent,MessageType,EventDateTime,FromUnit,FromRoom,FromBed,ToUnit,ToRoom,ToBed,MessageControlID,ProcessingStatus)
SELECT EncounterID,PatientID,'A08','ADT^A08',DATEADD(hour,4,AdmitDateTime),AssignedUnit,AssignedRoom,AssignedBed,AssignedUnit,AssignedRoom,AssignedBed,CONCAT('A08-',EncounterID),'PROCESSED' FROM dbo.Encounter WHERE EncounterID%2=0;
INSERT dbo.ADTEvent(EncounterID,PatientID,TriggerEvent,MessageType,EventDateTime,FromUnit,FromRoom,FromBed,ToUnit,ToRoom,ToBed,MessageControlID,ProcessingStatus)
SELECT EncounterID,PatientID,'A02','ADT^A02',DATEADD(day,1,AdmitDateTime),PriorUnit,PriorRoom,PriorBed,AssignedUnit,AssignedRoom,AssignedBed,CONCAT('A02-',EncounterID),'PROCESSED' FROM dbo.Encounter WHERE PatientClass IN('I','R') AND EncounterID%3=0;
INSERT dbo.ADTEvent(EncounterID,PatientID,TriggerEvent,MessageType,EventDateTime,FromUnit,FromRoom,FromBed,MessageControlID,ProcessingStatus)
SELECT EncounterID,PatientID,'A03','ADT^A03',DischargeDateTime,AssignedUnit,AssignedRoom,AssignedBed,CONCAT('A03-',EncounterID),'PROCESSED' FROM dbo.Encounter WHERE DischargeDateTime IS NOT NULL;
GO

INSERT dbo.Diagnosis(EncounterID,PatientID,DiagnosisCode,DiagnosisDescription,DiagnosisCategory,DiagnosisType,PresentOnAdmission,DiagnosisDate,ActiveFlag)
SELECT EncounterID,PatientID,
 CHOOSE(1+((EncounterID-1)%20),'I10','E11.9','J45.909','E78.5','I50.9','J18.9','N18.3','M17.9','F41.9','F32.9','G43.909','K21.9','I48.91','D64.9','E03.9','M54.5','N39.0','R07.9','S72.009A','C50.919'),
 CHOOSE(1+((EncounterID-1)%20),N'Essential hypertension',N'Type 2 diabetes mellitus',N'Asthma',N'Hyperlipidemia',N'Heart failure',N'Pneumonia',N'Chronic kidney disease stage 3',N'Osteoarthritis of knee',N'Anxiety disorder',N'Depressive disorder',N'Migraine',N'Gastroesophageal reflux disease',N'Atrial fibrillation',N'Anemia',N'Hypothyroidism',N'Low back pain',N'Urinary tract infection',N'Chest pain',N'Hip fracture',N'Breast cancer'),
 CHOOSE(1+((EncounterID-1)%10),N'Cardiovascular',N'Endocrine',N'Respiratory',N'Metabolic',N'Renal',N'Musculoskeletal',N'Mental Health',N'Neurological',N'Infectious Disease',N'Oncology'),
 CASE WHEN EncounterID%4=0 THEN 'Secondary' ELSE 'Primary' END,CASE WHEN EncounterID%5=0 THEN 0 ELSE 1 END,
 CAST(AdmitDateTime AS date),1
FROM dbo.Encounter;

INSERT dbo.Allergy(PatientID,Allergen,AllergyType,Reaction,Severity,Status,OnsetDate)
SELECT PatientID,CHOOSE(1+((PatientID-1)%12),N'Penicillin',N'Shellfish',N'Peanuts',N'Latex',N'Sulfonamides',N'Ibuprofen',N'Contrast Media',N'Egg',N'Milk',N'Pollen',N'Dust Mites',N'No Known Drug Allergy'),
 CHOOSE(1+((PatientID-1)%4),'Drug','Food','Environmental','Other'),CHOOSE(1+((PatientID-1)%8),N'Rash',N'Hives',N'Swelling',N'Nausea',N'Wheezing',N'Anaphylaxis',N'Itching',N'Unknown'),
 CHOOSE(1+((PatientID-1)%4),'Mild','Moderate','Severe','Unknown'),'Active',DATEADD(day,(PatientID*31) % (DATEDIFF(day,DateOfBirth,CAST('2026-08-01' AS date))+1),DateOfBirth)
FROM dbo.Patient WHERE PatientID%3<>0;
GO

INSERT dbo.MedicationOrder(EncounterID,PatientID,OrderNumber,MedicationCode,MedicationName,Dose,DoseUnit,Route,Frequency,OrderStatus,OrderDateTime,OrderingProviderID)
SELECT EncounterID,PatientID,CONCAT('MED',RIGHT('0000000000'+CAST(EncounterID AS varchar(10)),10)),
 CHOOSE(1+((EncounterID-1)%12),'ACET','METF','AMLO','ATOR','PANT','SALB','FURO','ENOX','CEF','OND','GABA','LEV'),
 CHOOSE(1+((EncounterID-1)%12),N'Acetaminophen',N'Metformin',N'Amlodipine',N'Atorvastatin',N'Pantoprazole',N'Salbutamol',N'Furosemide',N'Enoxaparin',N'Ceftriaxone',N'Ondansetron',N'Gabapentin',N'Levothyroxine'),
 CAST(CHOOSE(1+((EncounterID-1)%8),1,2,5,10,20,40,500,1000) AS decimal(10,2)),CHOOSE(1+((EncounterID-1)%4),'mg','mg','mcg','mL'),
 CHOOSE(1+((EncounterID-1)%5),'PO','IV','SC','INH','IM'),CHOOSE(1+((EncounterID-1)%6),'DAILY','BID','TID','QID','Q4H PRN','STAT'),
 CASE WHEN EncounterStatus='Active' THEN 'ACTIVE' ELSE 'COMPLETED' END,DATEADD(hour,1,AdmitDateTime),AttendingProviderID
FROM dbo.Encounter;

INSERT dbo.LabOrder(EncounterID,PatientID,AccessionNumber,OrderCode,OrderName,Priority,OrderStatus,OrderDateTime,CollectedDateTime,ResultedDateTime,OrderingProviderID)
SELECT EncounterID,PatientID,CONCAT('ACC',RIGHT('0000000000'+CAST(EncounterID AS varchar(10)),10)),
 CHOOSE(1+((EncounterID-1)%6),'CBC','BMP','CMP','TROP','A1C','TSH'),
 CHOOSE(1+((EncounterID-1)%6),N'Complete Blood Count',N'Basic Metabolic Panel',N'Comprehensive Metabolic Panel',N'Troponin',N'Hemoglobin A1C',N'Thyroid Stimulating Hormone'),
 CASE WHEN PatientClass='E' THEN 'STAT' ELSE 'ROUTINE' END,'FINAL',DATEADD(hour,1,AdmitDateTime),DATEADD(hour,2,AdmitDateTime),DATEADD(hour,5,AdmitDateTime),AttendingProviderID
FROM dbo.Encounter;

INSERT dbo.LabResult(LabOrderID,TestCode,TestName,ResultValue,ResultUnit,ReferenceRange,AbnormalFlag,ResultStatus,ObservationDateTime)
SELECT lo.LabOrderID,v.TestCode,v.TestName,v.ResultValue,v.ResultUnit,v.ReferenceRange,v.AbnormalFlag,'FINAL',DATEADD(minute,v.OffsetMinutes,lo.ResultedDateTime)
FROM dbo.LabOrder lo
CROSS APPLY(VALUES
 ('WBC',N'White Blood Cell Count',CONVERT(varchar(20),CAST(4.0+(lo.LabOrderID%90)/10.0 AS decimal(5,1))),'10^9/L','4.0-11.0',CASE WHEN lo.LabOrderID%11=0 THEN 'H' ELSE 'N' END,0),
 ('HGB',N'Hemoglobin',CONVERT(varchar(20),CAST(100+lo.LabOrderID%70 AS decimal(5,1))),'g/L','120-170',CASE WHEN lo.LabOrderID%7=0 THEN 'L' ELSE 'N' END,1),
 ('NA',N'Sodium',CONVERT(varchar(20),CAST(130+lo.LabOrderID%16 AS int)),'mmol/L','135-145',CASE WHEN lo.LabOrderID%9=0 THEN 'L' ELSE 'N' END,2)
)v(TestCode,TestName,ResultValue,ResultUnit,ReferenceRange,AbnormalFlag,OffsetMinutes);
GO

INSERT dbo.InterfaceMessage(PatientID,EncounterID,ChannelName,MessageType,MessageControlID,SourceSystem,DestinationSystem,ReceivedDateTime,ProcessedDateTime,ProcessingStatus,RetryCount,PayloadFormat)
SELECT PatientID,EncounterID,
 CHOOSE(1+((EncounterID-1)%5),'PRD-EHR-MIRTH-ADT','PRD-EHR-MIRTH-LAB','PRD-EHR-MIRTH-PHARMACY','PRD-MIRTH-REPORTING','DEV-SQL-MIRTH-TRAINING'),
 CASE WHEN EncounterID%5=0 THEN 'ORU^R01' ELSE 'ADT^A01' END,CONCAT('INT-',EncounterID),
 CHOOSE(1+((EncounterID-1)%4),'Epic','Cerner','Meditech','MirthCareEHR'),
 CHOOSE(1+((EncounterID-1)%5),'Mirth Connect','SQL Server','Pyxis','PACS','Analytics'),AdmitDateTime,DATEADD(second,1+EncounterID%20,AdmitDateTime),
 CASE WHEN EncounterID%19=0 THEN 'ERROR' ELSE 'PROCESSED' END,CASE WHEN EncounterID%19=0 THEN 1+EncounterID%3 ELSE 0 END,'HL7'
FROM dbo.Encounter;

INSERT dbo.InterfaceError(InterfaceMessageID,ErrorDateTime,ErrorCode,ErrorCategory,ErrorMessage,ResolvedFlag,ResolutionNotes)
SELECT InterfaceMessageID,DATEADD(second,5,ReceivedDateTime),
 CHOOSE(1+((InterfaceMessageID-1)%5),'SQL_TIMEOUT','MLLP_RESET','INVALID_MRN','MISSING_FIELD','JDBC_ERROR'),
 CHOOSE(1+((InterfaceMessageID-1)%5),'Database','Network','Validation','HL7 Mapping','JDBC'),
 CHOOSE(1+((InterfaceMessageID-1)%5),N'Database command timed out during training scenario.',N'Downstream connection reset during MLLP send.',N'Patient MRN failed synthetic validation.',N'Required HL7 field was missing.',N'JDBC connection failed during training scenario.'),
 CASE WHEN InterfaceMessageID%2=0 THEN 1 ELSE 0 END,CASE WHEN InterfaceMessageID%2=0 THEN N'Resolved during synthetic training exercise.' END
FROM dbo.InterfaceMessage WHERE ProcessingStatus='ERROR';

INSERT dbo.AuditLog(EventDateTime,UserOrSystem,ActionType,EntityType,EntityID,PatientID,Description,SourceIP)
SELECT UpdatedAt,CASE WHEN PatientID%2=0 THEN 'Mirth Connect' ELSE 'RegistrationUser' END,
 CASE WHEN PatientID%3=0 THEN 'UPDATE' ELSE 'CREATE' END,'Patient',CAST(PatientID AS varchar(50)),PatientID,
 N'Synthetic patient record activity for SQL training.',CONCAT('10.10.',PatientID%20,'.',10+PatientID%200)
FROM dbo.Patient;
GO

/*
===============================================================================
ENRICHMENT / DATA-QUALITY PASS
Keeps the original deterministic data generation, then fills the enhanced
columns and makes commonly taught related fields more coherent.
===============================================================================
*/

-- Facility / department / location / provider enrichment
UPDATE dbo.Facility
SET FacilityType = CASE FacilityID WHEN 1 THEN 'Acute Care Hospital'
                                   WHEN 2 THEN 'Rehabilitation Hospital'
                                   ELSE 'Ambulatory Clinic' END,
    TimeZone = 'America/Toronto',
    ActiveFlag = 1;

UPDATE dbo.Department
SET CostCenterCode = CONCAT('CC-',RIGHT('0000'+CAST(DepartmentID AS varchar(4)),4)),
    DepartmentPhone = CONCAT('+1-416-555-',RIGHT('0000'+CAST(2000+DepartmentID AS varchar(4)),4)),
    ActiveFlag = 1;

UPDATE l
SET Building = CASE d.FacilityID WHEN 1 THEN N'Main Hospital'
                                 WHEN 2 THEN N'Rehabilitation Centre'
                                 ELSE N'Community Clinic' END,
    FloorNumber = 1 + ((l.LocationID-1)%8),
    ActiveFlag = 1
FROM dbo.Location l
JOIN dbo.Department d ON d.DepartmentID=l.DepartmentID;

UPDATE dbo.Provider
SET ProviderType = 'Physician',
    Credentials = 'MD',
    ActiveFlag = 1;

-- Accurate age as of the deterministic training reference date
DECLARE @TrainingAsOfDate date='2026-08-01';

UPDATE p
SET AgeYears =
    DATEDIFF(year,p.DateOfBirth,@TrainingAsOfDate)
    - CASE WHEN DATEADD(year,DATEDIFF(year,p.DateOfBirth,@TrainingAsOfDate),p.DateOfBirth) > @TrainingAsOfDate THEN 1 ELSE 0 END
FROM dbo.Patient p;

UPDATE dbo.Patient
SET AgeGroup = CASE WHEN AgeYears < 1 THEN 'Infant'
                    WHEN AgeYears < 13 THEN 'Child'
                    WHEN AgeYears < 18 THEN 'Adolescent'
                    WHEN AgeYears < 65 THEN 'Adult'
                    ELSE 'Older Adult' END;

-- Coherent residence location triplets for easier SQL/Excel demonstrations.
UPDATE p
SET City = x.City,
    ProvinceState = x.ProvinceState,
    Country = x.Country,
    PostalCode = x.PostalCode
FROM dbo.Patient p
CROSS APPLY (VALUES
    (1,N'Toronto',N'Ontario',N'Canada','M5V 2T6'),
    (2,N'Brampton',N'Ontario',N'Canada','L6Y 1N7'),
    (3,N'Mississauga',N'Ontario',N'Canada','L5B 3C1'),
    (4,N'Ottawa',N'Ontario',N'Canada','K1P 1J1'),
    (5,N'Vancouver',N'British Columbia',N'Canada','V6B 1A1'),
    (6,N'Calgary',N'Alberta',N'Canada','T2P 1J9'),
    (7,N'Montreal',N'Quebec',N'Canada','H3B 1A7'),
    (8,N'Hyderabad',N'Telangana',N'India','500001'),
    (9,N'Lisbon',N'Lisbon',N'Portugal','1000-001'),
    (10,N'Rio de Janeiro',N'Rio de Janeiro',N'Brazil','20000-000'),
    (11,N'Paris',N'Île-de-France',N'France','75001'),
    (12,N'Lagos',N'Lagos',N'Nigeria','100001'),
    (13,N'Tokyo',N'Tokyo',N'Japan','100-0001'),
    (14,N'Manila',N'Metro Manila',N'Philippines','1000'),
    (15,N'Mexico City',N'CDMX',N'Mexico','06000')
) x(Seq,City,ProvinceState,Country,PostalCode)
WHERE x.Seq = 1 + ((p.PatientID-1)%15);

-- Citizenship is usually related to country of birth but still includes
-- naturalized Canadian patients for useful WHERE/GROUP BY scenarios.
UPDATE p
SET Citizenship =
    CASE WHEN p.PatientID%5=0 THEN N'Canadian'
         ELSE CASE p.CountryOfBirth
            WHEN N'Canada' THEN N'Canadian'
            WHEN N'India' THEN N'Indian'
            WHEN N'Brazil' THEN N'Brazilian'
            WHEN N'Portugal' THEN N'Portuguese'
            WHEN N'Argentina' THEN N'Argentinian'
            WHEN N'France' THEN N'French'
            WHEN N'Nigeria' THEN N'Nigerian'
            WHEN N'China' THEN N'Chinese'
            WHEN N'Japan' THEN N'Japanese'
            WHEN N'South Korea' THEN N'South Korean'
            WHEN N'Egypt' THEN N'Egyptian'
            WHEN N'Pakistan' THEN N'Pakistani'
            WHEN N'Italy' THEN N'Italian'
            WHEN N'Germany' THEN N'German'
            WHEN N'Mexico' THEN N'Mexican'
            WHEN N'Philippines' THEN N'Filipino'
            WHEN N'United Kingdom' THEN N'British'
            WHEN N'Australia' THEN N'Australian'
            WHEN N'Kenya' THEN N'Kenyan'
            WHEN N'United States' THEN N'American'
            ELSE N'Other'
         END
    END
FROM dbo.Patient p;

-- Insurance enrichment
UPDATE dbo.InsurancePolicy
SET PayerCode = CONCAT('PAY',RIGHT('000'+CAST(1+((InsurancePolicyID-1)%8) AS varchar(3)),3)),
    GroupNumber = CONCAT('GRP',RIGHT('000000'+CAST(100000+(InsurancePolicyID%900000) AS varchar(6)),6)),
    RelationshipToSubscriber = CHOOSE(1+((InsurancePolicyID-1)%4),'Self','Spouse','Child','Other'),
    CoverageType = CHOOSE(1+((InsurancePolicyID-1)%4),'Provincial','Employer','Private','Self Pay');

-- Encounter enrichment
UPDATE dbo.Encounter
SET AccountNumber = CONCAT('ACCT',RIGHT('0000000000'+CAST(EncounterID AS varchar(10)),10)),
    AdmissionType = CHOOSE(1+((EncounterID-1)%4),'Emergency','Urgent','Elective','Newborn'),
    ReasonForVisit = CHOOSE(1+((EncounterID-1)%10),
        N'Chest pain',N'Shortness of breath',N'Fever',N'Abdominal pain',N'Fall / injury',
        N'Follow-up visit',N'Rehabilitation therapy',N'Diabetes management',N'Neurologic symptoms',N'Routine assessment'),
    ChiefComplaint = CONCAT(N'Synthetic training complaint: ',
        CHOOSE(1+((EncounterID-1)%10),
        N'chest discomfort',N'difficulty breathing',N'fever and fatigue',N'abdominal discomfort',N'fall-related pain',
        N'follow-up evaluation',N'mobility rehabilitation',N'glucose management',N'headache / weakness',N'routine assessment'));

-- ADT / HL7 enrichment
UPDATE dbo.ADTEvent
SET HL7Version = '2.5.1',
    SourceSystem = CHOOSE(1+((ADTEventID-1)%3),'Epic','Oracle Health/Cerner','MEDITECH'),
    DestinationSystem = 'Mirth Connect',
    EventReasonCode = CASE TriggerEvent WHEN 'A01' THEN 'ADMISSION'
                                       WHEN 'A02' THEN 'TRANSFER'
                                       WHEN 'A03' THEN 'DISCHARGE'
                                       WHEN 'A08' THEN 'UPDATE'
                                       ELSE 'OTHER' END;

-- Clinical terminology enrichment
UPDATE dbo.Diagnosis
SET CodeSystem = 'ICD-10-CA',
    DiagnosisRank = CASE WHEN DiagnosisType='Primary' THEN 1 ELSE 2 END;

UPDATE dbo.Allergy
SET AllergenCode = CONCAT('ALG',RIGHT('000000'+CAST(AllergyID AS varchar(6)),6)),
    CodeSystem = 'LOCAL',
    VerificationStatus = CASE WHEN Allergen=N'No Known Drug Allergy' THEN 'Unconfirmed' ELSE 'Confirmed' END;

UPDATE dbo.MedicationOrder
SET MedicationCodeSystem = 'LOCAL-MED',
    PRNFlag = CASE WHEN Frequency LIKE '%PRN%' THEN 1 ELSE 0 END,
    StartDateTime = OrderDateTime,
    EndDateTime = CASE WHEN OrderStatus='COMPLETED' THEN DATEADD(day,7,OrderDateTime) END;

UPDATE dbo.LabOrder
SET PlacerOrderNumber = CONCAT('PLC-',RIGHT('0000000000'+CAST(LabOrderID AS varchar(10)),10)),
    FillerOrderNumber = CONCAT('FIL-',RIGHT('0000000000'+CAST(LabOrderID AS varchar(10)),10)),
    SpecimenType = CASE OrderCode WHEN 'CBC' THEN 'Whole Blood'
                                  WHEN 'BMP' THEN 'Serum'
                                  WHEN 'CMP' THEN 'Serum'
                                  WHEN 'TROP' THEN 'Plasma'
                                  WHEN 'A1C' THEN 'Whole Blood'
                                  WHEN 'TSH' THEN 'Serum'
                                  ELSE 'Unknown' END;

UPDATE dbo.LabResult
SET LOINCCode = CASE TestCode WHEN 'WBC' THEN '6690-2'
                              WHEN 'HGB' THEN '718-7'
                              WHEN 'NA' THEN '2951-2'
                              ELSE NULL END,
    ValueType = 'NM';

-- Interface operations enrichment
UPDATE dbo.InterfaceMessage
SET TriggerEvent = CASE WHEN CHARINDEX('^',MessageType)>0
                        THEN SUBSTRING(MessageType,CHARINDEX('^',MessageType)+1,10)
                        ELSE NULL END,
    HL7Version = '2.5.1',
    Direction = 'Outbound',
    TransportProtocol = CASE WHEN ChannelName LIKE '%REPORTING%' OR ChannelName LIKE '%SQL%' THEN 'JDBC' ELSE 'MLLP' END,
    ProcessingTimeMs = CASE WHEN ProcessedDateTime IS NULL THEN NULL
                            ELSE DATEDIFF_BIG(millisecond,ReceivedDateTime,ProcessedDateTime) END,
    AckStatus = CASE WHEN ProcessingStatus='ERROR' THEN 'NACK' ELSE 'ACK' END;

UPDATE dbo.InterfaceError
SET Severity = 'ERROR',
    SegmentName = CASE ErrorCode WHEN 'INVALID_MRN' THEN 'PID'
                                 WHEN 'MISSING_FIELD' THEN 'PV1'
                                 WHEN 'MLLP_RESET' THEN 'MSH'
                                 ELSE NULL END,
    FieldPosition = CASE ErrorCode WHEN 'INVALID_MRN' THEN 'PID-3'
                                   WHEN 'MISSING_FIELD' THEN 'PV1-2'
                                   WHEN 'MLLP_RESET' THEN 'MSH-10'
                                   ELSE NULL END;

UPDATE dbo.AuditLog
SET ApplicationName = CASE WHEN UserOrSystem='Mirth Connect' THEN 'Mirth Connect'
                           ELSE 'Registration Portal' END,
    WorkstationName = CASE WHEN UserOrSystem='Mirth Connect' THEN 'MIRTH-PRD-01'
                           ELSE CONCAT('REG-WS-',RIGHT('000'+CAST((AuditLogID%50)+1 AS varchar(3)),3)) END;
GO

CREATE INDEX IX_Patient_Name ON dbo.Patient(LastName,FirstName);
CREATE INDEX IX_Patient_DOB ON dbo.Patient(DateOfBirth);
CREATE INDEX IX_Patient_Country ON dbo.Patient(Country);
CREATE INDEX IX_Encounter_Patient_Admit ON dbo.Encounter(PatientID,AdmitDateTime DESC);
CREATE INDEX IX_Encounter_Status ON dbo.Encounter(EncounterStatus,PatientClass,ServiceCode);
CREATE INDEX IX_ADT_Patient_Event ON dbo.ADTEvent(PatientID,EventDateTime DESC);
CREATE INDEX IX_Diagnosis_Patient ON dbo.Diagnosis(PatientID,DiagnosisCode);
CREATE INDEX IX_Interface_Status ON dbo.InterfaceMessage(ProcessingStatus,ReceivedDateTime);
CREATE INDEX IX_Patient_CountryOfBirth ON dbo.Patient(CountryOfBirth);
CREATE INDEX IX_Patient_Citizenship ON dbo.Patient(Citizenship);
CREATE INDEX IX_Patient_AgeYears ON dbo.Patient(AgeYears);
CREATE INDEX IX_Encounter_VisitNumber ON dbo.Encounter(VisitNumber);
CREATE UNIQUE INDEX UX_Encounter_AccountNumber ON dbo.Encounter(AccountNumber) WHERE AccountNumber IS NOT NULL;
CREATE INDEX IX_ADT_MessageType ON dbo.ADTEvent(MessageType,EventDateTime DESC);
CREATE INDEX IX_LabOrder_Patient ON dbo.LabOrder(PatientID,OrderDateTime DESC);
CREATE INDEX IX_Interface_MessageType ON dbo.InterfaceMessage(MessageType,ReceivedDateTime DESC);
GO

/*
===============================================================================
TEACHING VIEWS
===============================================================================
*/

CREATE OR ALTER VIEW dbo.vw_PatientTeaching AS
SELECT
    PatientID,
    EnterpriseMRN,
    FacilityMRN,
    FirstName,
    MiddleName,
    LastName,
    FullName,
    DateOfBirth,
    AgeYears,
    AgeGroup,
    SexAtBirth,
    GenderIdentity,
    CountryOfBirth,
    Citizenship,
    Country,
    ProvinceState,
    City,
    PostalCode,
    PrimaryLanguage,
    InterpreterRequired,
    MobilePhone,
    EmailAddress,
    PatientStatus,
    RegistrationSource,
    LastSeenAt
FROM dbo.Patient;
GO

CREATE OR ALTER VIEW dbo.vw_PatientEncounterTeaching AS
SELECT
    p.PatientID,
    p.EnterpriseMRN,
    p.FullName,
    p.AgeYears,
    p.Country,
    e.EncounterID,
    e.VisitNumber,
    e.AccountNumber,
    e.PatientClass,
    e.EncounterType,
    e.EncounterStatus,
    e.ServiceCode,
    e.ReasonForVisit,
    e.AdmitDateTime,
    e.DischargeDateTime,
    f.FacilityName,
    d.DepartmentName,
    CONCAT(pr.FirstName,' ',pr.LastName) AS AttendingProvider
FROM dbo.Patient p
JOIN dbo.Encounter e ON e.PatientID=p.PatientID
JOIN dbo.Facility f ON f.FacilityID=e.FacilityID
JOIN dbo.Department d ON d.DepartmentID=e.DepartmentID
JOIN dbo.Provider pr ON pr.ProviderID=e.AttendingProviderID;
GO

CREATE OR ALTER VIEW dbo.vw_HL7InterfaceTeaching AS
SELECT
    im.InterfaceMessageID,
    im.MessageControlID,
    im.ChannelName,
    im.MessageType,
    im.TriggerEvent,
    im.HL7Version,
    im.SourceSystem,
    im.DestinationSystem,
    im.Direction,
    im.TransportProtocol,
    im.ReceivedDateTime,
    im.ProcessingTimeMs,
    im.ProcessingStatus,
    im.AckStatus,
    p.PatientID,
    p.EnterpriseMRN,
    p.FullName,
    e.VisitNumber
FROM dbo.InterfaceMessage im
LEFT JOIN dbo.Patient p ON p.PatientID=im.PatientID
LEFT JOIN dbo.Encounter e ON e.EncounterID=im.EncounterID;
GO

CREATE OR ALTER VIEW dbo.vw_CurrentCensus AS
SELECT e.EncounterID,e.VisitNumber,e.AccountNumber,p.PatientID,p.EnterpriseMRN,p.FullName,p.FirstName,p.LastName,p.DateOfBirth,p.AgeYears,p.AgeGroup,p.GenderIdentity,p.Country,
 e.PatientClass,e.ServiceCode,e.AssignedUnit,e.AssignedRoom,e.AssignedBed,e.AdmitDateTime,f.FacilityName,d.DepartmentName,
 CONCAT(pr.FirstName,' ',pr.LastName) AttendingProvider,e.FinancialClass
FROM dbo.Encounter e
JOIN dbo.Patient p ON p.PatientID=e.PatientID
JOIN dbo.Facility f ON f.FacilityID=e.FacilityID
JOIN dbo.Department d ON d.DepartmentID=e.DepartmentID
JOIN dbo.Provider pr ON pr.ProviderID=e.AttendingProviderID
WHERE e.EncounterStatus='Active' AND e.DischargeDateTime IS NULL;
GO

CREATE OR ALTER VIEW dbo.vw_InterfaceRetryQueue AS
SELECT im.InterfaceMessageID,im.MessageControlID,im.ChannelName,im.MessageType,im.SourceSystem,im.DestinationSystem,
 im.ReceivedDateTime,im.RetryCount,ie.ErrorCode,ie.ErrorCategory,ie.ErrorMessage,ie.ResolvedFlag
FROM dbo.InterfaceMessage im JOIN dbo.InterfaceError ie ON ie.InterfaceMessageID=im.InterfaceMessageID
WHERE im.ProcessingStatus='ERROR' AND ie.ResolvedFlag=0;
GO

CREATE OR ALTER PROCEDURE dbo.usp_FindPatient @Search nvarchar(100) AS
BEGIN
 SET NOCOUNT ON;
 SELECT TOP(100)
    PatientID,EnterpriseMRN,FacilityMRN,FirstName,MiddleName,LastName,FullName,
    DateOfBirth,AgeYears,GenderIdentity,CountryOfBirth,Citizenship,Country,City,
    PrimaryLanguage,MobilePhone,EmailAddress,PatientStatus
 FROM dbo.Patient
 WHERE EnterpriseMRN=@Search
    OR FacilityMRN=@Search
    OR FirstName LIKE '%'+@Search+'%'
    OR LastName LIKE '%'+@Search+'%'
    OR FullName LIKE '%'+@Search+'%'
    OR Country LIKE '%'+@Search+'%'
    OR CountryOfBirth LIKE '%'+@Search+'%'
 ORDER BY LastName,FirstName;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_CurrentCensus @ServiceCode varchar(20)=NULL AS
BEGIN
 SET NOCOUNT ON;
 SELECT * FROM dbo.vw_CurrentCensus WHERE @ServiceCode IS NULL OR ServiceCode=@ServiceCode
 ORDER BY AssignedUnit,AssignedRoom,AssignedBed;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_AdmitPatient @PatientID int,@ServiceCode varchar(20),@PatientClass varchar(10)='I' AS
BEGIN
 SET NOCOUNT ON; SET XACT_ABORT ON;
 BEGIN TRY
  BEGIN TRANSACTION;
  IF NOT EXISTS(SELECT 1 FROM dbo.Patient WHERE PatientID=@PatientID AND DeceasedFlag=0)
   THROW 51001,'Patient does not exist or is deceased.',1;
  IF EXISTS(SELECT 1 FROM dbo.Encounter WHERE PatientID=@PatientID AND EncounterStatus='Active' AND DischargeDateTime IS NULL)
   THROW 51002,'Patient already has an active encounter.',1;
  DECLARE @VisitNumber varchar(25)=CONCAT('LIVE',FORMAT(SYSDATETIME(),'yyyyMMddHHmmss'),RIGHT('0000'+CAST(@PatientID AS varchar(4)),4));
  DECLARE @EncounterID bigint;
  INSERT dbo.Encounter(VisitNumber,PatientID,FacilityID,DepartmentID,LocationID,PatientClass,EncounterType,EncounterStatus,ServiceCode,
   AdmitDateTime,DischargeDateTime,AttendingProviderID,AdmittingProviderID,ReferringProviderID,AdmissionSource,DischargeDisposition,
   AssignedUnit,AssignedRoom,AssignedBed,PriorUnit,PriorRoom,PriorBed,FinancialClass,CreatedAt,UpdatedAt)
  VALUES(@VisitNumber,@PatientID,1,8,1,@PatientClass,CASE WHEN @PatientClass='I' THEN 'Inpatient' ELSE 'Outpatient' END,'Active',@ServiceCode,
   SYSDATETIME(),NULL,1,2,3,'Interview Practice',NULL,'U08','R101','B1',NULL,NULL,NULL,'PROV',SYSDATETIME(),SYSDATETIME());
  SET @EncounterID=SCOPE_IDENTITY();
  INSERT dbo.ADTEvent(EncounterID,PatientID,TriggerEvent,MessageType,EventDateTime,ToUnit,ToRoom,ToBed,MessageControlID,ProcessingStatus)
  VALUES(@EncounterID,@PatientID,'A01','ADT^A01',SYSDATETIME(),'U08','R101','B1',CONCAT('LIVE-A01-',@EncounterID),'READY');
  COMMIT TRANSACTION;
  SELECT @EncounterID EncounterID,@VisitNumber VisitNumber,'ADMITTED' Status;
 END TRY
 BEGIN CATCH
  IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
  THROW;
 END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_DischargeEncounter @EncounterID bigint,@Disposition varchar(60)='Home' AS
BEGIN
 SET NOCOUNT ON; SET XACT_ABORT ON;
 BEGIN TRY
  BEGIN TRANSACTION;
  DECLARE @PatientID int,@Unit varchar(20),@Room varchar(20),@Bed varchar(20);
  SELECT @PatientID=PatientID,@Unit=AssignedUnit,@Room=AssignedRoom,@Bed=AssignedBed FROM dbo.Encounter
  WHERE EncounterID=@EncounterID AND EncounterStatus='Active' AND DischargeDateTime IS NULL;
  IF @PatientID IS NULL THROW 51003,'Active encounter not found.',1;
  UPDATE dbo.Encounter SET EncounterStatus='Discharged',DischargeDateTime=SYSDATETIME(),DischargeDisposition=@Disposition,UpdatedAt=SYSDATETIME()
  WHERE EncounterID=@EncounterID;
  INSERT dbo.ADTEvent(EncounterID,PatientID,TriggerEvent,MessageType,EventDateTime,FromUnit,FromRoom,FromBed,MessageControlID,ProcessingStatus)
  VALUES(@EncounterID,@PatientID,'A03','ADT^A03',SYSDATETIME(),@Unit,@Room,@Bed,CONCAT('LIVE-A03-',@EncounterID),'READY');
  COMMIT TRANSACTION;
  SELECT @EncounterID EncounterID,'DISCHARGED' Status;
 END TRY
 BEGIN CATCH
  IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
  THROW;
 END CATCH
END;
GO

PRINT 'HealtchareDatabse BUILD COMPLETE';
PRINT 'All data is synthetic training data only - NO REAL PHI. Patient names are fictionalized and do not represent real people.';

SELECT DB_NAME() AS CurrentDatabase;
SELECT COUNT(*) AS PatientCount FROM dbo.Patient;
SELECT COUNT(*) AS ActiveCensus FROM dbo.vw_CurrentCensus;
SELECT COUNT(*) AS EncounterCount FROM dbo.Encounter;
SELECT COUNT(*) AS ADTEventCount FROM dbo.ADTEvent;
SELECT COUNT(*) AS DiagnosisCount FROM dbo.Diagnosis;
SELECT COUNT(*) AS LabResultCount FROM dbo.LabResult;
SELECT COUNT(*) AS InterfaceErrorCount FROM dbo.InterfaceError;

-- Beginner-friendly verification queries
SELECT TOP(10)
    PatientID,EnterpriseMRN,FacilityMRN,FirstName,LastName,FullName,
    DateOfBirth,AgeYears,CountryOfBirth,Citizenship,Country,City
FROM dbo.Patient
ORDER BY PatientID;

SELECT Country,COUNT(*) AS Patients
FROM dbo.Patient
GROUP BY Country
ORDER BY Patients DESC;

SELECT AgeGroup,COUNT(*) AS Patients
FROM dbo.Patient
GROUP BY AgeGroup
ORDER BY Patients DESC;

SELECT TOP(20) *
FROM dbo.vw_HL7InterfaceTeaching
ORDER BY ReceivedDateTime DESC;

-- Requested training-data verification
SELECT
    COUNT(*) AS TotalPatients,
    COUNT(DISTINCT FirstName) AS UniqueFirstNames,
    COUNT(DISTINCT FullName) AS UniqueFullNames
FROM dbo.Patient;

SELECT
    MIN(DateOfBirth) AS EarliestDOB,
    MAX(DateOfBirth) AS LatestDOB,
    MIN(YEAR(DateOfBirth)) AS EarliestBirthYear,
    MAX(YEAR(DateOfBirth)) AS LatestBirthYear,
    COUNT(DISTINCT YEAR(DateOfBirth)) AS DistinctBirthYears
FROM dbo.Patient;

SELECT
    MIN(AdmitDateTime) AS EarliestAdmission,
    MAX(AdmitDateTime) AS LatestAdmission,
    MIN(YEAR(AdmitDateTime)) AS EarliestAdmissionYear,
    MAX(YEAR(AdmitDateTime)) AS LatestAdmissionYear,
    COUNT(DISTINCT YEAR(AdmitDateTime)) AS DistinctAdmissionYears
FROM dbo.Encounter;
GO