-- Staff Module

CREATE TABLE IF NOT EXISTS staff_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    organization_id INT NOT NULL,
    employee_id VARCHAR(50),
    hire_date DATE,
    termination_date DATE,
    employment_type ENUM('full_time','part_time','prn','contract') DEFAULT 'full_time',
    department VARCHAR(100),
    supervisor_id INT,
    hourly_rate DECIMAL(8,2),
    emergency_contact_name VARCHAR(200),
    emergency_contact_phone VARCHAR(20),
    background_check_date DATE,
    background_check_status ENUM('pending','cleared','flagged','expired') DEFAULT 'pending',
    driver_license VARCHAR(50),
    driver_license_exp DATE,
    notes TEXT,
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (user_id, organization_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (supervisor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Training & Certifications - Per 9 CSR 45-5.010(3), 42 CFR 483.430

CREATE TABLE IF NOT EXISTS training_courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(50) DEFAULT 'general',
    duration_hours DECIMAL(5,2),
    renewal_months INT,
    required_by_dmh TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    content_url VARCHAR(500),
    passing_score INT DEFAULT 80,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS training_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    assigned_by INT,
    due_date DATE,
    status ENUM('assigned','in_progress','completed','overdue','waived') DEFAULT 'assigned',
    completed_at DATETIME,
    score INT,
    certificate_url VARCHAR(500),
    expiration_date DATE,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX (organization_id, user_id, status),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES training_courses(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Schedule Module

CREATE TABLE IF NOT EXISTS schedule_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    location_id INT,
    title VARCHAR(255) NOT NULL,
    event_type VARCHAR(50) DEFAULT 'shift',
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    user_id INT,
    consumer_id INT,
    status ENUM('scheduled','confirmed','in_progress','completed','cancelled') DEFAULT 'scheduled',
    notes TEXT,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (organization_id, start_time),
    INDEX (user_id, start_time),
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Documents Module

CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    folder_id INT,
    consumer_id INT,
    user_id INT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    file_path VARCHAR(500) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    file_size INT,
    category ENUM('isp','medical','consent','incident','training','policy','financial','correspondence','other') DEFAULT 'other',
    version INT DEFAULT 1,
    requires_signature TINYINT(1) DEFAULT 0,
    signed_at DATETIME,
    signed_by INT,
    expiration_date DATE,
    is_confidential TINYINT(1) DEFAULT 0,
    uploaded_by INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX (organization_id, category),
    INDEX (consumer_id),
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE SET NULL,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS document_folders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    parent_id INT,
    name VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES document_folders(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Reports Module

CREATE TABLE IF NOT EXISTS generated_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    type VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    parameters JSON,
    file_path VARCHAR(500),
    format ENUM('pdf','xlsx','csv','docx') DEFAULT 'pdf',
    status ENUM('queued','generating','completed','failed') DEFAULT 'queued',
    generated_by INT NOT NULL,
    generated_at DATETIME,
    error TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (generated_by) REFERENCES users(id)
) ENGINE=InnoDB;