-- Example SQL (illustrative)

-- 1) Seed roles
INSERT INTO roles (name, slug, created_at, updated_at) VALUES
('Admin','admin',NOW(),NOW()),
('Doctor','doctor',NOW(),NOW()),
('Nurse','nurse',NOW(),NOW()),
('Patient','patient',NOW(),NOW())
ON DUPLICATE KEY UPDATE name=VALUES(name), updated_at=NOW();

-- 2) Create a department
INSERT INTO departments (name, description, is_active, created_at, updated_at)
VALUES ('Cardiology', 'Heart and vascular medicine', 1, NOW(), NOW());

-- 3) Create a doctor user + doctor profile
INSERT INTO users (name, email, phone, password, is_active, created_at, updated_at)
VALUES ('Dr. Alice', 'alice.doctor@example.com', '+994501112233', '$2y$...', 1, NOW(), NOW());

INSERT INTO doctors (user_id, department_id, license_no, specialization, phone, default_duration_minutes, consultation_fee, is_active, created_at, updated_at)
VALUES (LAST_INSERT_ID(), 1, 'AZ-LIC-001', 'Cardiologist', '+994501112233', 30, 50.00, 1, NOW(), NOW());

-- 4) Create weekly schedule for doctor (Mon 09:00-17:00)
INSERT INTO doctor_schedules (doctor_id, day_of_week, start_time, end_time, slot_minutes, created_at, updated_at)
VALUES (1, 1, '09:00', '17:00', 30, NOW(), NOW());

-- 5) Create a patient user + patient profile
INSERT INTO users (name, email, phone, password, is_active, created_at, updated_at)
VALUES ('John Patient', 'john.patient@example.com', '+994501234567', '$2y$...', 1, NOW(), NOW());

INSERT INTO patients (user_id, mrn, date_of_birth, gender, blood_type, phone, address, created_at, updated_at)
VALUES (LAST_INSERT_ID(), 'MRN-2026-ABC123', '1990-01-01', 'male', 'O+', '+994501234567', 'Baku', NOW(), NOW());

-- 6) Book an appointment (must pass overlap + schedule checks at API layer)
INSERT INTO appointments (patient_id, doctor_id, department_id, scheduled_at, ends_at, status, reason, created_at, updated_at)
VALUES (1, 1, 1, '2026-04-20 10:00:00', '2026-04-20 10:30:00', 'scheduled', 'Checkup', NOW(), NOW());

-- 7) Add an ICD code and diagnose
INSERT INTO icd_codes (code, title, description, is_active, created_at, updated_at)
VALUES ('I10', 'Essential (primary) hypertension', NULL, 1, NOW(), NOW());

INSERT INTO diagnoses (appointment_id, patient_id, doctor_id, icd_code_id, severity, notes, diagnosed_at, created_at, updated_at)
VALUES (1, 1, 1, 1, 'moderate', 'BP elevated', NOW(), NOW(), NOW());

-- 8) Create prescription + items
INSERT INTO prescriptions (patient_id, doctor_id, appointment_id, diagnosis_id, issued_at, status, created_at, updated_at)
VALUES (1, 1, 1, 1, NOW(), 'active', NOW(), NOW());

INSERT INTO prescription_items (prescription_id, medication_name, dosage, frequency, duration_days, created_at, updated_at)
VALUES (LAST_INSERT_ID(), 'Amlodipine', '5mg', 'once daily', 30, NOW(), NOW());

-- 9) Generate invoice and record payment
INSERT INTO invoices (patient_id, appointment_id, status, subtotal, tax, discount, total, currency, issued_at, due_at, created_at, updated_at)
VALUES (1, 1, 'issued', 50.00, 0, 0, 50.00, 'USD', NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY), NOW(), NOW());

INSERT INTO payments (invoice_id, patient_id, amount, method, status, paid_at, created_at, updated_at)
VALUES (1, 1, 50.00, 'cash', 'paid', NOW(), NOW(), NOW());

-- 10) Patient history: upcoming appointments
SELECT a.*
FROM appointments a
WHERE a.patient_id = 1 AND a.scheduled_at >= NOW()
ORDER BY a.scheduled_at ASC;