منصة ذكاء اصطناعي
لصناعة العطور

-- ══ جدول المكونات الرئيسية ══ CREATE TABLE ingredients ( id uuid DEFAULT gen_random_uuid() PRIMARY KEY, name_ar text NOT NULL, name_en text NOT NULL, category text CHECK(category IN ( 'natural', 'accord', 'aroma_chemical' )) NOT NULL, note_type text CHECK(note_type IN ('top','heart','base')), max_usage_limit numeric(5,2), -- % IFRA limit ifra_safe boolean DEFAULT true, cas_number text, origin text, price_per_kg numeric(10,2), longevity smallint CHECK(longevity BETWEEN 1 AND 10), sillage smallint CHECK(sillage BETWEEN 1 AND 10), description text, created_at timestamptz DEFAULT now() );

-- ══ جدول مكونات الأكوردات (فرعي) ══ CREATE TABLE accord_items ( id uuid DEFAULT gen_random_uuid() PRIMARY KEY, accord_id uuid REFERENCES ingredients(id) ON DELETE CASCADE, ingredient_id uuid REFERENCES ingredients(id), percentage numeric(6,3) NOT NULL, -- % داخل الأكورد sort_order smallint DEFAULT 0, UNIQUE(accord_id, ingredient_id) ); -- index للأداء CREATE INDEX idx_accord_items_accord ON accord_items(accord_id); CREATE INDEX idx_ingredients_category ON ingredients(category);

-- ══ جدول التركيبات وعناصرها ══ CREATE TABLE perfume_formulas_pro ( id uuid DEFAULT gen_random_uuid() PRIMARY KEY, user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE, name text NOT NULL, total_vol_ml numeric(8,2) DEFAULT 100, created_at timestamptz DEFAULT now() ); CREATE TABLE formula_pro_items ( id uuid DEFAULT gen_random_uuid() PRIMARY KEY, formula_id uuid REFERENCES perfume_formulas_pro(id) ON DELETE CASCADE, ingredient_id uuid REFERENCES ingredients(id), percentage numeric(6,3) NOT NULL, note_type text CHECK(note_type IN ('top','heart','base')), is_unfolded boolean DEFAULT false, source_accord_id uuid REFERENCES ingredients(id) NULL ); -- RLS ALTER TABLE perfume_formulas_pro ENABLE ROW LEVEL SECURITY; CREATE POLICY "own" ON perfume_formulas_pro FOR ALL USING (auth.uid() = user_id);