supabase-schema.sql

-- Project: NutritionBot
-- Created: 2026-03-09
-- Modified: 2026-03-09 — user_id 컬럼 추가
-- Purpose: Supabase schema for storing school menu data and meal analysis logs.

-- ---------------------------------------------------------------------------
-- Extensions
-- ---------------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ---------------------------------------------------------------------------
-- Table: school_menus
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.school_menus (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ DEFAULT now(),
    user_id TEXT NOT NULL,
    date DATE NOT NULL,
    meal_type TEXT NOT NULL CHECK (meal_type IN ('breakfast', 'lunch', 'dinner')),
    menu_items JSONB NOT NULL,
    source TEXT DEFAULT 'photo_ocr',
    CONSTRAINT uq_school_menus_user_date_meal UNIQUE (user_id, date, meal_type)
);

-- ---------------------------------------------------------------------------
-- Table: meal_logs
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.meal_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ DEFAULT now(),
    user_id TEXT NOT NULL,
    date DATE NOT NULL DEFAULT CURRENT_DATE,
    meal_type TEXT NOT NULL CHECK (meal_type IN ('breakfast', 'lunch', 'dinner', 'snack')),
    menu_id UUID REFERENCES public.school_menus(id),
    status TEXT NOT NULL DEFAULT 'before_only' CHECK (status IN ('before_only', 'completed')),
    food_items JSONB NOT NULL,
    total_calories INTEGER NOT NULL,
    total_protein_g REAL NOT NULL,
    total_carbs_g REAL NOT NULL,
    total_fat_g REAL NOT NULL,
    confidence_score REAL DEFAULT 0.0 CHECK (confidence_score >= 0.0 AND confidence_score <= 1.0),
    analysis_note TEXT
);

-- ---------------------------------------------------------------------------
-- Indexes
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_meal_logs_user_date
    ON public.meal_logs (user_id, date);

-- ---------------------------------------------------------------------------
-- Row Level Security
-- ---------------------------------------------------------------------------
ALTER TABLE public.school_menus ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.meal_logs ENABLE ROW LEVEL SECURITY;