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

-- Drop tables if they exist for clean re-seeding
DROP TABLE IF EXISTS "BreachLog" CASCADE;
DROP TABLE IF EXISTS "Claims" CASCADE;
DROP TABLE IF EXISTS "Patients" CASCADE;

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

INSERT INTO "Patients" ("PatientID", "FullName", "SSN", "DateOfBirth", "MedicalRecordNumber", "Phone", "Email", "Address", "InsurancePolicyNumber", "Diagnosis") 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');

CREATE TABLE "Claims" (
    "ClaimID" VARCHAR(20) PRIMARY KEY,
    "PatientID" INTEGER REFERENCES "Patients"("PatientID"),
    "ServiceDate" DATE,
    "BilledAmount" DECIMAL(10,2),
    "DiagnosisCode" VARCHAR(10),
    "ProviderNPI" VARCHAR(10),
    "PolicyNumber" VARCHAR(30),
    "SSN" VARCHAR(11)
);

INSERT INTO "Claims" ("ClaimID", "PatientID", "ServiceDate", "BilledAmount", "DiagnosisCode", "ProviderNPI", "PolicyNumber", "SSN") 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');

-- HIPAA Breach Log
CREATE TABLE "BreachLog" (
    "LogID" SERIAL PRIMARY KEY,
    "BreachDate" TIMESTAMP,
    "PatientID" INTEGER REFERENCES "Patients"("PatientID"),
    "Description" VARCHAR(500),
    "SSN" VARCHAR(11),
    "ReportedTo" VARCHAR(50)
);

INSERT INTO "BreachLog" ("BreachDate", "PatientID", "Description", "SSN", "ReportedTo") 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');