-- MSSQL PHI Seed Script
-- Run against target MSSQL instance to populate test data

-- Create database if not exists
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'phi_test')
BEGIN
    CREATE DATABASE phi_test;
END
GO

USE phi_test;
GO

-- Drop tables if they exist for clean re-seeding
IF OBJECT_ID('dbo.BreachLog', 'U') IS NOT NULL DROP TABLE dbo.BreachLog;
IF OBJECT_ID('dbo.Claims', 'U') IS NOT NULL DROP TABLE dbo.Claims;
IF OBJECT_ID('dbo.Patients', 'U') IS NOT NULL DROP TABLE dbo.Patients;
GO

CREATE TABLE dbo.Patients (
    PatientID INT PRIMARY KEY,
    FullName NVARCHAR(100),
    SSN VARCHAR(11),
    DateOfBirth DATE,
    MedicalRecordNumber VARCHAR(20),
    Phone VARCHAR(14),
    Email VARCHAR(100),
    Address VARCHAR(200),
    InsurancePolicyNumber VARCHAR(30),
    Diagnosis NVARCHAR(200)
);

INSERT INTO dbo.Patients VALUES
(10001, 'John M. Whitfield', '573-44-9281', '1983-07-15', 'MRN-77441', '(414) 555-0174', 'jwhitfield@email.com', '4421 Elm Street Milwaukee WI 53202', 'Aetna-A992881', 'Type 2 Diabetes'),
(10002, 'Dr. Sarah K. Chen', '819-77-3341', '1971-03-22', 'MRN-33018', '(312) 555-9912', 'schen@midwestmed.org', '1010 Lake Shore Dr Chicago IL 60611', 'Cigna-448172', 'Hypertension'),
(10003, 'James O. O''Brien', '819-77-3341', '1955-11-08', 'MRN-66109', '(513) 555-2288', 'jobrien@gmail.com', '892 Vine Street Cincinnati OH 45202', 'UnitedHealth-77223', 'Chronic Back Pain'),
(10004, 'Maria Elena Rodriguez', '622-11-0099', '1990-04-30', 'MRN-91503', '(214) 555-6610', 'mrodriguez@texashealth.net', '7715 Oak Park Ave Dallas TX 75235', 'BCBS-338190', 'Anxiety Disorder'),
(10005, 'Robert D. Washington', '441-28-7763', '1967-09-14', 'MRN-55221', '(313) 555-0044', 'rwashington@email.com', '2034 Michigan Ave Detroit MI 48226', 'Medicaid-119204', 'COPD'),
(10006, 'Lisa Ann Nakamura', '441-28-7763', '1988-01-03', 'MRN-88712', '(206) 555-3390', 'lnakamura@uwnmed.org', '556 Pine Rd Seattle WA 98101', 'Premera-661733', 'Asthma'),
(10007, 'Michael T. O''Sullivan', '733-90-1122', '1979-06-19', 'MRN-22909', '(503) 555-8711', 'msullivan@hrcare.org', '188 Crescent Bay Dr Portland OR 97201', 'Regence-442180', 'Arthritis'),
(10008, 'Angela R. Petit', '662-41-0039', '1962-12-25', 'MRN-77660', '(615) 555-2200', 'apetit@nhsmail.org', '9900 Broadway Nashville TN 37203', 'Humana-993371', 'Breast Cancer Stage IIA');
GO

CREATE TABLE dbo.Claims (
    ClaimID VARCHAR(20) PRIMARY KEY,
    PatientID INT,
    ServiceDate DATE,
    BilledAmount DECIMAL(10,2),
    DiagnosisCode VARCHAR(10),
    ProviderNPI VARCHAR(10),
    PolicyNumber VARCHAR(30),
    SSN VARCHAR(11),
    FK_PatientID INT FOREIGN KEY REFERENCES dbo.Patients(PatientID)
);

INSERT INTO dbo.Claims VALUES
('CLM-2026-001881', 10001, '2026-01-15', 2840.00, 'E11.9', '1154998722', 'AET-772-441-0091', '573-44-9281'),
('CLM-2026-001882', 10002, '2026-01-22', 550.00, 'M54.5', '1154998722', 'UHG-992-448-1177', '819-77-3341'),
('CLM-2026-001883', 10003, '2026-02-03', 1275.00, 'F41.1', '1154998722', 'BCBS-TX-772-441-9914', '622-11-0099'),
('CLM-2026-001884', 10004, '2026-02-18', 320.00, 'J45.20', '1154998722', 'PREM-WS-992-441-8817', '441-28-7763');
GO

-- HIPAA Breach Log
CREATE TABLE dbo.BreachLog (
    LogID INT PRIMARY KEY IDENTITY(1,1),
    BreachDate DATETIME,
    PatientID INT,
    Description NVARCHAR(500),
    SSN VARCHAR(11),
    ReportedTo VARCHAR(50)
);

INSERT INTO dbo.BreachLog VALUES
('2026-03-01 14:22:00', 10003, 'Unauthorized access to patient record by staff member', '819-77-3341', 'HHS OCR'),
('2026-03-15 09:45:00', 10001, 'Email containing unencrypted PHI sent to wrong recipient', '573-44-9281', 'HHS OCR');
GO