🗄️ Générateur de Schéma SQL

CREATE TABLE `appointment_logs` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `booking_id` char(26) NOT NULL,
  `level` ENUM('INFO','WARN','ERROR') NOT NULL,
  `message` TEXT NOT NULL,
  `context` json DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_logs_booking` (`booking_id`)
) ENGINE=InnoDB AUTO_INCREMENT=573 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `appointments` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `booking_id` char(26) NOT NULL,
  `calendar_event_id` VARCHAR(255) DEFAULT NULL,
  `service_id` VARCHAR(32) NOT NULL,
  `customer_id` bigint NOT NULL,
  `start_datetime` DATETIME NOT NULL,
  `end_datetime` DATETIME NOT NULL,
  `status` ENUM('PENDING','CONFIRMED','CANCELED','FAILED') NOT NULL DEFAULT 'PENDING',
  `appointment_mode` ENUM('Agence','Visio','Téléphone') DEFAULT 'Agence' COMMENT 'Mode de rendez-vous: Agence (présentiel), Visio (visioconférence), Téléphone',
  `client_message` TEXT COMMENT 'Message optionnel laissé par le client lors de la réservation',
  `calendar_sync_status` ENUM('PENDING','SYNCED','FAILED') DEFAULT 'PENDING',
  `cancellation_token` VARCHAR(512) DEFAULT NULL,
  `confirmation_token` VARCHAR(64) DEFAULT NULL,
  `confirmation_token_expires_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL,
  `ical_uid` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_appt_booking` (`booking_id`),
  UNIQUE KEY `uq_ical_uid` (`ical_uid`),
  KEY `fk_appt_customer` (`customer_id`),
  KEY `idx_appt_time` (`start_datetime`,`end_datetime`),
  KEY `idx_appt_calendar` (`calendar_event_id`),
  KEY `idx_confirmation_token` (`confirmation_token`),
  KEY `idx_confirmation_expires` (`confirmation_token_expires_at`),
  KEY `idx_appointment_mode` (`appointment_mode`),
  CONSTRAINT `fk_appt_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=215 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `availability_cache` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `source` VARCHAR(50) NOT NULL DEFAULT 'M365',
  `contact_email` VARCHAR(255) DEFAULT NULL,
  `slot_datetime` DATETIME NOT NULL,
  `duration_minutes` INT NOT NULL DEFAULT '30',
  `is_available` tinyint(1) NOT NULL DEFAULT '1',
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sync_batch_id` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_slot` (`source`,`slot_datetime`,`duration_minutes`)
) ENGINE=InnoDB AUTO_INCREMENT=5294 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customers` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `first_name` VARCHAR(100) DEFAULT NULL,
  `last_name` VARCHAR(100) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_customers_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=515 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `resources` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `display_name` VARCHAR(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `provider` ENUM('ms365','google') COLLATE utf8mb4_unicode_ci NOT NULL,
  `calendar_id` VARCHAR(256) COLLATE utf8mb4_unicode_ci NOT NULL,
  `timezone` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Europe/Brussels',
  `business_hours` json DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_provider_calendar` (`provider`,`calendar_id`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sync_logs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sync_type` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Type: M365, availability_cache, etc.',
  `sync_action` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Action: start, end, error, etc.',
  `sync_batch_id` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Batch ID pour regrouper les opérations',
  `contact_email` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email du contact concerné',
  `details` json DEFAULT NULL COMMENT 'Détails spécifiques au sync (events, slots, etc.)',
  `items_processed` INT DEFAULT '0' COMMENT 'Nombre d''éléments traités',
  `items_success` INT DEFAULT '0' COMMENT 'Nombre d''éléments réussis',
  `items_failed` INT DEFAULT '0' COMMENT 'Nombre d''éléments échoués',
  `started_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Début de l''opération',
  `completed_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Fin de l''opération',
  `duration_seconds` INT DEFAULT NULL COMMENT 'Durée en secondes',
  `status` ENUM('pending','running','completed','failed','partial') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `message` TEXT COLLATE utf8mb4_unicode_ci COMMENT 'Message détaillé ou erreur',
  `error_details` json DEFAULT NULL COMMENT 'Détails des erreurs',
  `environment` VARCHAR(50) COLLATE utf8mb4_unicode_ci DEFAULT 'local' COMMENT 'alpha, beta, production, local',
  `user_agent` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'User agent de la requête',
  `source_ip` VARCHAR(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'IP source',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sync_type` (`sync_type`),
  KEY `idx_sync_batch` (`sync_batch_id`),
  KEY `idx_contact_email` (`contact_email`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_sync_type_date` (`sync_type`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=154 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Logging des synchronisations M365 et availability_cache';

CREATE TABLE `sync_state` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `provider` VARCHAR(16) NOT NULL,
  `resource` VARCHAR(128) NOT NULL,
  `sync_cursor` VARCHAR(256) DEFAULT NULL,
  `subscription_id` VARCHAR(256) DEFAULT NULL,
  `expires_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `webhook_events` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `provider` VARCHAR(16) NOT NULL,
  `event_type` VARCHAR(64) NOT NULL,
  `payload` json NOT NULL,
  `received_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;