-- ============================================================
-- HIS SEED DATA
-- Run AFTER schema.sql
-- Default admin login: admin / Admin@2024
-- CHANGE THE PASSWORD IMMEDIATELY AFTER FIRST LOGIN.
-- ============================================================

USE his_db;

-- Default departments
INSERT INTO departments (dept_name, dept_description) VALUES
('General Medicine', 'General outpatient and inpatient care'),
('Surgery', 'Surgical procedures and theatre'),
('Laboratory', 'Diagnostic lab services'),
('Pharmacy', 'Drug dispensing and stock'),
('Radiology', 'Imaging services'),
('Maternity', 'Obstetrics and gynaecology'),
('Pediatrics', 'Children ward and outpatient'),
('Administration', 'Admin and finance staff');

-- Default admin user
-- Password: Admin@2024 (bcrypt hash generated via PHP password_hash)
INSERT INTO staff (
    staff_number, role_id, dept_id, first_name, last_name, email, phone,
    username, password_hash, gender, status, must_change_password
) VALUES (
    'EMP-0001',
    (SELECT role_id FROM roles WHERE role_name = 'admin'),
    (SELECT dept_id FROM departments WHERE dept_name = 'Administration'),
    'System', 'Administrator',
    'admin@hospital.com',
    '254700000000',
    'admin',
    '$2b$12$/AlpwC/AEQwV.VrSwxKnJu27Lc/C2Y/dneqZR9WF10YAheWet6mPi', -- password: Admin@2024
    'Male',
    'active',
    1
);

-- NOTE: The bcrypt hash above is for 'Admin@2024'.
-- To generate a fresh hash, run in PHP: echo password_hash('Admin@2024', PASSWORD_DEFAULT);

-- Service price catalog (consultation & admission fees)
INSERT INTO service_catalog (service_name, service_type, price, is_active) VALUES
('Outpatient Consultation Fee', 'consultation', 500.00, 1),
('Emergency Consultation Fee', 'consultation', 1000.00, 1),
('Inpatient Admission Fee', 'admission', 2000.00, 1),
('Ward Round Fee', 'procedure', 500.00, 1),
('Dressing / Wound Care', 'procedure', 300.00, 1);

-- Lab test catalog
INSERT INTO lab_test_catalog (test_name, test_code, category, price, turnaround_time) VALUES
('Full Blood Count (FBC)', 'FBC', 'Hematology', 600.00, '2 hours'),
('Blood Sugar (Random)', 'RBS', 'Chemistry', 200.00, '30 minutes'),
('Fasting Blood Sugar', 'FBS', 'Chemistry', 200.00, '30 minutes'),
('Urea & Electrolytes', 'U&E', 'Chemistry', 800.00, '4 hours'),
('Liver Function Tests', 'LFT', 'Chemistry', 1000.00, '4 hours'),
('Urinalysis', 'UA', 'Microbiology', 300.00, '1 hour'),
('Malaria RDT', 'MAL-RDT', 'Microbiology', 250.00, '30 minutes'),
('HIV Test', 'HIV', 'Serology', 200.00, '30 minutes'),
('Pregnancy Test (Urine)', 'BHCG', 'Serology', 150.00, '30 minutes'),
('Widal Test', 'WIDAL', 'Microbiology', 400.00, '2 hours'),
('ESR', 'ESR', 'Hematology', 300.00, '2 hours'),
('Thyroid Function (TSH)', 'TSH', 'Endocrinology', 1500.00, '6 hours'),
('HbA1c', 'HBA1C', 'Chemistry', 1200.00, '4 hours'),
('Lipid Profile', 'LIPID', 'Chemistry', 1000.00, '4 hours');

-- Sample ward and beds
INSERT INTO wards (ward_name, ward_type, capacity) VALUES
('General Ward', 'General', 20),
('ICU', 'ICU', 5),
('Maternity Ward', 'Maternity', 10),
('Pediatric Ward', 'Pediatric', 10);

