-- ========================================================
-- HEALTHCARE MANAGEMENT SYSTEM - DATABASE DUMP
-- PostgreSQL Compatible / cPanel Ready
-- Generated: 2026-05-07
-- ========================================================

-- Create Database (if not exists)
-- CREATE DATABASE healthcare_db WITH ENCODING = 'UTF8';
-- \c healthcare_db;

-- ========================================================
-- 1. USERS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'staff' CHECK (role IN ('super_admin', 'hr_admin', 'medical_director', 'doctor', 'nurse', 'receptionist', 'accountant', 'staff', 'patient')),
    phone VARCHAR(20) NULL,
    address TEXT NULL,
    avatar VARCHAR(255) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended', 'terminated')),
    two_factor_secret VARCHAR(255) NULL,
    two_factor_recovery_codes TEXT NULL,
    two_factor_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    last_login_at TIMESTAMP NULL,
    last_login_ip VARCHAR(45) NULL,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_email ON users(email);

-- ========================================================
-- 2. DEPARTMENTS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT NULL,
    head_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    location VARCHAR(255) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(255) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

CREATE INDEX idx_departments_code ON departments(code);
CREATE INDEX idx_departments_status ON departments(status);

-- ========================================================
-- 3. STAFF TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS staff (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    staff_id VARCHAR(50) NOT NULL UNIQUE,
    department_id INTEGER NULL REFERENCES departments(id) ON DELETE SET NULL,
    designation VARCHAR(255) NOT NULL,
    employment_type VARCHAR(50) NOT NULL DEFAULT 'full_time' CHECK (employment_type IN ('full_time', 'part_time', 'contract', 'intern')),
    joining_date DATE NOT NULL,
    resignation_date DATE NULL,
    salary DECIMAL(12,2) NULL,
    bank_details JSONB NULL,
    emergency_contact JSONB NULL,
    documents JSONB NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'on_leave', 'suspended', 'terminated', 'retired')),
    verification_pin VARCHAR(6) NULL,
    pin_generated_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

CREATE INDEX idx_staff_staff_id ON staff(staff_id);
CREATE INDEX idx_staff_department_id ON staff(department_id);
CREATE INDEX idx_staff_status ON staff(status);
CREATE INDEX idx_staff_employment_type ON staff(employment_type);

