-- =============================================================================
-- MindScreen AI — MySQL Database Schema
-- =============================================================================
-- Charset/collation chosen for full Unicode support (emoji, accented chars
-- in free-text chat messages).
-- Run as: mysql -u <user> -p < schema.sql
-- =============================================================================

CREATE DATABASE IF NOT EXISTS mindscreen_ai
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE mindscreen_ai;

-- -----------------------------------------------------------------------------
-- users
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    participant_code    VARCHAR(32)  NOT NULL UNIQUE COMMENT 'Anonymized research ID shown in exports/dashboards, never PII',
    email               VARCHAR(255) NOT NULL UNIQUE,
    password_hash       VARCHAR(255) NOT NULL COMMENT 'bcrypt hash, never plaintext',
    display_name        VARCHAR(100) NULL,
    date_of_birth       DATE NULL COMMENT 'Used only to confirm 18-30 eligibility, not shown in research exports',
    is_email_verified   BOOLEAN NOT NULL DEFAULT FALSE,
    email_verify_token  VARCHAR(255) NULL,
    email_verify_sent_at DATETIME NULL,
    consent_given_at    DATETIME NULL COMMENT 'Timestamp of informed research consent',
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_email (email),
    INDEX idx_users_participant_code (participant_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- chat_sessions
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS chat_sessions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_uuid    CHAR(36) NOT NULL UNIQUE,
    user_id         BIGINT UNSIGNED NOT NULL,
    stage           VARCHAR(32) NOT NULL DEFAULT 'intro_consent'
                        COMMENT 'intro_consent|rapport_building|phq9|gad7|pss10|open_reflection|complete|safety_paused',
    consent_given   BOOLEAN NOT NULL DEFAULT FALSE,
    safety_paused   BOOLEAN NOT NULL DEFAULT FALSE,
    started_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at    DATETIME NULL,
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_sessions_user (user_id),
    INDEX idx_sessions_stage (stage),
    INDEX idx_sessions_started_at (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- messages
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS messages (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id      BIGINT UNSIGNED NOT NULL,
    speaker         ENUM('bot', 'user') NOT NULL,
    content         TEXT NOT NULL,
    nlp_features    JSON NULL COMMENT 'Serialized TextFeatures.to_dict() for user messages',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_messages_session FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE,
    INDEX idx_messages_session (session_id),
    INDEX idx_messages_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- questionnaire_responses
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS questionnaire_responses (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id      BIGINT UNSIGNED NOT NULL,
    instrument      ENUM('PHQ9', 'GAD7', 'PSS10') NOT NULL,
    item_index      TINYINT UNSIGNED NOT NULL COMMENT 'Zero-indexed item number within the instrument',
    response_value  TINYINT UNSIGNED NOT NULL COMMENT '0-3 for PHQ9/GAD7, 0-4 for PSS10',
    raw_user_text   TEXT NULL COMMENT 'Original free-text answer before mapping to scale',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_qresponses_session FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE,
    UNIQUE KEY uq_session_instrument_item (session_id, instrument, item_index),
    INDEX idx_qresponses_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- ai_predictions
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_predictions (
    id                      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id              BIGINT UNSIGNED NOT NULL UNIQUE,
    depression_score        DECIMAL(5,1) NOT NULL COMMENT '0-100 combined score',
    depression_risk_level   ENUM('Low', 'Moderate', 'High') NOT NULL,
    anxiety_score           DECIMAL(5,1) NOT NULL,
    anxiety_risk_level      ENUM('Low', 'Moderate', 'High') NOT NULL,
    stress_score            DECIMAL(5,1) NOT NULL,
    stress_risk_level       ENUM('Low', 'Moderate', 'High') NOT NULL,
    overall_wellness_score  DECIMAL(5,1) NOT NULL,
    confidence_score        DECIMAL(4,3) NOT NULL,
    ml_predicted_risk       ENUM('Low', 'Moderate', 'High') NULL COMMENT 'Secondary scikit-learn signal',
    explanation_json        JSON NOT NULL COMMENT 'Full ScreeningResult.to_dict() for explainability/audit',
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_predictions_session FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE,
    INDEX idx_predictions_created_at (created_at),
    INDEX idx_predictions_depression_risk (depression_risk_level),
    INDEX idx_predictions_anxiety_risk (anxiety_risk_level),
    INDEX idx_predictions_stress_risk (stress_risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- reports
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reports (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id      BIGINT UNSIGNED NOT NULL UNIQUE,
    report_html     LONGTEXT NOT NULL,
    generated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_reports_session FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- feedback
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS feedback (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NULL,
    session_id      BIGINT UNSIGNED NULL,
    rating          TINYINT UNSIGNED NULL COMMENT '1-5 stars, nullable if only text feedback given',
    comment         TEXT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_feedback_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_feedback_session FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;