-- Consumers Module Schema

CREATE TABLE IF NOT EXISTS consumers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    location_id INT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    preferred_name VARCHAR(100),
    date_of_birth DATE NOT NULL,
    gender ENUM('male','female','non_binary','other') NOT NULL,
    ssn_encrypted VARCHAR(255),
    medicaid_id VARCHAR(50),
    medicare_id VARCHAR(50),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(2) DEFAULT 'MO',
    zip VARCHAR(10),
    phone VARCHAR(20),
    email VARCHAR(255),
    photo_url VARCHAR(500),
    service_type ENUM('isl','group_home','host_home','day_hab') NOT NULL,
    admission_date DATE,
    discharge_date DATE,
    status ENUM('active','inactive','discharged','pending','deceased') DEFAULT 'active',
    guardian_type ENUM('self','full','limited','conservator','none') DEFAULT 'self',
    primary_diagnosis TEXT,
    level_of_care INT,
    funding_source VARCHAR(100),
    county VARCHAR(100),
    regional_office VARCHAR(100),
    notes TEXT,
    metadata JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (organization_id, status),
    INDEX (location_id),
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS consumer_contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consumer_id INT NOT NULL,
    organization_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    relationship VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(255),
    address TEXT,
    is_emergency TINYINT(1) DEFAULT 0,
    is_guardian TINYINT(1) DEFAULT 0,
    contact_priority INT DEFAULT 0,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS consumer_diagnoses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consumer_id INT NOT NULL,
    organization_id INT NOT NULL,
    icd10_code VARCHAR(20),
    description VARCHAR(500) NOT NULL,
    diagnosis_date DATE,
    diagnosing_physician VARCHAR(200),
    status ENUM('active','resolved','chronic') DEFAULT 'active',
    is_primary TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS consumer_allergies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consumer_id INT NOT NULL,
    organization_id INT NOT NULL,
    allergen VARCHAR(200) NOT NULL,
    reaction VARCHAR(500),
    severity ENUM('mild','moderate','severe','life_threatening') DEFAULT 'moderate',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS consumer_assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    consumer_id INT NOT NULL,
    user_id INT NOT NULL,
    organization_id INT NOT NULL,
    role VARCHAR(100),
    is_active TINYINT(1) DEFAULT 1,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consumer_id) REFERENCES consumers(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;