-- ========================================================
-- 4. DOCTORS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS doctors (
    id SERIAL PRIMARY KEY,
    staff_id INTEGER NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
    specialization VARCHAR(255) NOT NULL,
    qualification TEXT NULL,
    experience_years INTEGER NOT NULL DEFAULT 0,
    license_number VARCHAR(255) NOT NULL UNIQUE,
    license_expiry DATE NULL,
    consultation_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
    availability_schedule JSONB NULL,
    bio TEXT NULL,
    rating DECIMAL(2,1) NOT NULL DEFAULT 0,
    total_reviews INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(50) NOT NULL DEFAULT 'available' CHECK (status IN ('available', 'unavailable', 'on_leave')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_doctors_specialization ON doctors(specialization);
CREATE INDEX idx_doctors_status ON doctors(status);
CREATE INDEX idx_doctors_license_number ON doctors(license_number);

-- ========================================================
-- 5. PATIENTS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS patients (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    date_of_birth DATE NULL,
    gender VARCHAR(20) NULL CHECK (gender IN ('male', 'female', 'other')),
    blood_group VARCHAR(10) NULL,
    medical_history TEXT NULL,
    allergies TEXT NULL,
    emergency_contact JSONB NULL,
    insurance_info JSONB NULL,
    patient_id VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_patients_patient_id ON patients(patient_id);
CREATE INDEX idx_patients_blood_group ON patients(blood_group);

-- ========================================================
-- 6. APPOINTMENTS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS appointments (
    id SERIAL PRIMARY KEY,
    patient_id INTEGER NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
    doctor_id INTEGER NOT NULL REFERENCES doctors(id) ON DELETE CASCADE,
    department_id INTEGER NULL REFERENCES departments(id) ON DELETE SET NULL,
    appointment_date TIMESTAMP NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'completed', 'cancelled', 'no_show')),
    type VARCHAR(50) NOT NULL DEFAULT 'consultation' CHECK (type IN ('consultation', 'follow_up', 'emergency', 'routine_checkup')),
    symptoms TEXT NULL,
    notes TEXT NULL,
    diagnosis TEXT NULL,
    prescription TEXT NULL,
    created_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    approved_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_appointments_date ON appointments(appointment_date);
CREATE INDEX idx_appointments_status ON appointments(status);
CREATE INDEX idx_appointments_patient_id ON appointments(patient_id);
CREATE INDEX idx_appointments_doctor_id ON appointments(doctor_id);

-- ========================================================
-- 7. LEAVES TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS leaves (
    id SERIAL PRIMARY KEY,
    staff_id INTEGER NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL CHECK (type IN ('annual', 'sick', 'maternity', 'paternity', 'bereavement', 'unpaid', 'compassionate', 'study')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    days_requested INTEGER NOT NULL,
    reason TEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'cancelled')),
    approved_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    approved_at TIMESTAMP NULL,
    rejection_reason TEXT NULL,
    documents JSONB NULL,
    is_retirement BOOLEAN NOT NULL DEFAULT FALSE,
    retirement_effective_date DATE NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_leaves_staff_id ON leaves(staff_id);
CREATE INDEX idx_leaves_status ON leaves(status);
CREATE INDEX idx_leaves_type ON leaves(type);
CREATE INDEX idx_leaves_dates ON leaves(start_date, end_date);

-- ========================================================
-- 8. ATTENDANCES TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS attendances (
    id SERIAL PRIMARY KEY,
    staff_id INTEGER NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    check_in TIME NULL,
    check_out TIME NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'present' CHECK (status IN ('present', 'absent', 'late', 'half_day', 'on_leave', 'holiday')),
    check_in_type VARCHAR(50) NOT NULL DEFAULT 'manual' CHECK (check_in_type IN ('manual', 'qr_code', 'gps', 'biometric', 'web')),
    check_out_type VARCHAR(50) NULL CHECK (check_out_type IN ('manual', 'qr_code', 'gps', 'biometric', 'web')),
    check_in_location JSONB NULL,
    check_out_location JSONB NULL,
    qr_code VARCHAR(255) NULL,
    notes TEXT NULL,
    work_hours DECIMAL(4,2) NOT NULL DEFAULT 0,
    approved_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(staff_id, date)
);

CREATE INDEX idx_attendances_date ON attendances(date);
CREATE INDEX idx_attendances_status ON attendances(status);

-- ========================================================
-- 9. SHIFTS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS shifts (
    id SERIAL PRIMARY KEY,
    staff_id INTEGER NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
    shift_name VARCHAR(255) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    days JSONB NOT NULL,
    department_id INTEGER NULL REFERENCES departments(id) ON DELETE SET NULL,
    effective_from DATE NOT NULL,
    effective_to DATE NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_shifts_staff_id ON shifts(staff_id);
CREATE INDEX idx_shifts_department_id ON shifts(department_id);
CREATE INDEX idx_shifts_status ON shifts(status);

-- ========================================================
-- 10. CERTIFICATIONS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS certifications (
    id SERIAL PRIMARY KEY,
    staff_id INTEGER NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    issuing_organization VARCHAR(255) NOT NULL,
    credential_id VARCHAR(255) NULL,
    issue_date DATE NOT NULL,
    expiry_date DATE NULL,
    document_url VARCHAR(255) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'expired', 'revoked')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_certifications_staff_id ON certifications(staff_id);
CREATE INDEX idx_certifications_status ON certifications(status);
CREATE INDEX idx_certifications_expiry ON certifications(expiry_date);

-- ========================================================
-- 11. AUDIT LOGS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(255) NOT NULL,
    entity_id BIGINT NULL,
    old_values JSONB NULL,
    new_values JSONB NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    url VARCHAR(255) NULL,
    method VARCHAR(10) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at);

-- ========================================================
-- 12. NOTIFICATIONS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL CHECK (type IN ('appointment', 'leave', 'attendance', 'system', 'announcement', 'reminder')),
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    data JSONB NULL,
    channel VARCHAR(50) NOT NULL DEFAULT 'in_app' CHECK (channel IN ('email', 'sms', 'push', 'in_app')),
    read_at TIMESTAMP NULL,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_type ON notifications(type);
CREATE INDEX idx_notifications_read ON notifications(read_at);
CREATE INDEX idx_notifications_created ON notifications(created_at);

-- ========================================================
-- 13. BLOG POSTS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS blog_posts (
    id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    excerpt TEXT NULL,
    content TEXT NOT NULL,
    featured_image VARCHAR(255) NULL,
    tags JSONB NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMP NULL,
    views INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_blog_posts_slug ON blog_posts(slug);
CREATE INDEX idx_blog_posts_status ON blog_posts(status);
CREATE INDEX idx_blog_posts_published ON blog_posts(published_at);

-- ========================================================
-- 14. CAREERS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS careers (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    department_id INTEGER NULL REFERENCES departments(id) ON DELETE SET NULL,
    description TEXT NOT NULL,
    requirements TEXT NOT NULL,
    employment_type VARCHAR(50) NOT NULL CHECK (employment_type IN ('full_time', 'part_time', 'contract', 'intern')),
    salary_min DECIMAL(12,2) NULL,
    salary_max DECIMAL(12,2) NULL,
    location VARCHAR(255) NULL,
    deadline DATE NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed', 'on_hold')),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_careers_department_id ON careers(department_id);
CREATE INDEX idx_careers_status ON careers(status);
CREATE INDEX idx_careers_deadline ON careers(deadline);

-- ========================================================
-- 15. CONTACTS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS contacts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'read', 'replied', 'archived')),
    reply TEXT NULL,
    replied_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    replied_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_contacts_status ON contacts(status);
CREATE INDEX idx_contacts_created ON contacts(created_at);

-- ========================================================
-- 16. SETTINGS TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS settings (
    id SERIAL PRIMARY KEY,
    key VARCHAR(255) NOT NULL UNIQUE,
    value TEXT NULL,
    group_name VARCHAR(50) NOT NULL DEFAULT 'general',
    type VARCHAR(50) NOT NULL DEFAULT 'string' CHECK (type IN ('string', 'integer', 'boolean', 'json')),
    description TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_settings_key ON settings(key);
CREATE INDEX idx_settings_group ON settings(group_name);

-- ========================================================
-- 17. LEAVE BALANCES TABLE
-- ========================================================
CREATE TABLE IF NOT EXISTS leave_balances (
    id SERIAL PRIMARY KEY,
    staff_id INTEGER NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
    year INTEGER NOT NULL,
    type VARCHAR(50) NOT NULL CHECK (type IN ('annual', 'sick', 'maternity', 'paternity', 'bereavement', 'study')),
    total_days INTEGER NOT NULL DEFAULT 0,
    used_days INTEGER NOT NULL DEFAULT 0,
    remaining_days INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(staff_id, year, type)
);

CREATE INDEX idx_leave_balances_staff_id ON leave_balances(staff_id);
CREATE INDEX idx_leave_balances_year ON leave_balances(year);

-- ========================================================
-- SEED DATA - DEPARTMENTS
-- ========================================================
INSERT INTO departments (name, code, description, location, phone, email, status) VALUES
('Emergency Medicine', 'EMRG', '24/7 emergency care and trauma services', 'Building A, Ground Floor', '+1-555-0101', 'emergency@hospital.com', 'active'),
('Cardiology', 'CARD', 'Heart and cardiovascular care', 'Building B, 2nd Floor', '+1-555-0102', 'cardiology@hospital.com', 'active'),
('Pediatrics', 'PEDS', 'Children and adolescent healthcare', 'Building C, 1st Floor', '+1-555-0103', 'pediatrics@hospital.com', 'active'),
('Orthopedics', 'ORTH', 'Bone, joint, and muscle care', 'Building B, 3rd Floor', '+1-555-0104', 'orthopedics@hospital.com', 'active'),
('Neurology', 'NEUR', 'Brain and nervous system care', 'Building D, 2nd Floor', '+1-555-0105', 'neurology@hospital.com', 'active'),
('Oncology', 'ONCO', 'Cancer treatment and research', 'Building E, 1st Floor', '+1-555-0106', 'oncology@hospital.com', 'active'),
('Radiology', 'RADS', 'Medical imaging and diagnostics', 'Building A, Basement', '+1-555-0107', 'radiology@hospital.com', 'active'),
('Human Resources', 'HRDE', 'Staff management and recruitment', 'Admin Building, 1st Floor', '+1-555-0108', 'hr@hospital.com', 'active'),
('Administration', 'ADMN', 'Hospital administration and operations', 'Admin Building, 2nd Floor', '+1-555-0109', 'admin@hospital.com', 'active'),
('Pharmacy', 'PHRM', 'Medication dispensing and management', 'Building A, Ground Floor', '+1-555-0110', 'pharmacy@hospital.com', 'active');

-- ========================================================
-- SEED DATA - USERS (with bcrypt hashed passwords)
-- Passwords: Admin@123!, Hr@123!, Director@123!, Doctor@123!, Nurse@123!, Reception@123!, Account@123!, Patient@123!
-- ========================================================
INSERT INTO users (name, email, password, role, phone, status, email_verified_at, created_at, updated_at) VALUES
('System Administrator', 'admin@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'super_admin', '+1-555-1000', 'active', NOW(), NOW(), NOW()),
('Sarah Johnson', 'hr@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'hr_admin', '+1-555-1001', 'active', NOW(), NOW(), NOW()),
('Dr. Michael Chen', 'director@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'medical_director', '+1-555-1002', 'active', NOW(), NOW(), NOW()),
('Dr. Emily Williams', 'dr.williams@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'doctor', '+1-555-2001', 'active', NOW(), NOW(), NOW()),
('Dr. James Rodriguez', 'dr.rodriguez@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'doctor', '+1-555-2002', 'active', NOW(), NOW(), NOW()),
('Dr. Lisa Anderson', 'dr.anderson@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'doctor', '+1-555-2003', 'active', NOW(), NOW(), NOW()),
('Dr. Robert Kim', 'dr.kim@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'doctor', '+1-555-2004', 'active', NOW(), NOW(), NOW()),
('Dr. Maria Garcia', 'dr.garcia@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'doctor', '+1-555-2005', 'active', NOW(), NOW(), NOW()),
('Jennifer Martinez', 'nurse.martinez@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'nurse', '+1-555-3001', 'active', NOW(), NOW(), NOW()),
('David Thompson', 'nurse.thompson@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'nurse', '+1-555-3002', 'active', NOW(), NOW(), NOW()),
('Amanda White', 'nurse.white@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'nurse', '+1-555-3003', 'active', NOW(), NOW(), NOW()),
('Karen Davis', 'reception@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'receptionist', '+1-555-4001', 'active', NOW(), NOW(), NOW()),
('Thomas Brown', 'accounting@hospital.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'accountant', '+1-555-5001', 'active', NOW(), NOW(), NOW()),
('John Smith', 'john.smith@email.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'patient', '+1-555-6001', 'active', NOW(), NOW(), NOW()),
('Mary Johnson', 'mary.j@email.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'patient', '+1-555-6002', 'active', NOW(), NOW(), NOW()),
('Robert Wilson', 'rob.wilson@email.com', '$2y$12$K0ByB.6YI2/OYrB4fQOYLe6QdRg6XnYlYqYqYqYqYqYqYqYqYqYqYq', 'patient', '+1-555-6003', 'active', NOW(), NOW(), NOW());

-- ========================================================
-- SEED DATA - STAFF
-- ========================================================
INSERT INTO staff (user_id, staff_id, department_id, designation, employment_type, joining_date, salary, status, verification_pin, pin_generated_at, created_at, updated_at) VALUES
(2, 'STF-HR001', 8, 'HR Manager', 'full_time', '2019-03-15', 85000.00, 'active', '123456', NOW(), NOW(), NOW()),
(3, 'STF-DIR01', 9, 'Medical Director', 'full_time', '2018-01-10', 200000.00, 'active', '234567', NOW(), NOW(), NOW()),
(4, 'STF-DOC01', 2, 'Senior Cardiologist', 'full_time', '2020-06-01', 180000.00, 'active', '345678', NOW(), NOW(), NOW()),
(5, 'STF-DOC02', 4, 'Orthopedic Surgeon', 'full_time', '2019-08-20', 175000.00, 'active', '456789', NOW(), NOW(), NOW()),
(6, 'STF-DOC03', 3, 'Pediatrician', 'full_time', '2021-02-15', 160000.00, 'active', '567890', NOW(), NOW(), NOW()),
(7, 'STF-DOC04', 5, 'Neurologist', 'full_time', '2017-11-05', 190000.00, 'active', '678901', NOW(), NOW(), NOW()),
(8, 'STF-DOC05', 6, 'Oncologist', 'full_time', '2019-04-12', 185000.00, 'active', '789012', NOW(), NOW(), NOW()),
(9, 'STF-NUR01', 1, 'Emergency Nurse', 'full_time', '2020-09-01', 75000.00, 'active', '890123', NOW(), NOW(), NOW()),
(10, 'STF-NUR02', 2, 'Cardiac Nurse', 'full_time', '2021-01-20', 72000.00, 'active', '901234', NOW(), NOW(), NOW()),
(11, 'STF-NUR03', 3, 'Pediatric Nurse', 'full_time', '2022-03-10', 70000.00, 'active', '012345', NOW(), NOW(), NOW()),
(12, 'STF-REC01', 9, 'Front Desk Receptionist', 'full_time', '2020-07-15', 45000.00, 'active', '112233', NOW(), NOW(), NOW()),
(13, 'STF-ACC01', 9, 'Financial Accountant', 'full_time', '2019-05-22', 65000.00, 'active', '223344', NOW(), NOW(), NOW());

-- ========================================================
-- SEED DATA - DOCTORS
-- ========================================================
INSERT INTO doctors (staff_id, specialization, qualification, experience_years, license_number, license_expiry, consultation_fee, availability_schedule, bio, rating, total_reviews, status, created_at, updated_at) VALUES
(3, 'Interventional Cardiology', 'MD, Board Certified', 15, 'MD-10001', '2026-12-31', 250.00, '{"monday":["09:00-12:00","14:00-17:00"],"tuesday":["09:00-12:00","14:00-17:00"],"wednesday":["09:00-12:00","14:00-17:00"],"thursday":["09:00-12:00","14:00-17:00"],"friday":["09:00-12:00","14:00-16:00"]}', 'Experienced cardiologist specializing in interventional procedures.', 4.8, 124, 'available', NOW(), NOW()),
(4, 'Joint Replacement Surgery', 'MD, Board Certified', 12, 'MD-10002', '2026-06-30', 300.00, '{"monday":["08:00-12:00","13:00-16:00"],"tuesday":["08:00-12:00","13:00-16:00"],"wednesday":["08:00-12:00","13:00-16:00"],"thursday":["08:00-12:00","13:00-16:00"],"friday":["08:00-12:00"]}', 'Expert in hip and knee replacement surgeries.', 4.9, 89, 'available', NOW(), NOW()),
(5, 'Neonatal Care', 'MD, Board Certified', 10, 'MD-10003', '2027-03-15', 200.00, '{"monday":["09:00-13:00","15:00-18:00"],"tuesday":["09:00-13:00","15:00-18:00"],"wednesday":["09:00-13:00","15:00-18:00"],"thursday":["09:00-13:00","15:00-18:00"],"friday":["09:00-13:00"]}', 'Specialized in newborn and infant care.', 4.7, 156, 'available', NOW(), NOW()),
(6, 'Neurovascular Surgery', 'MD, Board Certified', 18, 'MD-10004', '2026-09-20', 350.00, '{"monday":["08:00-12:00","14:00-17:00"],"tuesday":["08:00-12:00","14:00-17:00"],"wednesday":["08:00-12:00","14:00-17:00"],"thursday":["08:00-12:00","14:00-17:00"],"friday":["08:00-12:00"]}', 'Leading specialist in brain and spine surgeries.', 4.9, 67, 'available', NOW(), NOW()),
(7, 'Medical Oncology', 'MD, Board Certified', 14, 'MD-10005', '2027-01-10', 275.00, '{"monday":["09:00-12:00","14:00-18:00"],"tuesday":["09:00-12:00","14:00-18:00"],"wednesday":["09:00-12:00","14:00-18:00"],"thursday":["09:00-12:00","14:00-18:00"],"friday":["09:00-12:00","14:00-16:00"]}', 'Compassionate cancer care with latest treatment protocols.', 4.8, 203, 'available', NOW(), NOW());

-- ========================================================
-- SEED DATA - PATIENTS
-- ========================================================
INSERT INTO patients (user_id, date_of_birth, gender, blood_group, medical_history, allergies, emergency_contact, patient_id, created_at, updated_at) VALUES
(14, '1985-03-20', 'male', 'A+', 'No significant medical history.', 'None known.', '{"name":"Emergency Contact","phone":"+1-555-9999","relationship":"Family"}', 'PT-ABC123', NOW(), NOW()),
(15, '1990-07-15', 'female', 'O-', 'Asthma diagnosed in childhood.', 'Penicillin allergy.', '{"name":"Jane Johnson","phone":"+1-555-8888","relationship":"Sister"}', 'PT-DEF456', NOW(), NOW()),
(16, '1978-11-02', 'male', 'B+', 'Hypertension, Type 2 Diabetes.', 'Shellfish allergy.', '{"name":"Sarah Wilson","phone":"+1-555-7777","relationship":"Wife"}', 'PT-GHI789', NOW(), NOW());

-- ========================================================
-- SEED DATA - LEAVE BALANCES
-- ========================================================
INSERT INTO leave_balances (staff_id, year, type, total_days, used_days, remaining_days, created_at, updated_at) VALUES
(1, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(1, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(1, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(1, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(2, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(2, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(2, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(2, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(3, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(3, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(3, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(3, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(4, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(4, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(4, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(4, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(5, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(5, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(5, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(5, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(6, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(6, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(6, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(6, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(7, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(7, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(7, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(7, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(8, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(8, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(8, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(8, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(9, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(9, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(9, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(9, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(10, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(10, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(10, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(10, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(11, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(11, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(11, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(11, 2026, 'study', 10, 0, 10, NOW(), NOW()),
(12, 2026, 'annual', 21, 0, 21, NOW(), NOW()),
(12, 2026, 'sick', 10, 0, 10, NOW(), NOW()),
(12, 2026, 'bereavement', 5, 0, 5, NOW(), NOW()),
(12, 2026, 'study', 10, 0, 10, NOW(), NOW());

-- ========================================================
-- SEED DATA - BLOG POSTS
-- ========================================================
INSERT INTO blog_posts (author_id, title, slug, excerpt, content, tags, status, published_at, views, created_at, updated_at) VALUES
(1, 'Understanding Heart Health: Tips for a Stronger Cardiovascular System', 'understanding-heart-health-tips', 'Learn essential tips to maintain a healthy heart and prevent cardiovascular diseases.', 'Heart disease remains one of the leading causes of death worldwide. Maintaining a healthy cardiovascular system requires a combination of regular exercise, balanced nutrition, stress management, and routine check-ups...', '["cardiology", "heart health", "prevention"]', 'published', NOW(), 156, NOW(), NOW()),
(1, 'Pediatric Care: Ensuring Your Child''s Healthy Development', 'pediatric-care-ensuring-healthy-development', 'A comprehensive guide to pediatric healthcare and child development milestones.', 'Regular pediatric check-ups are crucial for monitoring your child''s growth and development. From vaccinations to nutritional guidance, our pediatric team provides comprehensive care...', '["pediatrics", "child health", "development"]', 'published', NOW(), 89, NOW(), NOW()),
(1, 'Advances in Cancer Treatment: What Patients Need to Know', 'advances-in-cancer-treatment', 'Exploring the latest breakthroughs in oncology and cancer treatment options.', 'The field of oncology has seen remarkable advances in recent years. From immunotherapy to precision medicine, new treatment options offer hope to cancer patients...', '["oncology", "cancer", "treatment"]', 'published', NOW(), 234, NOW(), NOW()),
(1, 'Emergency Preparedness: When to Visit the ER', 'emergency-preparedness-when-to-visit-er', 'Know the signs and symptoms that require immediate emergency medical attention.', 'Understanding when to seek emergency care can save lives. Chest pain, difficulty breathing, severe bleeding, and sudden confusion are all signs that require immediate attention...', '["emergency", "health tips", "safety"]', 'published', NOW(), 312, NOW(), NOW()),
(1, 'Mental Health Awareness in Healthcare Settings', 'mental-health-awareness-healthcare', 'The importance of mental health support for both patients and healthcare workers.', 'Mental health is an integral part of overall well-being. Our hospital provides comprehensive mental health services including counseling, therapy, and psychiatric care...', '["mental health", "wellness", "support"]', 'draft', NULL, 0, NOW(), NOW());

-- ========================================================
-- SEED DATA - CAREERS
-- ========================================================
INSERT INTO careers (title, department_id, description, requirements, employment_type, salary_min, salary_max, location, deadline, status, created_at, updated_at) VALUES
('Registered Nurse - Emergency Department', 1, 'We are seeking experienced registered nurses to join our emergency department team. The ideal candidate will have excellent clinical skills and the ability to work in a fast-paced environment.', 'Bachelor of Science in Nursing (BSN). Current RN license. ACLS and BLS certification. Minimum 2 years emergency nursing experience.', 'full_time', 70000.00, 95000.00, 'Main Hospital Campus', '2026-07-01', 'open', NOW(), NOW()),
('Cardiologist - Interventional', 2, 'Seeking a board-certified interventional cardiologist to join our growing cardiology department.', 'MD or DO degree. Board certification in Cardiology. Fellowship training in Interventional Cardiology. State medical license.', 'full_time', 300000.00, 450000.00, 'Main Hospital Campus', '2026-08-01', 'open', NOW(), NOW()),
('Medical Laboratory Technician', 7, 'Responsible for performing laboratory tests and procedures to support diagnostic and treatment decisions.', 'Associate degree in Medical Laboratory Technology. MLT certification. Attention to detail and accuracy.', 'full_time', 50000.00, 65000.00, 'Main Hospital Campus', '2026-06-15', 'open', NOW(), NOW()),
('HR Coordinator', 8, 'Support HR operations including recruitment, onboarding, and employee relations.', 'Bachelor''s degree in Human Resources or related field. 1-3 years HR experience. Strong organizational and communication skills.', 'full_time', 50000.00, 65000.00, 'Admin Building', '2026-06-15', 'open', NOW(), NOW()),
('Pharmacy Intern', 10, 'Pharmacy intern position for students currently enrolled in a Doctor of Pharmacy program.', 'Currently enrolled in PharmD program. Good academic standing. Strong attention to detail.', 'intern', 35000.00, 45000.00, 'Main Hospital Campus', '2026-06-15', 'open', NOW(), NOW());

-- ========================================================
-- SEED DATA - SETTINGS
-- ========================================================
INSERT INTO settings (key, value, group_name, type, description, created_at, updated_at) VALUES
('hospital_name', 'City General Hospital', 'general', 'string', 'Hospital display name', NOW(), NOW()),
('hospital_address', '123 Healthcare Avenue, Medical District, NY 10001', 'general', 'string', 'Hospital address', NOW(), NOW()),
('hospital_phone', '+1-555-HOSPITAL', 'general', 'string', 'Main hospital phone', NOW(), NOW()),
('hospital_email', 'info@citygeneralhospital.com', 'general', 'string', 'Hospital contact email', NOW(), NOW()),
('working_hours_start', '08:00', 'general', 'string', 'Working hours start time', NOW(), NOW()),
('working_hours_end', '18:00', 'general', 'string', 'Working hours end time', NOW(), NOW()),
('appointment_duration', '30', 'appointments', 'integer', 'Default appointment duration in minutes', NOW(), NOW()),
('max_appointments_per_day', '50', 'appointments', 'integer', 'Maximum appointments per day', NOW(), NOW()),
('leave_approval_required', 'true', 'leave', 'boolean', 'Require approval for leave requests', NOW(), NOW()),
('default_annual_leave', '21', 'leave', 'integer', 'Default annual leave days', NOW(), NOW()),
('default_sick_leave', '10', 'leave', 'integer', 'Default sick leave days', NOW(), NOW()),
('attendance_grace_period', '15', 'attendance', 'integer', 'Grace period for late arrivals in minutes', NOW(), NOW()),
('enable_qr_attendance', 'true', 'attendance', 'boolean', 'Enable QR code attendance', NOW(), NOW()),
('enable_gps_attendance', 'true', 'attendance', 'boolean', 'Enable GPS attendance tracking', NOW(), NOW()),
('maintenance_mode', 'false', 'system', 'boolean', 'System maintenance mode', NOW(), NOW()),
('email_notifications', 'true', 'notifications', 'boolean', 'Enable email notifications', NOW(), NOW()),
('sms_notifications', 'false', 'notifications', 'boolean', 'Enable SMS notifications', NOW(), NOW());

-- ========================================================
-- SEED DATA - CONTACTS
-- ========================================================
INSERT INTO contacts (name, email, phone, subject, message, status, created_at, updated_at) VALUES
('Alice Cooper', 'alice@email.com', '+1-555-1111', 'Appointment Inquiry', 'I would like to schedule a consultation with Dr. Williams. Please let me know the available slots.', 'new', NOW(), NOW()),
('Bob Martin', 'bob@email.com', '+1-555-2222', 'Billing Question', 'I have a question about my recent bill. Can someone contact me to discuss?', 'read', NOW(), NOW()),
('Carol White', 'carol@email.com', '+1-555-3333', 'Career Application', 'I am interested in the Registered Nurse position. How can I apply?', 'replied', NOW(), NOW());

-- ========================================================
-- SEED DATA - APPOINTMENTS
-- ========================================================
INSERT INTO appointments (patient_id, doctor_id, department_id, appointment_date, status, type, symptoms, notes, created_by, created_at, updated_at) VALUES
(1, 1, 2, '2026-05-10 10:00:00', 'confirmed', 'consultation', 'Chest pain, shortness of breath', 'First cardiology visit', 1, NOW(), NOW()),
(2, 3, 3, '2026-05-11 14:00:00', 'pending', 'routine_checkup', 'Regular checkup', 'Annual physical', 1, NOW(), NOW()),
(3, 5, 6, '2026-05-12 09:30:00', 'confirmed', 'follow_up', 'Post-chemo follow up', 'Continuing treatment plan', 1, NOW(), NOW()),
(1, 2, 4, '2026-05-15 11:00:00', 'pending', 'consultation', 'Knee pain', 'Possible joint replacement candidate', 1, NOW(), NOW());

-- ========================================================
-- SEED DATA - LEAVES
-- ========================================================
INSERT INTO leaves (staff_id, type, start_date, end_date, days_requested, reason, status, approved_by, approved_at, created_at, updated_at) VALUES
(1, 'annual', '2026-06-01', '2026-06-07', 7, 'Family vacation to Hawaii', 'approved', 1, NOW(), NOW(), NOW()),
(3, 'sick', '2026-05-05', '2026-05-06', 2, 'Flu symptoms, need rest', 'approved', 1, NOW(), NOW(), NOW()),
(5, 'annual', '2026-07-10', '2026-07-17', 7, 'Summer break with family', 'pending', NULL, NULL, NOW(), NOW()),
(8, 'bereavement', '2026-05-08', '2026-05-10', 3, 'Family member passed away', 'pending', NULL, NULL, NOW(), NOW());

-- ========================================================
-- SEED DATA - ATTENDANCES
-- ========================================================
INSERT INTO attendances (staff_id, date, check_in, check_out, status, check_in_type, check_out_type, work_hours, created_at, updated_at) VALUES
(1, '2026-05-01', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(2, '2026-05-01', '08:15:00', '17:30:00', 'late', 'web', 'web', 9.25, NOW(), NOW()),
(3, '2026-05-01', '08:00:00', '16:00:00', 'present', 'web', 'web', 8.00, NOW(), NOW()),
(4, '2026-05-01', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(5, '2026-05-01', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(1, '2026-05-02', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(2, '2026-05-02', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(3, '2026-05-02', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(4, '2026-05-02', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(5, '2026-05-02', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(1, '2026-05-03', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(2, '2026-05-03', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(3, '2026-05-03', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(4, '2026-05-03', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW()),
(5, '2026-05-03', '08:00:00', '17:00:00', 'present', 'web', 'web', 9.00, NOW(), NOW());

-- ========================================================
-- SEED DATA - NOTIFICATIONS
-- ========================================================
INSERT INTO notifications (user_id, type, title, message, data, channel, read_at, created_at, updated_at) VALUES
(4, 'appointment', 'New Appointment', 'You have a new appointment with John Smith on May 10, 2026.', '{"appointment_id":1}', 'in_app', NULL, NOW(), NOW()),
(4, 'appointment', 'Appointment Confirmed', 'Your appointment with Mary Johnson has been confirmed.', '{"appointment_id":2}', 'in_app', NOW(), NOW(), NOW()),
(1, 'system', 'System Update', 'The system will undergo maintenance on Sunday at 2 AM.', '{}', 'in_app', NULL, NOW(), NOW()),
(2, 'leave', 'Leave Approved', 'Your annual leave request has been approved.', '{"leave_id":1}', 'in_app', NOW(), NOW(), NOW()),
(5, 'leave', 'Leave Request', 'Dr. Rodriguez has requested sick leave.', '{"leave_id":2}', 'in_app', NULL, NOW(), NOW());

-- ========================================================
-- SEED DATA - CERTIFICATIONS
-- ========================================================
INSERT INTO certifications (staff_id, title, issuing_organization, credential_id, issue_date, expiry_date, status, created_at, updated_at) VALUES
(3, 'Board Certified Cardiologist', 'American Board of Internal Medicine', 'ABIM-12345', '2015-06-15', '2026-12-31', 'active', NOW(), NOW()),
(4, 'Orthopedic Surgery Certification', 'American Board of Orthopaedic Surgery', 'ABOS-67890', '2018-03-20', '2026-06-30', 'active', NOW(), NOW()),
(6, 'Neurosurgery Certification', 'American Board of Neurological Surgery', 'ABNS-11111', '2010-09-10', '2026-09-20', 'active', NOW(), NOW()),
(7, 'Medical Oncology Certification', 'American Board of Internal Medicine', 'ABIM-22222', '2012-11-05', '2027-01-10', 'active', NOW(), NOW());

-- ========================================================
-- SEED DATA - SHIFTS
-- ========================================================
INSERT INTO shifts (staff_id, shift_name, start_time, end_time, days, department_id, effective_from, status, created_at, updated_at) VALUES
(1, 'Day Shift', '08:00:00', '17:00:00', '["monday","tuesday","wednesday","thursday","friday"]', 8, '2026-01-01', 'active', NOW(), NOW()),
(3, 'Morning Shift', '08:00:00', '12:00:00', '["monday","tuesday","wednesday","thursday","friday"]', 2, '2026-01-01', 'active', NOW(), NOW()),
(4, 'Afternoon Shift', '13:00:00', '17:00:00', '["monday","tuesday","wednesday","thursday","friday"]', 4, '2026-01-01', 'active', NOW(), NOW()),
(9, 'Night Shift', '20:00:00', '08:00:00', '["monday","tuesday","wednesday","thursday","friday","saturday","sunday"]', 1, '2026-01-01', 'active', NOW(), NOW());

-- ========================================================
-- SEED DATA - AUDIT LOGS
-- ========================================================
INSERT INTO audit_logs (user_id, action, entity_type, entity_id, old_values, new_values, ip_address, user_agent, url, method, created_at) VALUES
(1, 'create', 'users', 14, NULL, '{"name":"John Smith","role":"patient"}', '192.168.1.1', 'Mozilla/5.0', '/api/auth/register', 'POST', NOW()),
(1, 'create', 'appointments', 1, NULL, '{"patient_id":1,"doctor_id":1}', '192.168.1.1', 'Mozilla/5.0', '/api/appointments', 'POST', NOW()),
(1, 'update', 'leaves', 1, '{"status":"pending"}', '{"status":"approved"}', '192.168.1.1', 'Mozilla/5.0', '/api/admin/leaves/1/approve', 'POST', NOW());

COMMIT;

-- ========================================================
-- END OF DATABASE DUMP
-- ========================================================