SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `system_public_id` char(26) DEFAULT NULL,
  `name` varchar(180) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_accounts_public_id_unique` (`public_id`),
  UNIQUE KEY `api_accounts_system_public_id_unique` (`system_public_id`),
  KEY `api_accounts_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `businesses` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `system_public_id` char(26) DEFAULT NULL,
  `api_account_id` bigint unsigned NOT NULL,
  `legal_name` varchar(180) NOT NULL,
  `commercial_name` varchar(180) DEFAULT NULL,
  `identification_type` char(2) NOT NULL,
  `identification_number` varchar(20) NOT NULL,
  `default_environment` varchar(20) NOT NULL DEFAULT 'sandbox',
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `businesses_public_id_unique` (`public_id`),
  UNIQUE KEY `businesses_system_public_id_unique` (`system_public_id`),
  UNIQUE KEY `business_identity_unique` (`api_account_id`,`identification_type`,`identification_number`),
  KEY `businesses_status_index` (`status`),
  CONSTRAINT `businesses_api_account_id_foreign` FOREIGN KEY (`api_account_id`) REFERENCES `api_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `branches` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `system_public_id` char(26) DEFAULT NULL,
  `business_id` bigint unsigned NOT NULL,
  `code` char(3) NOT NULL,
  `name` varchar(120) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `branches_public_id_unique` (`public_id`),
  UNIQUE KEY `branches_system_public_id_unique` (`system_public_id`),
  UNIQUE KEY `branches_business_code_unique` (`business_id`,`code`),
  KEY `branches_status_index` (`status`),
  CONSTRAINT `branches_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `terminals` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `system_public_id` char(26) DEFAULT NULL,
  `branch_id` bigint unsigned NOT NULL,
  `code` char(5) NOT NULL,
  `name` varchar(120) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `terminals_public_id_unique` (`public_id`),
  UNIQUE KEY `terminals_system_public_id_unique` (`system_public_id`),
  UNIQUE KEY `terminals_branch_code_unique` (`branch_id`,`code`),
  KEY `terminals_status_index` (`status`),
  CONSTRAINT `terminals_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_credentials` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `key_id` char(26) NOT NULL,
  `label` varchar(120) NOT NULL,
  `api_account_id` bigint unsigned NOT NULL,
  `business_id` bigint unsigned DEFAULT NULL,
  `branch_id` bigint unsigned DEFAULT NULL,
  `terminal_id` bigint unsigned DEFAULT NULL,
  `environment` varchar(20) NOT NULL,
  `secret_hash` char(64) NOT NULL,
  `fingerprint` char(16) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `expires_at` timestamp NULL DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `created_by_system_user` char(26) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_credentials_public_id_unique` (`public_id`),
  UNIQUE KEY `api_credentials_key_id_unique` (`key_id`),
  KEY `api_credentials_environment_index` (`environment`),
  KEY `api_credentials_fingerprint_index` (`fingerprint`),
  KEY `api_credentials_status_index` (`status`),
  KEY `api_credentials_expires_at_index` (`expires_at`),
  KEY `api_credentials_created_by_system_user_index` (`created_by_system_user`),
  CONSTRAINT `api_credentials_api_account_id_foreign` FOREIGN KEY (`api_account_id`) REFERENCES `api_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `api_credentials_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `api_credentials_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `api_credentials_terminal_id_foreign` FOREIGN KEY (`terminal_id`) REFERENCES `terminals` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_credential_scopes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `api_credential_id` bigint unsigned NOT NULL,
  `scope` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `api_credential_scope_unique` (`api_credential_id`,`scope`),
  CONSTRAINT `api_credential_scopes_credential_foreign` FOREIGN KEY (`api_credential_id`) REFERENCES `api_credentials` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sequence_counters` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `business_id` bigint unsigned NOT NULL,
  `branch_id` bigint unsigned NOT NULL,
  `terminal_id` bigint unsigned NOT NULL,
  `document_type` char(2) NOT NULL,
  `environment` varchar(20) NOT NULL,
  `next_number` bigint unsigned NOT NULL DEFAULT 1,
  `version` bigint unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sequence_counters_public_id_unique` (`public_id`),
  UNIQUE KEY `sequence_scope_unique` (`business_id`,`branch_id`,`terminal_id`,`document_type`,`environment`),
  CONSTRAINT `sequence_counters_business_foreign` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sequence_counters_branch_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sequence_counters_terminal_foreign` FOREIGN KEY (`terminal_id`) REFERENCES `terminals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sequence_leases` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `sequence_counter_id` bigint unsigned NOT NULL,
  `api_credential_id` bigint unsigned NOT NULL,
  `idempotency_key` varchar(128) DEFAULT NULL,
  `start_number` bigint unsigned NOT NULL,
  `end_number` bigint unsigned NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `expires_at` timestamp NOT NULL,
  `reconciled_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sequence_leases_public_id_unique` (`public_id`),
  KEY `sequence_lease_lookup` (`sequence_counter_id`,`status`,`expires_at`),
  UNIQUE KEY `sequence_lease_idempotency_unique` (`api_credential_id`,`idempotency_key`),
  CONSTRAINT `sequence_leases_counter_foreign` FOREIGN KEY (`sequence_counter_id`) REFERENCES `sequence_counters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sequence_leases_credential_foreign` FOREIGN KEY (`api_credential_id`) REFERENCES `api_credentials` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sequence_allocations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `sequence_counter_id` bigint unsigned NOT NULL,
  `sequence_lease_id` bigint unsigned DEFAULT NULL,
  `api_credential_id` bigint unsigned DEFAULT NULL,
  `sequence_number` bigint unsigned NOT NULL,
  `consecutive` char(20) NOT NULL,
  `source` varchar(20) NOT NULL DEFAULT 'online',
  `idempotency_key` varchar(128) DEFAULT NULL,
  `external_reference` varchar(120) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sequence_allocations_public_id_unique` (`public_id`),
  UNIQUE KEY `sequence_number_unique` (`sequence_counter_id`,`sequence_number`),
  KEY `sequence_allocations_consecutive_index` (`consecutive`),
  UNIQUE KEY `sequence_allocation_idempotency_unique` (`api_credential_id`,`idempotency_key`),
  KEY `sequence_allocations_external_reference_index` (`external_reference`),
  CONSTRAINT `sequence_allocations_counter_foreign` FOREIGN KEY (`sequence_counter_id`) REFERENCES `sequence_counters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sequence_allocations_lease_foreign` FOREIGN KEY (`sequence_lease_id`) REFERENCES `sequence_leases` (`id`) ON DELETE SET NULL,
  CONSTRAINT `sequence_allocations_credential_foreign` FOREIGN KEY (`api_credential_id`) REFERENCES `api_credentials` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `idempotency_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `api_credential_id` bigint unsigned NOT NULL,
  `idempotency_key` varchar(128) NOT NULL,
  `request_hash` char(64) NOT NULL,
  `method` varchar(10) NOT NULL,
  `path` varchar(255) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'processing',
  `response_status` smallint unsigned DEFAULT NULL,
  `response_body` longtext DEFAULT NULL,
  `locked_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idempotency_key_unique` (`api_credential_id`,`idempotency_key`),
  KEY `idempotency_requests_status_index` (`status`),
  KEY `idempotency_requests_expires_at_index` (`expires_at`),
  CONSTRAINT `idempotency_requests_credential_foreign` FOREIGN KEY (`api_credential_id`) REFERENCES `api_credentials` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `api_account_id` bigint unsigned DEFAULT NULL,
  `actor_type` varchar(40) NOT NULL,
  `actor_id` varchar(64) DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `target_type` varchar(80) NOT NULL,
  `target_id` varchar(64) DEFAULT NULL,
  `request_id` varchar(80) DEFAULT NULL,
  `ip_hash` char(64) DEFAULT NULL,
  `user_agent_hash` char(64) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `audit_events_public_id_unique` (`public_id`),
  KEY `audit_events_event_type_index` (`event_type`),
  KEY `audit_events_request_id_index` (`request_id`),
  KEY `audit_events_created_at_index` (`created_at`),
  KEY `audit_account_date` (`api_account_id`,`created_at`),
  CONSTRAINT `audit_events_api_account_foreign` FOREIGN KEY (`api_account_id`) REFERENCES `api_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `domain_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `api_account_id` bigint unsigned DEFAULT NULL,
  `deduplication_key` varchar(190) DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `aggregate_type` varchar(80) NOT NULL,
  `aggregate_id` varchar(64) NOT NULL,
  `payload` json NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `attempts` int unsigned NOT NULL DEFAULT 0,
  `available_at` timestamp NULL DEFAULT NULL,
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain_events_public_id_unique` (`public_id`),
  UNIQUE KEY `domain_events_deduplication_key_unique` (`deduplication_key`),
  KEY `domain_events_event_type_index` (`event_type`),
  KEY `domain_events_status_index` (`status`),
  KEY `domain_events_available_at_index` (`available_at`),
  KEY `domain_event_dispatch` (`status`,`available_at`),
  CONSTRAINT `domain_events_api_account_foreign` FOREIGN KEY (`api_account_id`) REFERENCES `api_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `inconsistencies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `api_account_id` bigint unsigned DEFAULT NULL,
  `business_id` bigint unsigned DEFAULT NULL,
  `branch_id` bigint unsigned DEFAULT NULL,
  `terminal_id` bigint unsigned DEFAULT NULL,
  `code` varchar(100) NOT NULL,
  `severity` varchar(20) NOT NULL DEFAULT 'warning',
  `source` varchar(40) NOT NULL,
  `entity_type` varchar(80) DEFAULT NULL,
  `entity_id` varchar(80) DEFAULT NULL,
  `fingerprint` char(64) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'open',
  `message` text DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `occurrences` int unsigned NOT NULL DEFAULT 1,
  `first_detected_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_detected_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `inconsistencies_public_id_unique` (`public_id`),
  KEY `inconsistencies_code_index` (`code`),
  KEY `inconsistencies_severity_index` (`severity`),
  KEY `inconsistencies_source_index` (`source`),
  KEY `inconsistencies_fingerprint_index` (`fingerprint`),
  KEY `inconsistencies_status_index` (`status`),
  KEY `inconsistency_account_status` (`api_account_id`,`status`,`severity`),
  KEY `inconsistencies_business_foreign_idx` (`business_id`),
  KEY `inconsistencies_branch_foreign_idx` (`branch_id`),
  KEY `inconsistencies_terminal_foreign_idx` (`terminal_id`),
  CONSTRAINT `inconsistencies_api_account_foreign` FOREIGN KEY (`api_account_id`) REFERENCES `api_accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `inconsistencies_business_foreign` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `inconsistencies_branch_foreign` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `inconsistencies_terminal_foreign` FOREIGN KEY (`terminal_id`) REFERENCES `terminals` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhook_endpoints` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `api_account_id` bigint unsigned NOT NULL,
  `business_id` bigint unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `url` varchar(500) NOT NULL,
  `signing_secret_ciphertext` text NOT NULL,
  `signing_secret_nonce` varchar(80) NOT NULL,
  `secret_key_version` smallint unsigned NOT NULL DEFAULT 1,
  `secret_fingerprint` char(16) NOT NULL,
  `tls_verify` tinyint(1) NOT NULL DEFAULT 1,
  `timeout_seconds` smallint unsigned NOT NULL DEFAULT 10,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `created_by_system_user` char(26) DEFAULT NULL,
  `last_success_at` timestamp NULL DEFAULT NULL,
  `last_failure_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `webhook_endpoints_public_id_unique` (`public_id`),
  KEY `webhook_endpoints_secret_fingerprint_index` (`secret_fingerprint`),
  KEY `webhook_endpoints_status_index` (`status`),
  KEY `webhook_endpoints_created_by_system_user_index` (`created_by_system_user`),
  KEY `webhook_endpoints_business_id_foreign_idx` (`business_id`),
  CONSTRAINT `webhook_endpoints_api_account_id_foreign` FOREIGN KEY (`api_account_id`) REFERENCES `api_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `webhook_endpoints_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhook_endpoint_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `webhook_endpoint_id` bigint unsigned NOT NULL,
  `event_type` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `webhook_endpoint_event_unique` (`webhook_endpoint_id`,`event_type`),
  CONSTRAINT `webhook_endpoint_events_endpoint_foreign` FOREIGN KEY (`webhook_endpoint_id`) REFERENCES `webhook_endpoints` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhook_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `api_account_id` bigint unsigned NOT NULL,
  `business_id` bigint unsigned DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `aggregate_type` varchar(80) NOT NULL,
  `aggregate_id` varchar(80) NOT NULL,
  `payload` json NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `available_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `webhook_events_public_id_unique` (`public_id`),
  KEY `webhook_events_event_type_index` (`event_type`),
  KEY `webhook_events_status_index` (`status`),
  KEY `webhook_events_available_at_index` (`available_at`),
  KEY `webhook_events_dispatch` (`api_account_id`,`status`,`available_at`),
  KEY `webhook_events_business_id_foreign_idx` (`business_id`),
  CONSTRAINT `webhook_events_api_account_id_foreign` FOREIGN KEY (`api_account_id`) REFERENCES `api_accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `webhook_events_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhook_attempts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `public_id` char(26) NOT NULL,
  `webhook_event_id` bigint unsigned NOT NULL,
  `webhook_endpoint_id` bigint unsigned NOT NULL,
  `attempt_number` smallint unsigned NOT NULL,
  `http_status` smallint unsigned DEFAULT NULL,
  `latency_ms` int unsigned DEFAULT NULL,
  `result` varchar(20) NOT NULL,
  `error_code` varchar(80) DEFAULT NULL,
  `response_excerpt` text DEFAULT NULL,
  `next_retry_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `webhook_attempts_public_id_unique` (`public_id`),
  UNIQUE KEY `webhook_attempt_unique` (`webhook_event_id`,`webhook_endpoint_id`,`attempt_number`),
  KEY `webhook_attempts_result_index` (`result`),
  KEY `webhook_attempts_next_retry_at_index` (`next_retry_at`),
  KEY `webhook_attempts_created_at_index` (`created_at`),
  KEY `webhook_attempts_endpoint_foreign_idx` (`webhook_endpoint_id`),
  CONSTRAINT `webhook_attempts_event_foreign` FOREIGN KEY (`webhook_event_id`) REFERENCES `webhook_events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `webhook_attempts_endpoint_foreign` FOREIGN KEY (`webhook_endpoint_id`) REFERENCES `webhook_endpoints` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`,`batch`)
SELECT '2026_09_22_000001_create_identity_tables',1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_09_22_000001_create_identity_tables');
INSERT INTO `migrations` (`migration`,`batch`)
SELECT '2026_09_22_000002_create_api_credentials',1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_09_22_000002_create_api_credentials');
INSERT INTO `migrations` (`migration`,`batch`)
SELECT '2026_09_22_000003_create_sequence_engine',1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_09_22_000003_create_sequence_engine');
INSERT INTO `migrations` (`migration`,`batch`)
SELECT '2026_09_22_000004_create_idempotency_and_events',1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_09_22_000004_create_idempotency_and_events');
INSERT INTO `migrations` (`migration`,`batch`)
SELECT '2026_09_22_000005_create_runtime_tables',1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_09_22_000005_create_runtime_tables');
INSERT INTO `migrations` (`migration`,`batch`)
SELECT '2026_09_22_000006_create_inconsistencies',1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_09_22_000006_create_inconsistencies');

INSERT INTO `migrations` (`migration`,`batch`)
SELECT '2026_09_22_000007_create_webhook_tables',1 FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration`='2026_09_22_000007_create_webhook_tables');

SET FOREIGN_KEY_CHECKS=1;