-- Migration XXX: CSV Parser - System Templates Seed Data -- Legt Standard-Import-Konfigurationen für bekannte CSV-Formate an -- Diese Templates sind für alle User verfügbar (is_system = true, profile_id = NULL) -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- NUTRITION (Ernährung) -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- 1. FDDB Export (Deutsch) INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'nutrition', 'FDDB Export (Standard)', 'Standard-Format für FDDB.de CSV-Exporte (Deutsch). Delimiter Semikolon, kJ → kcal Konvertierung.', ARRAY['datum_tag_monat_jahr_stunde_minute', 'fett_g', 'kh_g', 'kj', 'protein_g']::TEXT[], ';', 'utf-8', true, '{ "datum_tag_monat_jahr_stunde_minute": "date", "kj": "kcal", "fett_g": "fat_g", "kh_g": "carbs_g", "protein_g": "protein_g" }'::JSONB, '{ "date": { "type": "date", "format": "dd.mm.yyyy HH:MM", "extract": "date_only" }, "kcal": { "type": "float", "source_unit": "kj", "decimal_separator": "," }, "fat_g": { "type": "float", "decimal_separator": "," }, "carbs_g": { "type": "float", "decimal_separator": "," }, "protein_g": { "type": "float", "decimal_separator": "," } }'::JSONB ); -- 2. MyFitnessPal Export (English) INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'nutrition', 'MyFitnessPal Export', 'Standard CSV export from MyFitnessPal (English)', ARRAY['Carbohydrates (g)', 'Calories', 'Date', 'Fat (g)', 'Protein (g)']::TEXT[], ',', 'utf-8', true, '{ "Date": "date", "Calories": "kcal", "Fat (g)": "fat_g", "Carbohydrates (g)": "carbs_g", "Protein (g)": "protein_g" }'::JSONB, '{ "date": { "type": "date", "format": "yyyy-mm-dd" }, "kcal": { "type": "float", "decimal_separator": "." }, "fat_g": { "type": "float", "decimal_separator": "." }, "carbs_g": { "type": "float", "decimal_separator": "." }, "protein_g": { "type": "float", "decimal_separator": "." } }'::JSONB ); -- 3. Cronometer Export INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'nutrition', 'Cronometer Export', 'Cronometer daily nutrition export (English)', ARRAY['Day', 'Energy (kcal)', 'Fat (g)', 'Net Carbs (g)', 'Protein (g)']::TEXT[], ',', 'utf-8', true, '{ "Day": "date", "Energy (kcal)": "kcal", "Fat (g)": "fat_g", "Net Carbs (g)": "carbs_g", "Protein (g)": "protein_g" }'::JSONB, '{ "date": { "type": "date", "format": "yyyy-mm-dd" }, "kcal": {"type": "float", "decimal_separator": "."}, "fat_g": {"type": "float", "decimal_separator": "."}, "carbs_g": {"type": "float", "decimal_separator": "."}, "protein_g": {"type": "float", "decimal_separator": "."} }'::JSONB ); -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- ACTIVITY (Aktivität) -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- 1. Apple Health Workout Export (English) INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'activity', 'Apple Health Workout Export (English)', 'Apple Health CSV-Export für Workouts (English). Automatisches Training-Type-Mapping.', ARRAY['Active Energy (kcal)', 'Distance (km)', 'Duration', 'End', 'Heart Rate Average (bpm)', 'Start', 'Workout Type']::TEXT[], ',', 'utf-8', true, '{ "Workout Type": "activity_type", "Start": "start_time", "End": "end_time", "Duration": "duration_min", "Distance (km)": "distance_km", "Active Energy (kcal)": "kcal_active", "Heart Rate Average (bpm)": "hr_avg" }'::JSONB, '{ "start_time": { "type": "datetime", "format": "yyyy-mm-dd HH:MM:SS", "extract": "date_and_time" }, "end_time": { "type": "datetime", "format": "yyyy-mm-dd HH:MM:SS" }, "duration_min": { "type": "duration", "format": "HH:MM:SS", "target_unit": "minutes" }, "distance_km": { "type": "float", "decimal_separator": "." }, "kcal_active": { "type": "float", "decimal_separator": "." }, "hr_avg": { "type": "int" } }'::JSONB ); -- 2. Apple Health Workout Export (Deutsch) INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'activity', 'Apple Health Workout Export (Deutsch)', 'Apple Health CSV-Export für Workouts (Deutsch). Automatisches Training-Type-Mapping.', ARRAY['Aktive Energie (kcal)', 'Dauer', 'Durchschnittliche Herzfrequenz (bpm)', 'Ende', 'Start', 'Strecke (km)', 'Trainingsart']::TEXT[], ',', 'utf-8', true, '{ "Trainingsart": "activity_type", "Start": "start_time", "Ende": "end_time", "Dauer": "duration_min", "Strecke (km)": "distance_km", "Aktive Energie (kcal)": "kcal_active", "Durchschnittliche Herzfrequenz (bpm)": "hr_avg" }'::JSONB, '{ "start_time": { "type": "datetime", "format": "yyyy-mm-dd HH:MM:SS", "extract": "date_and_time" }, "end_time": { "type": "datetime", "format": "yyyy-mm-dd HH:MM:SS" }, "duration_min": { "type": "duration", "format": "HH:MM:SS", "target_unit": "minutes" }, "distance_km": { "type": "float", "decimal_separator": "," }, "kcal_active": { "type": "float", "decimal_separator": "," }, "hr_avg": { "type": "int" } }'::JSONB ); -- 3. Garmin Connect Export INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'activity', 'Garmin Connect Export', 'Garmin Connect activity CSV export (English)', ARRAY['Activity Type', 'Avg HR', 'Calories', 'Date', 'Distance', 'Duration', 'Time']::TEXT[], ',', 'utf-8', true, '{ "Activity Type": "activity_type", "Date": "date", "Time": "start_time", "Duration": "duration_min", "Distance": "distance_km", "Calories": "kcal_active", "Avg HR": "hr_avg" }'::JSONB, '{ "date": { "type": "date", "format": "yyyy-mm-dd" }, "start_time": { "type": "time", "format": "HH:MM:SS" }, "duration_min": { "type": "duration", "format": "HH:MM:SS", "target_unit": "minutes" }, "distance_km": { "type": "float", "decimal_separator": "." }, "kcal_active": { "type": "float", "decimal_separator": "." }, "hr_avg": { "type": "int" } }'::JSONB ); -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- BLOOD PRESSURE (Blutdruck) -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- 1. Omron Export (Deutsch) INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'blood_pressure', 'Omron Export (Deutsch)', 'Omron Blutdruckmessgerät CSV-Export (Deutsch)', ARRAY['Datum', 'Diastolisch (mmHg)', 'Puls (bpm)', 'Systolisch (mmHg)', 'Zeit']::TEXT[], ',', 'utf-8', true, '{ "Datum": "measured_date", "Zeit": "measured_time", "Systolisch (mmHg)": "systolic", "Diastolisch (mmHg)": "diastolic", "Puls (bpm)": "pulse" }'::JSONB, '{ "measured_date": { "type": "date", "format": "dd.mm.yyyy" }, "measured_time": { "type": "time", "format": "HH:MM" }, "systolic": {"type": "int"}, "diastolic": {"type": "int"}, "pulse": {"type": "int"} }'::JSONB ); -- 2. Omron Export (English) INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'blood_pressure', 'Omron Export (English)', 'Omron blood pressure monitor CSV export (English)', ARRAY['Date', 'Diastolic (mmHg)', 'Pulse (bpm)', 'Systolic (mmHg)', 'Time']::TEXT[], ',', 'utf-8', true, '{ "Date": "measured_date", "Time": "measured_time", "Systolic (mmHg)": "systolic", "Diastolic (mmHg)": "diastolic", "Pulse (bpm)": "pulse" }'::JSONB, '{ "measured_date": { "type": "date", "format": "mm/dd/yyyy" }, "measured_time": { "type": "time", "format": "HH:MM" }, "systolic": {"type": "int"}, "diastolic": {"type": "int"}, "pulse": {"type": "int"} }'::JSONB ); -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- WEIGHT (Gewicht) -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- 1. Apple Health Weight Export INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'weight', 'Apple Health Weight Export', 'Apple Health body mass CSV export', ARRAY['Body Mass (kg)', 'Start']::TEXT[], ',', 'utf-8', true, '{ "Start": "date", "Body Mass (kg)": "weight" }'::JSONB, '{ "date": { "type": "datetime", "format": "yyyy-mm-dd HH:MM:SS", "extract": "date_only" }, "weight": { "type": "float", "decimal_separator": "." } }'::JSONB ); -- 2. Withings Export INSERT INTO csv_field_mappings ( profile_id, is_system, module, mapping_name, description, column_signature, delimiter, encoding, has_header, field_mappings, type_conversions ) VALUES ( NULL, true, 'weight', 'Withings Export', 'Withings smart scale CSV export (weight, body fat, muscle mass)', ARRAY['Body Fat (%)', 'Date', 'Muscle Mass (kg)', 'Weight (kg)']::TEXT[], ',', 'utf-8', true, '{ "Date": "date", "Weight (kg)": "weight" }'::JSONB, '{ "date": { "type": "date", "format": "yyyy-mm-dd" }, "weight": { "type": "float", "decimal_separator": "." } }'::JSONB ); -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ -- SUMMARY -- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ DO $$ DECLARE template_count INTEGER; BEGIN SELECT COUNT(*) INTO template_count FROM csv_field_mappings WHERE is_system = true; RAISE NOTICE '✓ CSV Parser: % System-Templates created', template_count; RAISE NOTICE ' - Nutrition: 3 (FDDB, MyFitnessPal, Cronometer)'; RAISE NOTICE ' - Activity: 3 (Apple Health DE/EN, Garmin)'; RAISE NOTICE ' - Blood Pressure: 2 (Omron DE/EN)'; RAISE NOTICE ' - Weight: 2 (Apple Health, Withings)'; END $$;