-- MySQL Schema for Ambulance App (wavejtpe_masm_app database)
-- Note: Database is created automatically by Docker MYSQL_DATABASE environment variable

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    role VARCHAR(50),
    is_verified BOOLEAN DEFAULT FALSE,
    OTP VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- MASM scheme table
CREATE TABLE IF NOT EXISTS masm_scheme (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    `desc` TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- Clients table
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    insurance_scheme_id INT,
    phone_number VARCHAR(20),
    is_cash_client BOOLEAN DEFAULT FALSE,
    membership_number VARCHAR(255),
    suffix VARCHAR(50),
    address TEXT,
    date_of_birth DATE,
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (insurance_scheme_id) REFERENCES masm_scheme(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_phone_number (phone_number),
    INDEX idx_insurance_scheme_id (insurance_scheme_id)
);


-- Chat table
CREATE TABLE IF NOT EXISTS chat (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message_to INT NOT NULL,
    message TEXT NOT NULL,
    status ENUM('pending', 'cancelled', 'dispatched', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (message_to) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_message_to (message_to),
    INDEX idx_status (status)
);

-- Ambulance table
CREATE TABLE IF NOT EXISTS ambulance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicle_number VARCHAR(50) NOT NULL UNIQUE,
    license_plate VARCHAR(50) NOT NULL UNIQUE,
    model VARCHAR(255),
    make VARCHAR(255),
    status ENUM('available', 'in-use', 'maintenance', 'out-of-service') DEFAULT 'available',
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_vehicle_number (vehicle_number),
    INDEX idx_license_plate (license_plate),
    INDEX idx_status (status)
);

-- Driver table
CREATE TABLE IF NOT EXISTS driver (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    license_number VARCHAR(50) NOT NULL UNIQUE,
    ambulance_id INT,
    status ENUM('on-duty', 'off-duty', 'available') DEFAULT 'on-duty',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (ambulance_id) REFERENCES ambulance(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_license_number (license_number),
    INDEX idx_ambulance_id (ambulance_id),
    INDEX idx_status (status)
);

-- Ambulance requests table
CREATE TABLE IF NOT EXISTS ambulance_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_by INT NULL,
    created_by INT NULL,
    is_urgent BOOLEAN DEFAULT FALSE,
    location VARCHAR(255) NOT NULL,
    destination VARCHAR(255),
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    destination_latitude DECIMAL(10, 8),
    destination_longitude DECIMAL(11, 8),
    status ENUM('pending', 'dispatched', 'ongoing', 'enroute', 'cancelled', 'completed') DEFAULT 'pending',
    comment TEXT,
    trip_started_at TIMESTAMP NULL,
    trip_completed_at TIMESTAMP NULL,
    pickup_confirmed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (request_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_request_by (request_by),
    INDEX idx_created_by (created_by),
    INDEX idx_status (status)
);

-- Notifications table
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    request_id INT NULL,
    text TEXT NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_read BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (request_id) REFERENCES ambulance_requests(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_request_id (request_id),
    INDEX idx_timestamp (timestamp)
);

-- Dispatch table
CREATE TABLE IF NOT EXISTS dispatch (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ambulance_request_id INT NOT NULL,
    ambulance_id INT NOT NULL,
    driver_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (ambulance_request_id) REFERENCES ambulance_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (ambulance_id) REFERENCES ambulance(id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES driver(id) ON DELETE CASCADE,
    INDEX idx_ambulance_request_id (ambulance_request_id),
    INDEX idx_ambulance_id (ambulance_id),
    INDEX idx_driver_id (driver_id),
    UNIQUE KEY unique_request (ambulance_request_id)
);

-- Device tokens table (for push notifications)
CREATE TABLE IF NOT EXISTS device_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(512) NOT NULL UNIQUE,
    platform ENUM('android', 'ios', 'web') DEFAULT 'android',
    is_active BOOLEAN DEFAULT TRUE,
    push_enabled BOOLEAN DEFAULT TRUE,
    sound_enabled BOOLEAN DEFAULT TRUE,
    vibration_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_platform (platform),
    INDEX idx_active (is_active)
);

-- Token blacklist table (for invalidated JWTs)
CREATE TABLE IF NOT EXISTS token_blacklist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token TEXT NOT NULL,
    expires_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_expires_at (expires_at)
);

-- Incident type table
CREATE TABLE IF NOT EXISTS incident_type (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- Incident details table
CREATE TABLE IF NOT EXISTS incident_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL UNIQUE,
    number_of_casualties INT DEFAULT 0,
    is_member BOOLEAN DEFAULT FALSE,
    bill_accepted BOOLEAN DEFAULT FALSE,
    incident_type_id INT,
    incident_requirement_notes TEXT,
    general_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES ambulance_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (incident_type_id) REFERENCES incident_type(id) ON DELETE SET NULL,
    INDEX idx_request_id (request_id),
    INDEX idx_incident_type_id (incident_type_id)
);


-- ================================
-- PATIENT REPORTS TABLE
-- ================================
CREATE TABLE IF NOT EXISTS patient_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    incident_type_id INT  NULL,
    general_complaints TEXT NULL,
    impression TEXT NULL,
    patient_allergies TEXT NULL,
    patient_past_history TEXT NULL,
    patient_admitting_hospital VARCHAR(255) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (request_id) REFERENCES ambulance_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (incident_type_id) REFERENCES incident_type(id) ON DELETE RESTRICT,

    INDEX idx_request_id (request_id),
    INDEX idx_incident_type_id (incident_type_id)
);

