robin
b2a1d292da
- Implemented reusable TagSelector component with i18n support - Added tag weighting system (popularity scores 1-5) - Created admin panel for tag management - Integrated Nebius AI and Brave Search for 'Magic Scan' - Refactored app focus: removed bottle status, updated counters, and displayed extended bottle details - Updated i18n for German and English - Added database migration scripts
162 lines
6.8 KiB
SQL
162 lines
6.8 KiB
SQL
-- Migration for Advanced Tagging System
|
|
|
|
-- 1. Rename existing 'tasting_tags' to 'tasting_buddies'
|
|
-- This table currently stores which buddies are part of a tasting.
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'tasting_tags') THEN
|
|
ALTER TABLE tasting_tags RENAME TO tasting_buddies;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 2. Create 'tags' table
|
|
CREATE TYPE tag_category AS ENUM ('nose', 'taste', 'finish', 'texture');
|
|
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
category tag_category NOT NULL,
|
|
is_system_default BOOLEAN DEFAULT false,
|
|
popularity_score INTEGER DEFAULT 3, -- 1 to 5
|
|
created_by UUID REFERENCES profiles(id) ON DELETE CASCADE,
|
|
UNIQUE(name, category)
|
|
);
|
|
|
|
-- 3. Create new 'tasting_tags' junction table (for aroma tags)
|
|
CREATE TABLE IF NOT EXISTS tasting_tags (
|
|
tasting_id UUID REFERENCES tastings(id) ON DELETE CASCADE NOT NULL,
|
|
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE NOT NULL,
|
|
user_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
|
|
PRIMARY KEY (tasting_id, tag_id)
|
|
);
|
|
|
|
-- 4. Enable RLS
|
|
ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE tasting_tags ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 5. Policies for 'tags'
|
|
-- Everyone can read system default tags and their own custom tags
|
|
DROP POLICY IF EXISTS "tags_select_policy" ON tags;
|
|
CREATE POLICY "tags_select_policy" ON tags
|
|
FOR SELECT USING (
|
|
is_system_default = true OR
|
|
(SELECT auth.uid()) = created_by OR
|
|
EXISTS (SELECT 1 FROM admin_users WHERE user_id = (SELECT auth.uid()))
|
|
);
|
|
|
|
-- Users can insert their own custom tags
|
|
DROP POLICY IF EXISTS "tags_insert_policy" ON tags;
|
|
CREATE POLICY "tags_insert_policy" ON tags
|
|
FOR INSERT WITH CHECK (
|
|
(SELECT auth.uid()) = created_by
|
|
);
|
|
|
|
-- 6. Policies for 'tasting_tags' (junction)
|
|
DROP POLICY IF EXISTS "tasting_tags_owner_policy" ON tasting_tags;
|
|
CREATE POLICY "tasting_tags_owner_policy" ON tasting_tags
|
|
FOR ALL USING ((SELECT auth.uid()) = user_id);
|
|
|
|
-- 7. Insert Master List of System Tags
|
|
INSERT INTO tags (name, category, is_system_default) VALUES
|
|
-- FRUCHTIG (FRUITY)
|
|
('Apfel', 'nose', true), ('Apfel', 'taste', true),
|
|
('Grüner Apfel', 'nose', true), ('Grüner Apfel', 'taste', true),
|
|
('Bratapfel', 'nose', true), ('Bratapfel', 'taste', true),
|
|
('Birne', 'nose', true), ('Birne', 'taste', true),
|
|
('Zitrone', 'nose', true), ('Zitrone', 'taste', true),
|
|
('Zitrus', 'nose', true), ('Zitrus', 'taste', true),
|
|
('Orange', 'nose', true), ('Orange', 'taste', true),
|
|
('Orangenschale', 'nose', true), ('Orangenschale', 'taste', true),
|
|
('Pfirsich', 'nose', true), ('Pfirsich', 'taste', true),
|
|
('Aprikose', 'nose', true), ('Aprikose', 'taste', true),
|
|
('Banane', 'nose', true), ('Banane', 'taste', true),
|
|
('Ananas', 'nose', true), ('Ananas', 'taste', true),
|
|
('Tropische Früchte', 'nose', true), ('Tropische Früchte', 'taste', true),
|
|
('Kirsche', 'nose', true), ('Kirsche', 'taste', true),
|
|
('Beeren', 'nose', true), ('Beeren', 'taste', true),
|
|
('Brombeere', 'nose', true), ('Brombeere', 'taste', true),
|
|
('Himbeere', 'nose', true), ('Himbeere', 'taste', true),
|
|
('Pflaume', 'nose', true), ('Pflaume', 'taste', true),
|
|
('Trockenfrüchte', 'nose', true), ('Trockenfrüchte', 'taste', true),
|
|
('Rosinen', 'nose', true), ('Rosinen', 'taste', true),
|
|
('Datteln', 'nose', true), ('Datteln', 'taste', true),
|
|
('Feigen', 'nose', true), ('Feigen', 'taste', true),
|
|
|
|
-- SÜSS & CREMIG (SWEET & CREAMY)
|
|
('Vanille', 'nose', true), ('Vanille', 'taste', true),
|
|
('Honig', 'nose', true), ('Honig', 'taste', true),
|
|
('Karamell', 'nose', true), ('Karamell', 'taste', true),
|
|
('Toffee', 'nose', true), ('Toffee', 'taste', true),
|
|
('Schokolade', 'nose', true), ('Schokolade', 'taste', true),
|
|
('Zartbitterschokolade', 'nose', true), ('Zartbitterschokolade', 'taste', true),
|
|
('Milchschokolade', 'nose', true), ('Milchschokolade', 'taste', true),
|
|
('Malz', 'nose', true), ('Malz', 'taste', true),
|
|
('Müsli', 'nose', true), ('Müsli', 'taste', true),
|
|
('Butter', 'nose', true), ('Butter', 'taste', true),
|
|
('Butterkeks', 'nose', true), ('Butterkeks', 'taste', true),
|
|
('Marzipan', 'nose', true), ('Marzipan', 'taste', true),
|
|
('Mandel', 'nose', true), ('Mandel', 'taste', true),
|
|
('Sahnebonbon', 'nose', true), ('Sahnebonbon', 'taste', true),
|
|
|
|
-- WÜRZIG & NUSSIG (SPICY & NUTTY)
|
|
('Eiche', 'nose', true), ('Eiche', 'taste', true),
|
|
('Zimt', 'nose', true), ('Zimt', 'taste', true),
|
|
('Pfeffer', 'nose', true), ('Pfeffer', 'taste', true),
|
|
('Muskatnuss', 'nose', true), ('Muskatnuss', 'taste', true),
|
|
('Ingwer', 'nose', true), ('Ingwer', 'taste', true),
|
|
('Nelke', 'nose', true), ('Nelke', 'taste', true),
|
|
('Walnuss', 'nose', true), ('Walnuss', 'taste', true),
|
|
('Haselnuss', 'nose', true), ('Haselnuss', 'taste', true),
|
|
('Geröstete Nüsse', 'nose', true), ('Geröstete Nüsse', 'taste', true),
|
|
|
|
-- RAUCHIG & TORFIG (PEATY & SMOKY)
|
|
('Lagerfeuer', 'nose', true), ('Lagerfeuer', 'taste', true),
|
|
('Holzkohle', 'nose', true), ('Holzkohle', 'taste', true),
|
|
('Torfrauch', 'nose', true), ('Torfrauch', 'taste', true),
|
|
('Asche', 'nose', true), ('Asche', 'taste', true),
|
|
('Jod', 'nose', true), ('Jod', 'taste', true),
|
|
('Medizinisch', 'nose', true), ('Medizinisch', 'taste', true),
|
|
('Teer', 'nose', true), ('Teer', 'taste', true),
|
|
('Asphalt', 'nose', true), ('Asphalt', 'taste', true),
|
|
('Geräucherter Schinken', 'nose', true), ('Geräucherter Schinken', 'taste', true),
|
|
('Speck', 'nose', true), ('Speck', 'taste', true),
|
|
('Grillfleisch', 'nose', true), ('Grillfleisch', 'taste', true),
|
|
|
|
-- MARITIM & SALZIG (COASTAL)
|
|
('Meersalz', 'nose', true), ('Meersalz', 'taste', true),
|
|
('Salzlake', 'nose', true), ('Salzlake', 'taste', true),
|
|
('Seetang', 'nose', true), ('Seetang', 'taste', true),
|
|
('Algen', 'nose', true), ('Algen', 'taste', true),
|
|
('Austern', 'nose', true), ('Austern', 'taste', true),
|
|
('Meeresbrise', 'nose', true), ('Meeresbrise', 'taste', true),
|
|
|
|
-- FLORAL & KRÄUTER (FLORAL & HERBAL)
|
|
('Heidekraut', 'nose', true), ('Heidekraut', 'taste', true),
|
|
('Gras', 'nose', true), ('Gras', 'taste', true),
|
|
('Heu', 'nose', true), ('Heu', 'taste', true),
|
|
('Minze', 'nose', true), ('Minze', 'taste', true),
|
|
('Menthol', 'nose', true), ('Menthol', 'taste', true),
|
|
('Eukalyptus', 'nose', true), ('Eukalyptus', 'taste', true),
|
|
('Tabak', 'nose', true), ('Tabak', 'taste', true),
|
|
('Leder', 'nose', true), ('Leder', 'taste', true),
|
|
('Tee', 'nose', true), ('Tee', 'taste', true),
|
|
|
|
-- FINISH DAUER
|
|
('Kurz & Knackig', 'finish', true),
|
|
('Mittellang', 'finish', true),
|
|
('Lang anhaltend', 'finish', true),
|
|
('Ewig', 'finish', true),
|
|
|
|
-- TEXTUR & GEFÜHL
|
|
('Ölig', 'texture', true),
|
|
('Viskos', 'texture', true),
|
|
('Trocken', 'texture', true),
|
|
('Adstringierend', 'texture', true),
|
|
('Wärmend', 'texture', true),
|
|
('Scharf', 'texture', true),
|
|
('Beißend', 'texture', true),
|
|
('Weich', 'texture', true),
|
|
('Samtig', 'texture', true),
|
|
('Wässrig', 'texture', true)
|
|
ON CONFLICT (name, category) DO NOTHING;
|