44 lines
1.9 KiB
MySQL
44 lines
1.9 KiB
MySQL
|
-- Migration 063: FDDB-Listen/Rezepte + Verknüpfung an nutrition_items
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS food_recipes (
|
||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
|
||
|
|
name_raw VARCHAR(500) NOT NULL,
|
||
|
|
name_normalized VARCHAR(500) NOT NULL,
|
||
|
|
portions NUMERIC(8,2) NOT NULL DEFAULT 1,
|
||
|
|
description TEXT,
|
||
|
|
source VARCHAR(20) NOT NULL DEFAULT 'fddb_list',
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
CONSTRAINT uq_food_recipe_profile_name UNIQUE (profile_id, name_normalized)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_food_recipes_profile ON food_recipes (profile_id);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS food_recipe_ingredients (
|
||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
recipe_id UUID NOT NULL REFERENCES food_recipes(id) ON DELETE CASCADE,
|
||
|
|
source_name_raw VARCHAR(500) NOT NULL,
|
||
|
|
source_name_normalized VARCHAR(500) NOT NULL,
|
||
|
|
quantity_raw VARCHAR(80),
|
||
|
|
quantity_g NUMERIC(10,3),
|
||
|
|
sort_order INT NOT NULL DEFAULT 0
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_food_recipe_ing_recipe ON food_recipe_ingredients (recipe_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_food_recipe_ing_norm ON food_recipe_ingredients (source_name_normalized);
|
||
|
|
|
||
|
|
ALTER TABLE nutrition_items
|
||
|
|
ADD COLUMN IF NOT EXISTS recipe_id UUID REFERENCES food_recipes(id) ON DELETE SET NULL;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_nutrition_items_recipe
|
||
|
|
ON nutrition_items (recipe_id)
|
||
|
|
WHERE recipe_id IS NOT NULL;
|
||
|
|
|
||
|
|
COMMENT ON TABLE food_recipes IS 'FDDB-Listen/Rezepte; Tagebuchzeile kann statt Einzel-BLS auf ein Rezept zeigen';
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
RAISE NOTICE 'Migration 063: FDDB recipes + nutrition_items.recipe_id';
|
||
|
|
END $$;
|