-- Beds for General Ward
INSERT INTO beds (ward_id, bed_number) VALUES
((SELECT ward_id FROM wards WHERE ward_name = 'General Ward'), 'G-01'),
((SELECT ward_id FROM wards WHERE ward_name = 'General Ward'), 'G-02'),
((SELECT ward_id FROM wards WHERE ward_name = 'General Ward'), 'G-03'),
((SELECT ward_id FROM wards WHERE ward_name = 'General Ward'), 'G-04'),
((SELECT ward_id FROM wards WHERE ward_name = 'General Ward'), 'G-05');

-- Beds for ICU
INSERT INTO beds (ward_id, bed_number) VALUES
((SELECT ward_id FROM wards WHERE ward_name = 'ICU'), 'ICU-01'),
((SELECT ward_id FROM wards WHERE ward_name = 'ICU'), 'ICU-02'),
((SELECT ward_id FROM wards WHERE ward_name = 'ICU'), 'ICU-03');

-- Beds for Maternity
INSERT INTO beds (ward_id, bed_number) VALUES
((SELECT ward_id FROM wards WHERE ward_name = 'Maternity Ward'), 'M-01'),
((SELECT ward_id FROM wards WHERE ward_name = 'Maternity Ward'), 'M-02'),
((SELECT ward_id FROM wards WHERE ward_name = 'Maternity Ward'), 'M-03');

-- Sample pharmaceutical categories
INSERT INTO pharm_categories (cat_name, cat_description) VALUES
('Antibiotics', 'Bacterial infection treatments'),
('Analgesics', 'Pain relief medications'),
('Antifungals', 'Fungal infection treatments'),
('Antivirals', 'Viral infection treatments'),
('Antidiabetics', 'Diabetes management drugs'),
('Antihypertensives', 'Blood pressure medications'),
('IV Fluids', 'Intravenous fluid preparations'),
('Vitamins & Supplements', 'Nutritional supplements');

-- Sample pharmaceuticals (initial stock for testing)
INSERT INTO pharmaceuticals (pharm_cat_id, drug_name, generic_name, unit, unit_price, reorder_level, quantity_in_stock) VALUES
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Antibiotics'), 'Amoxicillin 500mg Caps', 'Amoxicillin', 'capsule', 15.00, 100, 500),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Antibiotics'), 'Ciprofloxacin 500mg Tabs', 'Ciprofloxacin', 'tablet', 25.00, 50, 300),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Analgesics'), 'Paracetamol 500mg Tabs', 'Paracetamol', 'tablet', 5.00, 200, 1000),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Analgesics'), 'Ibuprofen 400mg Tabs', 'Ibuprofen', 'tablet', 10.00, 100, 500),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Antidiabetics'), 'Metformin 500mg Tabs', 'Metformin', 'tablet', 8.00, 100, 400),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Antihypertensives'), 'Amlodipine 5mg Tabs', 'Amlodipine', 'tablet', 12.00, 50, 300),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Antihypertensives'), 'Enalapril 10mg Tabs', 'Enalapril', 'tablet', 18.00, 50, 200),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='IV Fluids'), 'Normal Saline 500ml', 'Sodium Chloride 0.9%', 'bag', 150.00, 20, 100),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='IV Fluids'), 'Dextrose 5% 500ml', 'Dextrose 5%', 'bag', 160.00, 20, 80),
((SELECT pharm_cat_id FROM pharm_categories WHERE cat_name='Vitamins & Supplements'), 'Vitamin C 500mg Tabs', 'Ascorbic Acid', 'tablet', 5.00, 100, 600);

-- Initial stock ledger entries for the sample drugs (mark as purchase)
INSERT INTO pharm_stock_ledger (pharm_id, change_qty, reason, performed_by, balance_after)
SELECT pharm_id, quantity_in_stock, 'purchase', NULL, quantity_in_stock
FROM pharmaceuticals WHERE quantity_in_stock > 0;