🗄️ 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=384 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',
  `calendar_sync_status` ENUM('PENDING','SYNCED','FAILED') DEFAULT 'PENDING',
  `cancellation_token` VARCHAR(512) 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`),
  CONSTRAINT `fk_appt_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `availability_cache` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `service_id` VARCHAR(32) NOT NULL,
  `slot_start` DATETIME NOT NULL,
  `slot_end` DATETIME NOT NULL,
  `source` VARCHAR(32) NOT NULL DEFAULT 'M365',
  `version` INT NOT NULL DEFAULT '1',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_avail` (`service_id`,`slot_start`,`slot_end`)
) ENGINE=InnoDB AUTO_INCREMENT=35 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=324 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_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;