-- ─────────────────────────────────────────────
--  NutriScreen Database Schema
--  MySQL 5.7+ / MariaDB 10.3+ compatible
-- ─────────────────────────────────────────────

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

USE nutriscreen;

-- ── ASSESSMENTS ───────────────────────────────
CREATE TABLE IF NOT EXISTS assessments (
    id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Contact
    first_name            VARCHAR(100)  DEFAULT '',
    last_name             VARCHAR(100)  DEFAULT '',
    email                 VARCHAR(255)  DEFAULT '',
    phone                 VARCHAR(30)   DEFAULT '',

    -- Demographics
    age                   TINYINT UNSIGNED DEFAULT NULL,
    sex                   VARCHAR(20)   DEFAULT '',
    gender_identity       VARCHAR(100)  DEFAULT '',

    -- Anthropometrics
    height_ft             TINYINT UNSIGNED DEFAULT NULL,
    height_in             TINYINT UNSIGNED DEFAULT NULL,
    height_cm             FLOAT         DEFAULT NULL,
    weight_lbs            FLOAT         DEFAULT NULL,
    weight_kg             FLOAT         DEFAULT NULL,
    usual_weight          VARCHAR(50)   DEFAULT '',
    weight_change         VARCHAR(20)   DEFAULT '',
    weight_change_amount  VARCHAR(200)  DEFAULT '',

    -- Lifestyle
    occupation_type       VARCHAR(30)   DEFAULT '',
    sitting_hours         TINYINT UNSIGNED DEFAULT NULL,
    exercise_days         TINYINT UNSIGNED DEFAULT NULL,
    exercise_type         VARCHAR(100)  DEFAULT '',
    exercise_duration     SMALLINT UNSIGNED DEFAULT NULL,
    activity_level        VARCHAR(30)   DEFAULT '',

    -- Eating patterns
    meals_per_day         TINYINT UNSIGNED DEFAULT NULL,
    eat_out_frequency     TINYINT UNSIGNED DEFAULT NULL,
    snacking              VARCHAR(20)   DEFAULT '',
    skip_meals            VARCHAR(100)  DEFAULT '',
    first_meal_time       TIME          DEFAULT NULL,
    last_meal_time        TIME          DEFAULT NULL,
    consistent_times      VARCHAR(20)   DEFAULT '',
    meal_prep             VARCHAR(30)   DEFAULT '',

    -- Dietary
    diet_type             VARCHAR(30)   DEFAULT '',

    -- Food frequencies
    freq_chicken          VARCHAR(20)   DEFAULT '',
    freq_red_meat         VARCHAR(20)   DEFAULT '',
    freq_fish             VARCHAR(20)   DEFAULT '',
    freq_eggs             VARCHAR(20)   DEFAULT '',
    freq_plant_protein    VARCHAR(20)   DEFAULT '',
    freq_milk             VARCHAR(20)   DEFAULT '',
    freq_cheese           VARCHAR(20)   DEFAULT '',
    freq_yogurt           VARCHAR(20)   DEFAULT '',
    freq_plant_milk       VARCHAR(20)   DEFAULT '',
    freq_whole_grains     VARCHAR(20)   DEFAULT '',
    freq_refined_grains   VARCHAR(20)   DEFAULT '',
    freq_sugar            VARCHAR(20)   DEFAULT '',
    freq_fruits           VARCHAR(20)   DEFAULT '',
    freq_vegetables       VARCHAR(20)   DEFAULT '',
    freq_leafy            VARCHAR(20)   DEFAULT '',
    freq_fried            VARCHAR(20)   DEFAULT '',
    freq_fast_food        VARCHAR(20)   DEFAULT '',
    freq_nuts             VARCHAR(20)   DEFAULT '',
    freq_oils             VARCHAR(20)   DEFAULT '',

    -- Hydration
    water_intake          VARCHAR(20)   DEFAULT '',
    sugary_drinks         VARCHAR(20)   DEFAULT '',
    caffeine              VARCHAR(20)   DEFAULT '',
    alcohol               VARCHAR(20)   DEFAULT '',

    -- Clinical
    diagnoses             TEXT,
    other_diagnosis       VARCHAR(255)  DEFAULT '',
    medications           TEXT,
    supplements           TEXT,

    -- Symptoms
    symptoms              TEXT,
    chew_swallow          VARCHAR(30)   DEFAULT '',

    -- Behavioral
    food_relationship     VARCHAR(30)   DEFAULT '',
    stress_eating         VARCHAR(20)   DEFAULT '',
    food_guilt            VARCHAR(20)   DEFAULT '',
    current_diet          VARCHAR(200)  DEFAULT '',

    -- Sleep
    sleep_hours           VARCHAR(20)   DEFAULT '',
    rested_waking         VARCHAR(20)   DEFAULT '',
    insomnia              VARCHAR(30)   DEFAULT '',

    -- Social/environmental
    food_budget           VARCHAR(30)   DEFAULT '',
    fresh_food_access     VARCHAR(20)   DEFAULT '',
    dietary_restrictions  VARCHAR(255)  DEFAULT '',
    household_size        VARCHAR(10)   DEFAULT '',

    -- Goals
    goals                 TEXT,
    motivation            TINYINT UNSIGNED DEFAULT NULL,
    barriers              TEXT,

    -- Raw
    raw_json              MEDIUMTEXT,
    submitted_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_email       (email),
    INDEX idx_submitted   (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ── APPOINTMENTS ──────────────────────────────
CREATE TABLE IF NOT EXISTS appointments (
    id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    patient_name          VARCHAR(200)  DEFAULT '',
    email                 VARCHAR(255)  DEFAULT '',
    phone                 VARCHAR(30)   DEFAULT '',
    dietitian             VARCHAR(50)   DEFAULT '',
    preferred_date        DATE          DEFAULT NULL,
    preferred_time        VARCHAR(20)   DEFAULT '',
    session_type          VARCHAR(20)   DEFAULT '',
    notes                 TEXT,
    assessment_id         VARCHAR(50)   DEFAULT '',
    status                ENUM('pending','confirmed','cancelled','completed') DEFAULT 'pending',
    submitted_at          DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_email       (email),
    INDEX idx_dietitian   (dietitian),
    INDEX idx_date        (preferred_date),
    INDEX idx_status      (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
