mitai-jinkendo/backend/schema.sql

-- ================================================================
-- MITAI JINKENDO v9b – PostgreSQL Schema
-- ================================================================
-- Migration from SQLite to PostgreSQL
-- Includes v9b Tier System features
-- ================================================================

-- Enable UUID Extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ================================================================
-- CORE TABLES
-- ================================================================

-- ── Profiles Table ──────────────────────────────────────────────
-- User/Profile management with auth and permissions
CREATE TABLE IF NOT EXISTS profiles (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name            VARCHAR(255) NOT NULL DEFAULT 'Nutzer',
    avatar_color    VARCHAR(7) DEFAULT '#1D9E75',
    photo_id        UUID,
    sex             VARCHAR(1) DEFAULT 'm' CHECK (sex IN ('m', 'w', 'd')),
    dob             DATE,
    height          NUMERIC(5,2) DEFAULT 178,
    goal_weight     NUMERIC(5,2),
    goal_bf_pct     NUMERIC(4,2),

    -- Auth & Permissions
    role            VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin')),
    pin_hash        TEXT,
    auth_type       VARCHAR(20) DEFAULT 'pin' CHECK (auth_type IN ('pin', 'email')),
    session_days    INTEGER DEFAULT 30,
    ai_enabled      BOOLEAN DEFAULT TRUE,
    ai_limit_day    INTEGER,
    export_enabled  BOOLEAN DEFAULT TRUE,
    email           VARCHAR(255) UNIQUE,

    -- v9b: Tier System
    tier                VARCHAR(20) DEFAULT 'free' CHECK (tier IN ('free', 'basic', 'premium', 'selfhosted')),
    tier_expires_at     TIMESTAMP WITH TIME ZONE,
    trial_ends_at       TIMESTAMP WITH TIME ZONE,
    invited_by          UUID REFERENCES profiles(id),

    -- Timestamps
    created         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_profiles_email ON profiles(email) WHERE email IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_profiles_tier ON profiles(tier);

-- ── Sessions Table ──────────────────────────────────────────────
-- Auth token management
CREATE TABLE IF NOT EXISTS sessions (
    token       VARCHAR(64) PRIMARY KEY,
    profile_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    expires_at  TIMESTAMP WITH TIME ZONE NOT NULL,
    created     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_sessions_profile_id ON sessions(profile_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);

-- ── AI Usage Tracking ───────────────────────────────────────────
-- Daily AI call limits per profile
CREATE TABLE IF NOT EXISTS ai_usage (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    date        DATE NOT NULL,
    call_count  INTEGER DEFAULT 0,
    UNIQUE(profile_id, date)
);

CREATE INDEX IF NOT EXISTS idx_ai_usage_profile_date ON ai_usage(profile_id, date);

-- ================================================================
-- TRACKING TABLES
-- ================================================================

-- ── Weight Log ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS weight_log (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    date        DATE NOT NULL,
    weight      NUMERIC(5,2) NOT NULL,
    note        TEXT,
    source      VARCHAR(20) DEFAULT 'manual',
    created     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_weight_log_profile_date ON weight_log(profile_id, date DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_weight_log_profile_date_unique ON weight_log(profile_id, date);

-- ── Circumference Log ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS circumference_log (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    date        DATE NOT NULL,
    c_neck      NUMERIC(5,2),
    c_chest     NUMERIC(5,2),
    c_waist     NUMERIC(5,2),
    c_belly     NUMERIC(5,2),
    c_hip       NUMERIC(5,2),
    c_thigh     NUMERIC(5,2),
    c_calf      NUMERIC(5,2),
    c_arm       NUMERIC(5,2),
    c_arm_relaxed NUMERIC(5,2),
    notes       TEXT,
    photo_id    UUID,
    created     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_circumference_profile_date ON circumference_log(profile_id, date DESC);

-- ── Caliper Log ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS caliper_log (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id      UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    date            DATE NOT NULL,
    sf_method       VARCHAR(20) DEFAULT 'jackson3',
    sf_chest        NUMERIC(5,2),
    sf_axilla       NUMERIC(5,2),
    sf_triceps      NUMERIC(5,2),
    sf_subscap      NUMERIC(5,2),
    sf_suprailiac   NUMERIC(5,2),
    sf_abdomen      NUMERIC(5,2),
    sf_thigh        NUMERIC(5,2),
    sf_calf_med     NUMERIC(5,2),
    sf_lowerback    NUMERIC(5,2),
    sf_biceps       NUMERIC(5,2),
    body_fat_pct    NUMERIC(4,2),
    lean_mass       NUMERIC(5,2),
    fat_mass        NUMERIC(5,2),
    notes           TEXT,
    created         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_caliper_profile_date ON caliper_log(profile_id, date DESC);

-- ── Nutrition Log ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS nutrition_log (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    date        DATE NOT NULL,
    kcal        NUMERIC(7,2),
    protein_g   NUMERIC(6,2),
    fat_g       NUMERIC(6,2),
    carbs_g     NUMERIC(6,2),
    source      VARCHAR(20) DEFAULT 'csv',
    created     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_nutrition_profile_date ON nutrition_log(profile_id, date DESC);

-- ── Activity Log ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS activity_log (
    id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id      UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    date            DATE NOT NULL,
    start_time      TIME,
    end_time        TIME,
    activity_type   VARCHAR(50) NOT NULL,
    duration_min    NUMERIC(6,2),
    kcal_active     NUMERIC(12,2),
    kcal_resting    NUMERIC(12,2),
    hr_avg          NUMERIC(6,2),
    hr_max          NUMERIC(6,2),
    distance_km     NUMERIC(7,2),
    rpe             INTEGER CHECK (rpe >= 1 AND rpe <= 10),
    source          VARCHAR(20) DEFAULT 'manual',
    notes           TEXT,
    created         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_activity_profile_date ON activity_log(profile_id, date DESC);

-- ── Photos ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS photos (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    meas_id     UUID,  -- Legacy: reference to measurement (circumference/caliper)
    date        DATE,
    path        TEXT NOT NULL,
    taken_at    TIMESTAMPTZ,  -- Aufnahmezeit aus EXIF (optional)
    created     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_photos_profile_date ON photos(profile_id, date DESC);

-- ================================================================
-- AI TABLES
-- ================================================================

-- ── AI Insights ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ai_insights (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    profile_id  UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    scope       VARCHAR(50) NOT NULL,
    content     TEXT NOT NULL,
    created     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_ai_insights_profile_scope ON ai_insights(profile_id, scope, created DESC);

-- ── AI Prompts ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ai_prompts (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    template    TEXT NOT NULL,
    active      BOOLEAN DEFAULT TRUE,
    sort_order  INTEGER DEFAULT 0,
    created     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_ai_prompts_slug ON ai_prompts(slug);
CREATE INDEX IF NOT EXISTS idx_ai_prompts_active_sort ON ai_prompts(active, sort_order);

-- ================================================================
-- TRIGGERS
-- ================================================================

-- Auto-update timestamp trigger for profiles
CREATE OR REPLACE FUNCTION update_updated_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_profiles_updated ON profiles;
CREATE TRIGGER trigger_profiles_updated
    BEFORE UPDATE ON profiles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_timestamp();

DROP TRIGGER IF EXISTS trigger_ai_prompts_updated ON ai_prompts;
CREATE TRIGGER trigger_ai_prompts_updated
    BEFORE UPDATE ON ai_prompts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_timestamp();

-- ================================================================
-- COMMENTS (Documentation)
-- ================================================================

COMMENT ON TABLE profiles IS 'User profiles with auth, permissions, and tier system';
COMMENT ON TABLE sessions IS 'Active auth tokens';
COMMENT ON TABLE ai_usage IS 'Daily AI call tracking per profile';
COMMENT ON TABLE weight_log IS 'Weight measurements';
COMMENT ON TABLE circumference_log IS 'Body circumference measurements (8 points)';
COMMENT ON TABLE caliper_log IS 'Skinfold measurements with body fat calculations';
COMMENT ON TABLE nutrition_log IS 'Daily nutrition intake (calories + macros)';
COMMENT ON TABLE activity_log IS 'Training sessions and activities';
COMMENT ON TABLE photos IS 'Progress photos';
COMMENT ON TABLE ai_insights IS 'AI-generated analysis results';
COMMENT ON TABLE ai_prompts IS 'Configurable AI prompt templates';

COMMENT ON COLUMN profiles.tier IS 'Subscription tier: free, basic, premium, selfhosted';
COMMENT ON COLUMN profiles.trial_ends_at IS 'Trial expiration timestamp (14 days from registration)';
COMMENT ON COLUMN profiles.tier_expires_at IS 'Paid tier expiration timestamp';
COMMENT ON COLUMN profiles.invited_by IS 'Profile ID of inviter (for beta invitations)';