CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    mobile_number VARCHAR(15),
    education VARCHAR(100),
    medical_condition VARCHAR(100),
    diabetes_type VARCHAR(50),
    diabetes_duration VARCHAR(50),
    hba1c FLOAT,
    systolic INT,
    diastolic INT,
    medical_history TEXT,
    age INT,
    gender VARCHAR(20),
    role VARCHAR(20) DEFAULT 'Patient', -- Currently only 'Patient' role is used
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS test_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    test_type VARCHAR(50) NOT NULL, -- e.g., 'moca', 'memory_match'
    score INT,
    details JSON, -- For detailed breakdown like 'visuospatial', 'naming', etc.
    test_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Wellness logs (water, sleep)
CREATE TABLE IF NOT EXISTS wellness_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type VARCHAR(20) NOT NULL, -- 'water', 'sleep'
    value FLOAT, -- 250 (ml) or 8.5 (hours)
    details JSON, -- Any extra metadata
    log_date DATE DEFAULT (CURRENT_DATE),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Chat history
CREATE TABLE IF NOT EXISTS chat_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    message TEXT NOT NULL,
    is_user BOOLEAN DEFAULT TRUE,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
