CREATE TABLE `supplier_api_credentials` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `client_id` varchar(100) NOT NULL,
  `secret_hash` varchar(255) NOT NULL,
  `status` enum('active','revoked') NOT NULL DEFAULT 'active',
  `last_used_at` datetime DEFAULT NULL,
  `last_used_ip` varchar(45) 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_api_client_id` (`client_id`),
  KEY `idx_supplier_api_supplier` (`supplier_id`),
  CONSTRAINT `fk_supplier_api_credentials_supplier`
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `supplier_api_credential_scopes` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `credential_id` bigint(20) UNSIGNED NOT NULL,
  `scope` varchar(100) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_supplier_api_scope` (`credential_id`,`scope`),
  KEY `idx_supplier_api_scope_credential` (`credential_id`),
  CONSTRAINT `fk_supplier_api_scope_credential`
    FOREIGN KEY (`credential_id`) REFERENCES `supplier_api_credentials` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `supplier_api_access_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `credential_id` bigint(20) UNSIGNED NOT NULL,
  `token_hash` char(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `last_used_at` datetime DEFAULT NULL,
  `last_used_ip` varchar(45) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_supplier_api_token_hash` (`token_hash`),
  KEY `idx_supplier_api_token_credential` (`credential_id`),
  KEY `idx_supplier_api_token_expires` (`expires_at`),
  CONSTRAINT `fk_supplier_api_token_credential`
    FOREIGN KEY (`credential_id`) REFERENCES `supplier_api_credentials` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `supplier_api_request_log` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) UNSIGNED DEFAULT NULL,
  `credential_id` bigint(20) UNSIGNED DEFAULT NULL,
  `method` varchar(10) NOT NULL,
  `path` varchar(500) NOT NULL,
  `status_code` smallint(5) UNSIGNED NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `request_id` varchar(64) NOT NULL,
  `duration_ms` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_supplier_api_request_id` (`request_id`),
  KEY `idx_supplier_api_log_supplier_created` (`supplier_id`,`created_at`),
  KEY `idx_supplier_api_log_credential_created` (`credential_id`,`created_at`),
  CONSTRAINT `fk_supplier_api_log_supplier`
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
    ON DELETE SET NULL,
  CONSTRAINT `fk_supplier_api_log_credential`
    FOREIGN KEY (`credential_id`) REFERENCES `supplier_api_credentials` (`id`)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `supplier_external_locations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `integration_id` bigint(20) UNSIGNED NOT NULL,
  `location_id` bigint(20) UNSIGNED NOT NULL,
  `external_location_id` varchar(255) NOT NULL,
  `external_name` varchar(255) DEFAULT NULL,
  `last_synced_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_external_location_integration_external` (`integration_id`,`external_location_id`),
  UNIQUE KEY `uq_external_location_integration_location` (`integration_id`,`location_id`),
  KEY `idx_external_location_supplier` (`supplier_id`),
  KEY `idx_external_location_location` (`location_id`),
  CONSTRAINT `fk_external_location_supplier`
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_external_location_integration`
    FOREIGN KEY (`integration_id`) REFERENCES `supplier_integrations` (`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_external_location_location`
    FOREIGN KEY (`location_id`) REFERENCES `supplier_locations` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `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 `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 `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;