-- 031: Robuste Verknüpfung "Datei → importierter Datensatz" via Import-UUID.
--
-- Bisher: bookings.import_run_id zeigt auf import_runs.id; bei Re-Import wird der Wert
-- überschrieben — Undo der ursprünglichen Datei findet die Buchung nicht mehr.
-- Außerdem konnte ein TRUNCATE der import_runs/import_files-Tabellen die Verknüpfung
-- komplett zerstören.
--
-- Neu: jede Import-Datei bekommt eine `import_uuid` (CHAR 36). Beim INSERT eines neuen
-- Datensatzes (Buchung / Kunde / aida_prebookings-Zeile) wird die UUID dauerhaft auf
-- dem Record gespeichert. Re-Imports / Updates LASSEN die UUID unverändert — die Spur
-- zum ursprünglichen Import bleibt erhalten. Undo löscht alle Records mit passender UUID.
--
-- ALTERs sind idempotent (per INFORMATION_SCHEMA-Check).

SET @dbn := DATABASE();

-- ---- bookings.import_uuid ---------------------------------------------------
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'bookings' AND COLUMN_NAME = 'import_uuid');
SET @sql := IF(@c = 0,
    'ALTER TABLE bookings ADD COLUMN import_uuid CHAR(36) DEFAULT NULL AFTER import_run_id, ADD KEY idx_bookings_import_uuid (import_uuid)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- customers.import_uuid --------------------------------------------------
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'import_uuid');
SET @sql := IF(@c = 0,
    'ALTER TABLE customers ADD COLUMN import_uuid CHAR(36) DEFAULT NULL, ADD KEY idx_customers_import_uuid (import_uuid)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- aida_prebookings.import_uuid -------------------------------------------
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'aida_prebookings' AND COLUMN_NAME = 'import_uuid');
SET @sql := IF(@c = 0,
    'ALTER TABLE aida_prebookings ADD COLUMN import_uuid CHAR(36) DEFAULT NULL, ADD KEY idx_aida_prebookings_import_uuid (import_uuid)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- files.import_uuid (für Prediction-Listen — die haben kein import_files) -
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'files' AND COLUMN_NAME = 'import_uuid');
SET @sql := IF(@c = 0,
    'ALTER TABLE files ADD COLUMN import_uuid CHAR(36) DEFAULT NULL, ADD KEY idx_files_import_uuid (import_uuid)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- import_files.import_uuid (für AIDA-Bookings — pro Datei eine UUID) -----
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA = @dbn AND TABLE_NAME = 'import_files' AND COLUMN_NAME = 'import_uuid');
SET @sql := IF(@c = 0,
    'ALTER TABLE import_files ADD COLUMN import_uuid CHAR(36) DEFAULT NULL, ADD KEY idx_import_files_import_uuid (import_uuid)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;