-- ================================
-- FLUIDS ADMINISTERED TABLE
-- ================================
CREATE TABLE IF NOT EXISTS fluids_administered (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_report_id INT NOT NULL,
    fluid_name VARCHAR(255) NOT NULL,
    volume VARCHAR(100) NULL,
    time_given TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (patient_report_id) REFERENCES patient_reports(id) ON DELETE CASCADE,

    INDEX idx_patient_report_id (patient_report_id)
);

-- ================================
-- DRUGS MASTER TABLE
-- ================================
CREATE TABLE IF NOT EXISTS drugs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_drug_name (name)
);

-- ================================
-- DRUGS ADMINISTERED TABLE
-- ================================
CREATE TABLE IF NOT EXISTS drugs_administered (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_report_id INT NOT NULL,
    drug_id INT NOT NULL,
    dose VARCHAR(100) NOT NULL,
    route ENUM(
        'oral',
        'intravenous',
        'intramuscular',
        'subcutaneous',
        'inhalation',
        'topical',
        'rectal',
        'nasal',
        'transdermal'
    ) NOT NULL,
    time_given TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (patient_report_id) REFERENCES patient_reports(id) ON DELETE CASCADE,
    FOREIGN KEY (drug_id) REFERENCES drugs(id) ON DELETE RESTRICT,

    INDEX idx_patient_report_id (patient_report_id),
    INDEX idx_drug_id (drug_id)
);

-- ================================
-- OBSERVATIONS TABLE
-- ================================
CREATE TABLE IF NOT EXISTS observations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_report_id INT NOT NULL,
    observation ENUM('Blood Pressure', 'Pulse', 'Temperature', 'Respirations', 'Oxygen Saturation', 'Skin', 'Dextrostix', 'Pupils', 'Glasgow Coma Scale', 'Oxygen') NOT NULL,
    value VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (patient_report_id) REFERENCES patient_reports(id) ON DELETE CASCADE,

    INDEX idx_patient_report_id (patient_report_id)
);

-- ================================
-- PATIENT REPORTS EMAILS TABLE
-- ================================
CREATE TABLE IF NOT EXISTS patient_reports_emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_report_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    code VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_report_id) REFERENCES patient_reports(id) ON DELETE CASCADE,
    INDEX idx_patient_report_id (patient_report_id)
);


-- ================================
-- APPROVED HEALTH CARE EMAILS TABLE
-- ================================
CREATE TABLE IF NOT EXISTS approved_health_care_emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    health_care_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

