CREATE TABLE IF NOT EXISTS `supplier_webhook_endpoints` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `endpoint_id` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `url` varchar(1000) NOT NULL,
  `secret_encrypted` text NOT NULL,
  `secret_last_four` char(4) NOT NULL,
  `status` enum('active','disabled') NOT NULL DEFAULT 'active',
  `last_delivery_at` datetime DEFAULT NULL,
  `last_success_at` datetime DEFAULT NULL,
  `last_failure_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_supplier_webhook_endpoint_id` (`endpoint_id`),
  KEY `idx_supplier_webhook_supplier_status` (`supplier_id`,`status`),
  CONSTRAINT `fk_supplier_webhook_endpoint_supplier`
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_webhook_subscriptions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `webhook_endpoint_id` bigint(20) UNSIGNED NOT NULL,
  `event_type` varchar(100) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_supplier_webhook_subscription` (`webhook_endpoint_id`,`event_type`),
  KEY `idx_supplier_webhook_subscription_endpoint` (`webhook_endpoint_id`),
  CONSTRAINT `fk_supplier_webhook_subscription_endpoint`
    FOREIGN KEY (`webhook_endpoint_id`) REFERENCES `supplier_webhook_endpoints` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_webhook_deliveries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `webhook_endpoint_id` bigint(20) UNSIGNED NOT NULL,
  `event_id` varchar(100) NOT NULL,
  `event_type` varchar(100) NOT NULL,
  `payload_json` longtext NOT NULL,
  `status` enum('pending','delivering','delivered','failed') NOT NULL DEFAULT 'pending',
  `attempt_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `response_code` smallint(5) UNSIGNED DEFAULT NULL,
  `response_body` text DEFAULT NULL,
  `last_attempt_at` datetime DEFAULT NULL,
  `next_attempt_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_supplier_webhook_delivery_event_endpoint` (`event_id`,`webhook_endpoint_id`),
  KEY `idx_supplier_webhook_delivery_queue` (`status`,`next_attempt_at`),
  KEY `idx_supplier_webhook_delivery_supplier_created` (`supplier_id`,`created_at`),
  CONSTRAINT `fk_supplier_webhook_delivery_supplier`
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_supplier_webhook_delivery_endpoint`
    FOREIGN KEY (`webhook_endpoint_id`) REFERENCES `supplier_webhook_endpoints` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;