-- nova-orm-schema-checksum: 51e7d7d55485ce5f17c4b930e7165f26
-- Nova ORM Schema Dump
-- Generated at: 2026-08-24T12:38:06.185Z


CREATE TABLE `acumaticaAmazonProduct` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sku` varchar(50) NOT NULL,
  `asin` varchar(50) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `reviewUrl` varchar(1000) DEFAULT NULL,
  `productUrl` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `acumaticaImeiIccidData` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `imei` varchar(50) NOT NULL,
  `iccid` varchar(50) DEFAULT NULL,
  `amazonShipmentId` varchar(50) DEFAULT NULL,
  `deviceModel` varchar(50) DEFAULT NULL,
  `deviceCarrier` varchar(50) DEFAULT NULL,
  `carrierNetwork` varchar(50) DEFAULT NULL,
  `carrierProductId` varchar(50) DEFAULT NULL,
  `poNbr` varchar(50) DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `createdDate` varchar(50) DEFAULT NULL,
  `lastModifiedDate` varchar(50) DEFAULT NULL,
  `shipDate` varchar(50) DEFAULT NULL,
  `asin` varchar(50) DEFAULT NULL,
  `countryCode` varchar(3) DEFAULT NULL,
  `platform` varchar(100) DEFAULT NULL,
  `listingId` varchar(100) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixAcumaticaImeiIccidDataImei` (`imei`),
  KEY `ixAcumaticaImeiIccidDataAmazonShipmentId` (`amazonShipmentId`),
  KEY `ixAcumaticaImeiIccidDataIccid` (`iccid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `acumaticaMarketplaceProduct` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `platform` enum('amazon','walmart','ebay','best_buy','tiktok') NOT NULL,
  `location` varchar(50) NOT NULL,
  `sku` varchar(50) NOT NULL,
  `identifier` varchar(50) NOT NULL,
  `name` varchar(500) DEFAULT NULL,
  `reviewUrl` varchar(1000) DEFAULT NULL,
  `productUrl` varchar(1000) DEFAULT NULL,
  `isPrepaid` tinyint(1) NOT NULL DEFAULT '0',
  `prepaidDurationMonths` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxAcumaticaMarketplaceProductPlatformIdentifierLocation` (`platform`,`identifier`,`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `acumaticaOrderShipmentDetails` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `imei` varchar(255) DEFAULT NULL,
  `shipmentNumber` varchar(50) DEFAULT NULL,
  `shipmentStatus` varchar(50) DEFAULT NULL,
  `shipmentDate` datetime DEFAULT NULL,
  `orderType` varchar(10) DEFAULT NULL,
  `orderStatus` varchar(50) DEFAULT NULL,
  `iccid` varchar(255) DEFAULT NULL,
  `sku` varchar(50) DEFAULT NULL,
  `customer` varchar(50) DEFAULT NULL,
  `orderNumber` varchar(50) DEFAULT NULL,
  `customerOrderNumber` varchar(100) DEFAULT NULL,
  `orderDate` datetime DEFAULT NULL,
  `orderedQty` decimal(18,6) DEFAULT NULL,
  `orderTotal` decimal(18,4) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `lastModifiedBy` varchar(100) DEFAULT NULL,
  `lastModifiedOn` datetime DEFAULT NULL,
  `shipVia` varchar(50) DEFAULT NULL,
  `gpsDeviceQty` decimal(18,6) DEFAULT NULL,
  `shiplineQty` decimal(18,6) DEFAULT NULL,
  `warehouse` varchar(50) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  `carrier` varchar(50) DEFAULT NULL,
  `lastModifiedDateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixAcumaticaOrderShipmentDetailsIccid` (`iccid`),
  KEY `ixAcumaticaOrderShipmentDetails_imei_shipmentDate` (`imei`,`shipmentDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `acumaticaShipment` (
  `shipmentId` varchar(50) NOT NULL,
  `shipmentDate` datetime DEFAULT NULL,
  `location` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`shipmentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `acumaticaShipmentAmazon` (
  `shipmentId` varchar(50) NOT NULL,
  `asin` varchar(50) NOT NULL,
  PRIMARY KEY (`shipmentId`,`asin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `aempToken` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `token` binary(16) NOT NULL,
  `createdBy` binary(16) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `expiresAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuAempToken_Token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `aiConversation` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `title` varchar(255) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ixAiConversationUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `aiMessage` (
  `id` binary(16) NOT NULL,
  `conversationId` binary(16) NOT NULL,
  `userId` binary(16) NOT NULL,
  `role` varchar(20) NOT NULL,
  `content` mediumtext NOT NULL,
  `toolCalls` json DEFAULT NULL,
  `toolResults` json DEFAULT NULL,
  `ordinal` int NOT NULL DEFAULT '0',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ixAiMessageConversationIdCreatedAtOrdinal` (`conversationId`,`createdAt`,`ordinal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `airflowAcumaticaExportImeiIccidData` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `amazonShipmentID` varchar(50) NOT NULL,
  `imei` varchar(50) NOT NULL,
  `iccid` varchar(500) DEFAULT NULL,
  `deviceModel` varchar(1000) DEFAULT NULL,
  `deviceCarrier` varchar(1000) DEFAULT NULL,
  `carrierNetwork` varchar(1000) DEFAULT NULL,
  `carrierProductId` varchar(1000) DEFAULT NULL,
  `poNumber` varchar(1000) DEFAULT NULL,
  `createdBy` varchar(1000) DEFAULT NULL,
  `createdDate` varchar(1000) DEFAULT NULL,
  `lastModifiedDate` varchar(1000) DEFAULT NULL,
  `shipDate` varchar(1000) DEFAULT NULL,
  `platform` varchar(1000) DEFAULT NULL,
  `asin` varchar(1000) DEFAULT NULL,
  `listingId` varchar(1000) DEFAULT NULL,
  `location` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `airflowAcumaticaExportOrderShipmentDetails` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `customer` varchar(50) NOT NULL,
  `orderType` varchar(50) NOT NULL,
  `orderNumber` varchar(50) NOT NULL,
  `customerOrderNumber` varchar(50) NOT NULL,
  `orderDate` varchar(50) NOT NULL,
  `orderedQty` varchar(50) NOT NULL,
  `orderTotal` varchar(50) NOT NULL,
  `orderStatus` varchar(50) NOT NULL,
  `currency` varchar(50) NOT NULL,
  `createdBy` varchar(50) NOT NULL,
  `createdOn` varchar(50) NOT NULL,
  `lastModifiedBy` varchar(50) NOT NULL,
  `lastModifiedOn` varchar(50) NOT NULL,
  `shipmentStatus` varchar(50) NOT NULL,
  `shipVia` varchar(50) NOT NULL,
  `shipmentNumber` varchar(50) NOT NULL,
  `shipmentDate` varchar(50) NOT NULL,
  `inventoryId` varchar(50) NOT NULL,
  `gpsDeviceQty` varchar(50) NOT NULL,
  `shiplineQty` varchar(50) NOT NULL,
  `lotSerialNumber` varchar(512) NOT NULL,
  `warehouse` varchar(50) NOT NULL,
  `location` varchar(50) NOT NULL,
  `carrier` varchar(50) NOT NULL,
  `iccid` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `airflowAcumaticaShipmentsAmazon` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `shipment` varchar(50) DEFAULT NULL,
  `date` varchar(50) DEFAULT NULL,
  `asin` varchar(50) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `alert` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `alertTypeId` smallint NOT NULL,
  `value` varchar(5000) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL,
  `userId` binary(16) DEFAULT NULL,
  `messageId` binary(16) NOT NULL,
  `boundaryId` int DEFAULT NULL,
  `imei` varchar(15) NOT NULL,
  `sendTime` datetime(3) NOT NULL,
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `clientId` int DEFAULT NULL,
  `isBuffer` tinyint(1) DEFAULT NULL,
  `isNotification` tinyint(1) DEFAULT NULL,
  `gpsUtcTime` datetime(3) DEFAULT NULL,
  `relevantAddress` varchar(255) DEFAULT NULL,
  `gpsMessageId` binary(16) DEFAULT NULL,
  `cellTowerRelevantAddress` varchar(255) DEFAULT NULL,
  `userAlertConfigurationId` int DEFAULT NULL,
  `clientAlertConfigurationId` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ixAlertSendTime` (`sendTime`),
  KEY `ixAlertImei` (`imei`),
  KEY `ixAlertUserId_AlertTypeId_SendTime` (`userId`,`alertTypeId`,`sendTime`),
  KEY `ixAlertUserId_Imei_AlertTypeId_SendTime` (`userId`,`imei`,`alertTypeId`,`sendTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `alertType` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` varchar(2000) DEFAULT NULL,
  `isSystem` tinyint(1) NOT NULL DEFAULT '0',
  `icon` varchar(50) DEFAULT NULL,
  `context` varchar(25) DEFAULT NULL,
  `identifier` varchar(50) DEFAULT NULL,
  `stage` varchar(10) DEFAULT NULL,
  `category` varchar(25) DEFAULT NULL,
  `key` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixAlertTypeStage` (`stage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `anomaly` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `imei` varchar(32) DEFAULT NULL,
  `type` varchar(40) NOT NULL,
  `severity` varchar(20) NOT NULL,
  `summary` varchar(255) NOT NULL,
  `observedValue` double DEFAULT NULL,
  `baselineValue` double DEFAULT NULL,
  `score` double DEFAULT NULL,
  `method` varchar(30) DEFAULT NULL,
  `metrics` json DEFAULT NULL,
  `occurredAt` datetime NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `dedupKey` varchar(191) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxAnomalyDedupKey` (`dedupKey`),
  KEY `ixAnomalyClientIdStatusOccurredAt` (`clientId`,`status`,`occurredAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `appFeatureType` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(500) NOT NULL,
  `statusId` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `asinPromotion` (
  `asin` varchar(15) NOT NULL,
  `promotionId` int NOT NULL,
  PRIMARY KEY (`asin`,`promotionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetCategory` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuAssetCategoryName` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetProfile` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deviceId` int DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `clientId` int NOT NULL,
  `assetProfileTypeId` smallint DEFAULT NULL,
  `make` varchar(50) DEFAULT NULL,
  `model` varchar(50) DEFAULT NULL,
  `serialNumber` varchar(512) DEFAULT NULL,
  `year` smallint DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'Active',
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_deviceId` (`deviceId`),
  KEY `ix_clientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetProfileCategory` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuAssetProfileCategoryName` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetProfileCustomField` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assetProfileId` int NOT NULL,
  `name` varchar(50) NOT NULL,
  `value` varchar(50) NOT NULL,
  `type` varchar(50) NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_assetProfileId` (`assetProfileId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetProfileCustomStatus` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `status` varchar(50) NOT NULL,
  `label` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `color` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_clientId_status` (`clientId`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetProfileMedia` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assetProfileId` int NOT NULL,
  `mediaType` varchar(15) NOT NULL COMMENT 'The type of the media: image or document',
  `fileName` varchar(255) NOT NULL,
  `fileExtension` varchar(15) DEFAULT NULL,
  `s3FilesPath` varchar(255) DEFAULT NULL,
  `uploadedBy` binary(16) DEFAULT NULL COMMENT 'userId of the uploader. Null for rows created before the column existed.',
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_ix_assetProfileId_fileName` (`assetProfileId`,`fileName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetProfileNote` (
  `id` int NOT NULL AUTO_INCREMENT,
  `assetProfileId` int NOT NULL,
  `note` varchar(255) NOT NULL,
  `userId` binary(16) NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_assetProfileId` (`assetProfileId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `assetProfileType` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `assetProfileCategoryId` tinyint NOT NULL,
  `clientId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_assetProfileCategoryId_name_clientId` (`assetProfileCategoryId`,`name`,`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `avalaraExemptCert` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `fileName` varchar(1000) NOT NULL,
  `expirationDate` date NOT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_0fa6a1061b4cb7238164321492` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `avalaraQueue` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `subscriptionId` varchar(256) DEFAULT NULL,
  `planId` varchar(256) DEFAULT NULL,
  `priceIncrease` decimal(19,4) DEFAULT NULL,
  `priceDecrease` decimal(19,4) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `basePrice` enum('original','calculated') DEFAULT NULL,
  `attempts` tinyint NOT NULL DEFAULT '0',
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ixAvalaraQueueSubscriptionId` (`subscriptionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `beaconBleMesh` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `name` varchar(250) DEFAULT NULL,
  `bleMac` varchar(20) NOT NULL,
  `bleMeshId` int DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuBeaconMesh_bleMac` (`bleMac`),
  KEY `ixBeaconMesh_clientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deviceId` int DEFAULT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `subscriptionId` varchar(50) DEFAULT NULL,
  `platform` varchar(25) DEFAULT NULL,
  `status` varchar(25) DEFAULT NULL,
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `subscriptionValue` decimal(19,4) DEFAULT NULL,
  `subscriptionPeriodMonths` tinyint DEFAULT NULL,
  `subscriptionValueMonthly` decimal(19,4) DEFAULT NULL,
  `clientId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuBillingImei` (`imei`),
  KEY `ixBillingCreatedAt` (`createdAt`),
  KEY `ixBillingComposite` (`imei`,`subscriptionId`,`platform`,`status`,`planId`),
  KEY `ixBillingSubscriptionId` (`subscriptionId`),
  KEY `ixBillingPlanId` (`planId`),
  KEY `ixBillingClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaign` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(64) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  `startAt` datetime DEFAULT NULL,
  `endAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuBillingCampaignType` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaignExpansion` (
  `id` int NOT NULL AUTO_INCREMENT,
  `offerId` int NOT NULL,
  `clientId` int NOT NULL,
  `userId` binary(16) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `expansionOffer` enum('free_subscription','free_loaner','control') NOT NULL,
  `assignedAt` datetime DEFAULT NULL,
  `status` enum('assigned','pending','contacted','payment_collected','shipped','subscription_created','activated','returned','canceled','expired') NOT NULL DEFAULT 'assigned',
  `qualifyingImei` varchar(64) DEFAULT NULL,
  `qualifyingSubscriptionId` varchar(255) DEFAULT NULL,
  `qualifyingModelFamily` varchar(50) DEFAULT NULL,
  `qualifyingPlanId` varchar(255) DEFAULT NULL,
  `offerShownAt` datetime DEFAULT NULL,
  `offerClickedAt` datetime DEFAULT NULL,
  `offerDismissedAt` datetime DEFAULT NULL,
  `offerSubmittedAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuBillingCampaignExpansionClientId` (`clientId`),
  KEY `ixBillingCampaignExpansionCreatedAt` (`createdAt`),
  KEY `ixBillingCampaignExpansionQualifyingImei` (`qualifyingImei`),
  KEY `ixBillingCampaignExpansionOffer` (`expansionOffer`),
  KEY `ixBillingCampaignExpansionStatus` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaignExpansionAddress` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `isDifferentFromAccount` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `street1` varchar(255) DEFAULT NULL,
  `street2` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `postalCode` varchar(32) DEFAULT NULL,
  `countryCode` varchar(2) DEFAULT NULL,
  `phone` varchar(64) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingCampaignExpansionAddressClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaignExpansionDevice` (
  `id` int NOT NULL AUTO_INCREMENT,
  `expansionId` int NOT NULL,
  `imei` varchar(64) DEFAULT NULL,
  `subscriptionId` varchar(255) DEFAULT NULL,
  `subscriptionCreatedAt` datetime DEFAULT NULL,
  `trialStartAt` datetime DEFAULT NULL,
  `trialEndAt` datetime DEFAULT NULL,
  `convertedAt` datetime DEFAULT NULL,
  `canceledAt` datetime DEFAULT NULL,
  `activatedAt` datetime DEFAULT NULL,
  `activationDeadlineAt` datetime DEFAULT NULL,
  `firstPingAt` datetime DEFAULT NULL,
  `lastPingCheckedAt` datetime DEFAULT NULL,
  `pingDetected` tinyint(1) NOT NULL DEFAULT '0',
  `returnDueAt` datetime DEFAULT NULL,
  `graceEndsAt` datetime DEFAULT NULL,
  `returnedAt` datetime DEFAULT NULL,
  `returnNeedsManualVerification` tinyint(1) NOT NULL DEFAULT '0',
  `reminderD50SentAt` datetime DEFAULT NULL,
  `preBillingFyiSentAt` datetime DEFAULT NULL,
  `csReminderSentAt` datetime DEFAULT NULL,
  `csReminderCount` int NOT NULL DEFAULT '0',
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuBillingCampaignExpansionDeviceExpansionId` (`expansionId`),
  KEY `ixBillingCampaignExpansionDeviceGraceEndsAt` (`graceEndsAt`),
  KEY `ixBillingCampaignExpansionDeviceReturnDueAt` (`returnDueAt`),
  KEY `ixBillingCampaignExpansionDeviceActivationDeadline` (`pingDetected`,`activationDeadlineAt`),
  KEY `ixBillingCampaignExpansionDeviceSubscriptionId` (`subscriptionId`),
  KEY `ixBillingCampaignExpansionDeviceImei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaignExpansionOrder` (
  `id` int NOT NULL AUTO_INCREMENT,
  `expansionId` int NOT NULL,
  `addressId` int DEFAULT NULL,
  `selectedModelFamily` varchar(50) DEFAULT NULL,
  `selectedDeviceTypeId` smallint DEFAULT NULL,
  `devicePriceCents` int DEFAULT NULL,
  `planId` varchar(255) DEFAULT NULL,
  `monthlyPriceCents` int DEFAULT NULL,
  `shopifyDraftOrderId` varchar(255) DEFAULT NULL,
  `draftOrderCompletedAt` datetime DEFAULT NULL,
  `shopifyOrderId` varchar(255) DEFAULT NULL,
  `shippedAt` datetime DEFAULT NULL,
  `deliveredAt` datetime DEFAULT NULL,
  `trackingNumber` varchar(255) DEFAULT NULL,
  `carrier` varchar(128) DEFAULT NULL,
  `deviceFeePaymentIntentId` varchar(255) DEFAULT NULL,
  `deviceFeeChargedAt` datetime DEFAULT NULL,
  `deviceFeeFailureReason` varchar(512) DEFAULT NULL,
  `deviceFeeReminderSentAt` datetime DEFAULT NULL,
  `deviceFeeReminderCount` int NOT NULL DEFAULT '0',
  `csNotes` text,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuBillingCampaignExpansionOrderExpansionId` (`expansionId`),
  KEY `ixBillingCampaignExpansionOrderAwaitingPayment` (`deviceFeeChargedAt`,`createdAt`),
  KEY `ixBillingCampaignExpansionOrderShippedAt` (`shippedAt`),
  KEY `ixBillingCampaignExpansionOrderShopifyOrderId` (`shopifyOrderId`),
  KEY `ixBillingCampaignExpansionOrderDraftOrderId` (`shopifyDraftOrderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaignFreeOffer` (
  `id` int NOT NULL AUTO_INCREMENT,
  `offerId` int NOT NULL,
  `subscriptionId` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `imei` varchar(64) DEFAULT NULL,
  `clientId` int DEFAULT NULL,
  `type` enum('discount','deferral') DEFAULT NULL,
  `status` enum('applied','completed','canceled_before','canceled_during') DEFAULT NULL,
  `discountPrice` decimal(19,4) DEFAULT NULL,
  `discountDurationMonths` int DEFAULT NULL,
  `deferralMonths` int DEFAULT NULL,
  `originalRenewalDate` date DEFAULT NULL,
  `newRenewalDate` date DEFAULT NULL,
  `freeMonthStartAt` datetime DEFAULT NULL,
  `freeMonthEndAt` datetime DEFAULT NULL,
  `appliedAt` datetime DEFAULT NULL,
  `completedAt` datetime DEFAULT NULL,
  `canceledAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingCampaignFreeOfferClientIdAppliedAt` (`clientId`,`appliedAt`),
  KEY `ixBillingCampaignFreeOfferImei` (`imei`),
  KEY `ixBillingCampaignFreeOfferSubscriptionId` (`subscriptionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaignFreeTrial` (
  `id` int NOT NULL AUTO_INCREMENT,
  `offerId` int NOT NULL,
  `subscriptionId` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `imei` varchar(64) DEFAULT NULL,
  `status` enum('in_trial','canceled','converted','overdue') DEFAULT NULL,
  `trialLengthHours` int DEFAULT NULL,
  `trialStartAt` datetime DEFAULT NULL,
  `trialEndAt` datetime DEFAULT NULL,
  `convertedAt` datetime DEFAULT NULL,
  `canceledAt` datetime DEFAULT NULL,
  `reminderSent` tinyint(1) NOT NULL DEFAULT '0',
  `modalShown` tinyint(1) NOT NULL DEFAULT '0',
  `retryAttemptCount` int NOT NULL DEFAULT '0',
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingCampaignFreeTrialSubscriptionId` (`subscriptionId`),
  KEY `ixBillingCampaignFreeTrialCreatedAt` (`createdAt`),
  KEY `ixBillingCampaignFreeTrialOfferId` (`offerId`),
  KEY `ixBillingCampaignFreeTrialStatusTrialEndAt` (`status`,`trialEndAt`),
  KEY `ixBillingCampaignFreeTrialEmail` (`email`),
  KEY `ixBillingCampaignFreeTrialImei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCampaignPrepaid` (
  `id` int NOT NULL AUTO_INCREMENT,
  `offerId` int NOT NULL,
  `subscriptionId` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `imei` varchar(64) DEFAULT NULL,
  `status` enum('prepaid_started','prepaid_ending_soon','prepaid_converted','canceled','overdue') DEFAULT NULL,
  `prepaidDurationMonths` int DEFAULT NULL,
  `prepaidStartAt` datetime DEFAULT NULL,
  `prepaidEndAt` datetime DEFAULT NULL,
  `convertedAt` datetime DEFAULT NULL,
  `canceledAt` datetime DEFAULT NULL,
  `annualUpsellEligible` tinyint(1) NOT NULL DEFAULT '0',
  `annualUpsellImpressions` int NOT NULL DEFAULT '0',
  `annualUpsellFirstShownAt` datetime DEFAULT NULL,
  `annualUpsellLastShownAt` datetime DEFAULT NULL,
  `annualUpsellDismissedCount` int NOT NULL DEFAULT '0',
  `annualUpsellUpgradedAt` datetime DEFAULT NULL,
  `annualUpsellQueuedAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingCampaignPrepaidSubscriptionId` (`subscriptionId`),
  KEY `ixBillingCampaignPrepaidCreatedAt` (`createdAt`),
  KEY `ixBillingCampaignPrepaidOfferId` (`offerId`),
  KEY `ixBillingCampaignPrepaidStatusPrepaidEndAt` (`status`,`prepaidEndAt`),
  KEY `ixBillingCampaignPrepaidEmail` (`email`),
  KEY `ixBillingCampaignPrepaidImei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCustomerAmazon` (
  `clientId` int NOT NULL,
  `firstName` varchar(100) DEFAULT NULL,
  `lastName` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `streetAddress` varchar(255) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `postalCode` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`clientId`),
  KEY `ixBillingCustomerAmazonEmail` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCustomerBraintree` (
  `id` varchar(50) NOT NULL,
  `firstName` varchar(50) DEFAULT NULL,
  `lastName` varchar(50) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(150) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `churnStatus` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingCustomerBraintreeEmail` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingCustomerStripe` (
  `id` varchar(50) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `postalCode` varchar(30) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `hasMigratedExpiredCard` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ixBillingCustomerStripeEmail` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingDeviceHistory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int DEFAULT NULL,
  `deviceId` int DEFAULT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `subscriptionId` varchar(50) DEFAULT NULL,
  `platform` varchar(25) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingDeviceHistoryImeiType` (`imei`,`type`),
  KEY `ixBillingDeviceHistoryImeiSubscriptionIdCreatedAt` (`imei`,`subscriptionId`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingDeviceTypePlan` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deviceTypeId` int NOT NULL,
  `planId` varchar(50) NOT NULL,
  `deviceTypeModel` varchar(50) NOT NULL,
  `deviceType` varchar(50) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `status` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingDeviceTypePlanDeviceTypeModel` (`deviceTypeModel`),
  KEY `ixBillingDeviceTypePlanPlanId` (`planId`),
  KEY `ixBillingDeviceTypePlanStatus` (`status`),
  KEY `ixBillingDeviceTypePlanDeviceTypeId` (`deviceTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingDunning` (
  `subscriptionId` varchar(100) NOT NULL,
  `customerId` varchar(100) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `platform` enum('shopify','braintree','acumatica','klarna','amazon','braintree_amazon','demo','stripe','stripe_amazon','oem') NOT NULL,
  `eventType` enum('campaign_started','email_bounced','campaign_recovered','campaign_lost','campaign_won') NOT NULL,
  `churnBusterId` varchar(255) NOT NULL,
  `captureUrl` varchar(1000) NOT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`subscriptionId`),
  KEY `ixBillingDunningEventType` (`subscriptionId`,`eventType`),
  KEY `ixBillingDunningCustomerId` (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingHubspotPaymentLog` (
  `id` int NOT NULL AUTO_INCREMENT,
  `owner` varchar(50) DEFAULT NULL,
  `amountPaid` decimal(19,4) DEFAULT NULL,
  `bankOfIssuer` varchar(50) DEFAULT NULL,
  `billingAddress` varchar(255) DEFAULT NULL,
  `billingAddressCity` varchar(50) DEFAULT NULL,
  `billingAddressCountry` varchar(3) DEFAULT NULL,
  `billingAddressLine1` varchar(255) DEFAULT NULL,
  `billingAddressLine2` varchar(255) DEFAULT NULL,
  `billingAddressState` varchar(50) DEFAULT NULL,
  `billingAddressZip` varchar(50) DEFAULT NULL,
  `createdByUserId` varchar(50) DEFAULT NULL,
  `currencyCode` varchar(3) DEFAULT NULL,
  `customer` varchar(50) DEFAULT NULL,
  `grossAmount` decimal(19,4) DEFAULT NULL,
  `lastFourDigits` varchar(4) DEFAULT NULL,
  `lineItemDiscountAmount` decimal(19,4) DEFAULT NULL,
  `netAmount` decimal(19,4) DEFAULT NULL,
  `objectCreateDateTime` datetime DEFAULT NULL,
  `objectId` varchar(20) DEFAULT NULL,
  `objectLastModifiedDateTime` datetime DEFAULT NULL,
  `orderDiscountAmount` decimal(19,4) DEFAULT NULL,
  `orderDiscountCode` varchar(50) DEFAULT NULL,
  `orderDiscountPercentage` smallint NOT NULL DEFAULT '0',
  `ownerAssignedDateTime` datetime DEFAULT NULL,
  `paymentDateTime` datetime DEFAULT NULL,
  `paymentId` varchar(20) DEFAULT NULL,
  `paymentMethod` varchar(10) DEFAULT NULL,
  `paymentSourceName` varchar(255) DEFAULT NULL,
  `paymentType` varchar(20) DEFAULT NULL,
  `processingFees` decimal(19,4) DEFAULT NULL,
  `refunded` decimal(19,4) DEFAULT NULL,
  `shippingAddress` varchar(255) DEFAULT NULL,
  `shippingAddressCity` varchar(50) DEFAULT NULL,
  `shippingAddressCountry` varchar(3) DEFAULT NULL,
  `shippingAddressLine1` varchar(255) DEFAULT NULL,
  `shippingAddressLine2` varchar(255) DEFAULT NULL,
  `shippingAddressState` varchar(50) DEFAULT NULL,
  `shippingAddressZip` varchar(50) DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `sourceId` varchar(20) DEFAULT NULL,
  `sourceUrl` varchar(255) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `statusUpdatedOn` datetime DEFAULT NULL,
  `totalDiscountAmount` decimal(19,4) DEFAULT NULL,
  `totalFees` decimal(19,4) DEFAULT NULL,
  `totalTaxes` decimal(19,4) DEFAULT NULL,
  `updatedByUserId` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingInvoice` (
  `id` int NOT NULL AUTO_INCREMENT,
  `platform` enum('stripe','klarna','amazon') NOT NULL,
  `externalId` varchar(100) NOT NULL,
  `clientId` int DEFAULT NULL,
  `customerId` varchar(50) DEFAULT NULL,
  `subscriptionId` varchar(50) DEFAULT NULL,
  `customerEmail` varchar(255) DEFAULT NULL,
  `customerAddress` json DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `currency` char(3) NOT NULL DEFAULT 'usd',
  `amountDue` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `amountPaid` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `amountRemaining` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `subtotal` decimal(19,4) DEFAULT NULL,
  `total` decimal(19,4) DEFAULT NULL,
  `invoiceCreated` datetime NOT NULL,
  `dueDate` datetime DEFAULT NULL,
  `periodStart` datetime DEFAULT NULL,
  `periodEnd` datetime DEFAULT NULL,
  `paid` tinyint(1) NOT NULL DEFAULT '0',
  `attempted` tinyint(1) NOT NULL DEFAULT '0',
  `attemptCount` int NOT NULL DEFAULT '0',
  `hostedUrl` varchar(500) DEFAULT NULL,
  `pdfUrl` varchar(500) DEFAULT NULL,
  `invoiceNumber` varchar(100) DEFAULT NULL,
  `billingReason` varchar(64) DEFAULT NULL,
  `collectionMethod` varchar(32) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `lastEventType` varchar(64) DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxBillingInvoicePlatformExternal` (`platform`,`externalId`),
  KEY `ixBillingInvoiceStatusCreated` (`status`,`invoiceCreated`),
  KEY `ixBillingInvoiceSubscription` (`subscriptionId`),
  KEY `ixBillingInvoiceCustomerPlatform` (`customerId`,`platform`),
  KEY `ixBillingInvoiceClientCreated` (`clientId`,`invoiceCreated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingKlarnaCustomer` (
  `clientId` int NOT NULL,
  `firstName` varchar(100) DEFAULT NULL,
  `lastName` varchar(100) DEFAULT NULL,
  `billingEmail` varchar(255) DEFAULT NULL,
  `shippingEmail` varchar(255) DEFAULT NULL,
  `streetAddress` varchar(255) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `postalCode` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`clientId`),
  KEY `ixBillingKlarnaCustomerShippingEmail` (`shippingEmail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingKlarnaOrder` (
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `orderId` varchar(255) NOT NULL,
  `deviceType` varchar(50) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `subscriptionStart` date DEFAULT NULL,
  `subscriptionEnd` date DEFAULT NULL,
  `nextBillingDate` date DEFAULT NULL,
  `subscriptionValue` decimal(19,4) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `discounts` json DEFAULT NULL,
  `addOns` json DEFAULT NULL,
  `nextBillingPeriodAmount` decimal(19,4) DEFAULT NULL,
  `paymentMethodToken` varchar(50) DEFAULT NULL,
  `paymentType` varchar(20) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`imei`),
  KEY `ixBillingKlarnaOrderCreatedAt` (`createdAt`),
  KEY `ixBillingKlarnaOrderComposite` (`orderId`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingPlanFeatures` (
  `featureId` int NOT NULL AUTO_INCREMENT,
  `planId` varchar(50) NOT NULL,
  `feature` text,
  `status` varchar(20) NOT NULL,
  PRIMARY KEY (`featureId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingPlans` (
  `planId` varchar(50) NOT NULL,
  `targetCustomer` varchar(20) NOT NULL,
  `code` varchar(50) NOT NULL,
  `status` varchar(20) NOT NULL,
  `platform` varchar(255) NOT NULL,
  `billingFrequency` smallint NOT NULL,
  `contractLength` smallint NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `price` varchar(50) NOT NULL,
  `startDate` datetime DEFAULT NULL,
  `endDate` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`planId`),
  KEY `ixBillingPlansStartDate_EndDate` (`startDate`,`endDate`),
  KEY `ixBillingPlansTargetCustomer` (`targetCustomer`),
  KEY `ixBillingPlansStatus` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingStatusHistoryBraintree` (
  `id` int NOT NULL AUTO_INCREMENT,
  `timestamp` varchar(255) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `balance` decimal(19,4) DEFAULT NULL,
  `price` decimal(19,4) DEFAULT NULL,
  `currencyIsoCode` varchar(3) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `subscriptionId` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBillingStatusHistoryBraintreeTimestamp` (`timestamp`),
  KEY `ixBillingStatusHistoryBraintreeSubscriptionIdTimestamp` (`subscriptionId`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingSubscriptionAmazon` (
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `subscriptionId` varchar(255) DEFAULT NULL,
  `deviceType` varchar(50) DEFAULT NULL,
  `asin` varchar(50) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `subscriptionStart` date DEFAULT NULL,
  `subscriptionEnd` date DEFAULT NULL,
  `nextBillingDate` date DEFAULT NULL,
  `subscriptionValue` decimal(19,4) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `discounts` json DEFAULT NULL,
  `addOns` json DEFAULT NULL,
  `nextBillingPeriodAmount` decimal(19,4) DEFAULT NULL,
  `paymentMethodToken` varchar(50) DEFAULT NULL,
  `scheduledSuspension` datetime DEFAULT NULL,
  `paymentSkipped` tinyint(1) DEFAULT NULL,
  `existsInBraintree` tinyint(1) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`imei`),
  KEY `ixBillingSubscriptionAmazonCreatedAt` (`createdAt`),
  KEY `ixBillingSubscriptionAmazonComposite` (`subscriptionId`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingSubscriptionBraintree` (
  `subscriptionId` varchar(50) NOT NULL,
  `customerId` varchar(50) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `subscriptionStart` date DEFAULT NULL,
  `subscriptionEnd` date DEFAULT NULL,
  `nextBillingDate` date DEFAULT NULL,
  `subscriptionValue` decimal(19,4) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `subscriptionPeriodMonths` tinyint DEFAULT NULL,
  `paymentInstrumentType` varchar(20) DEFAULT NULL,
  `paymentInstrumentDetails` json DEFAULT NULL,
  `isPaused` tinyint(1) DEFAULT '0',
  `discountDetails` json DEFAULT NULL,
  `addons` json DEFAULT NULL,
  `balance` decimal(19,4) DEFAULT NULL,
  `currentBillingCycle` tinyint DEFAULT NULL,
  `daysPastDue` tinyint DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `descriptor` json DEFAULT NULL,
  `failureCount` tinyint DEFAULT NULL,
  `firstBillingDate` date DEFAULT NULL,
  `merchantAccountId` varchar(50) DEFAULT NULL,
  `nextBillingPeriodAmount` decimal(19,4) DEFAULT NULL,
  `numberOfBillingCycles` tinyint DEFAULT NULL,
  `paidThroughDate` date DEFAULT NULL,
  `paymentMethodToken` varchar(50) DEFAULT NULL,
  `statusHistory` json DEFAULT NULL,
  `trialDuration` smallint DEFAULT NULL,
  `trialDurationUnit` varchar(10) DEFAULT NULL,
  `trialPeriod` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`subscriptionId`),
  KEY `ixBillingSubscriptionBraintreeStatusUpdatedAt` (`status`,`updatedAt`),
  KEY `ixBillingSubscriptionBraintreeCreatedAt` (`createdAt`),
  KEY `ixBillingSubscriptionBraintreeComposite` (`subscriptionId`,`customerId`,`status`),
  KEY `ixBillingSubscriptionBraintreeCustomerId` (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingSubscriptionShopify` (
  `subscriptionId` bigint NOT NULL,
  `shopifySubscriptionId` bigint NOT NULL,
  `provider` varchar(20) NOT NULL DEFAULT 'loop',
  `customerId` bigint NOT NULL,
  `customerEmail` varchar(255) DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `shopifyOrderId` bigint DEFAULT NULL,
  `nextBillingAt` datetime DEFAULT NULL,
  `lastPaymentStatus` varchar(20) DEFAULT NULL,
  `totalLineItemPrice` decimal(19,4) DEFAULT NULL,
  `billingIntervalUnit` varchar(10) DEFAULT NULL,
  `billingIntervalCount` smallint DEFAULT NULL,
  `paymentMethodId` bigint DEFAULT NULL,
  `paymentMethodType` varchar(50) DEFAULT NULL,
  `paymentMethodDetails` json DEFAULT NULL,
  `isPrepaid` tinyint(1) NOT NULL DEFAULT '0',
  `isMarkedForCancellation` tinyint(1) NOT NULL DEFAULT '0',
  `lines` json DEFAULT NULL,
  `pausedAt` datetime DEFAULT NULL,
  `canceledAt` datetime DEFAULT NULL,
  `activatedAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`subscriptionId`),
  UNIQUE KEY `uxBillingSubscriptionShopifyShopifyId` (`shopifySubscriptionId`),
  KEY `ixBillingSubscriptionShopifyNextBillingAt` (`nextBillingAt`),
  KEY `ixBillingSubscriptionShopifyStatusUpdatedAt` (`status`,`updatedAt`),
  KEY `ixBillingSubscriptionShopifyCustomerId` (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `billingSubscriptionStripe` (
  `subscriptionId` varchar(50) NOT NULL,
  `customerId` varchar(50) NOT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `activatedAt` datetime DEFAULT NULL,
  `subscriptionStart` date DEFAULT NULL,
  `subscriptionEnd` date DEFAULT NULL,
  `subscriptionValue` decimal(19,4) DEFAULT NULL,
  `subscriptionPeriodMonths` smallint DEFAULT NULL,
  `quantity` smallint DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `paymentType` varchar(50) DEFAULT NULL,
  `paymentDetails` json DEFAULT NULL,
  `discounts` json DEFAULT NULL,
  `addons` json DEFAULT NULL,
  `balance` decimal(19,4) DEFAULT NULL,
  `paymentMethod` varchar(100) DEFAULT NULL,
  `cancelAt` date DEFAULT NULL,
  `canceledAt` date DEFAULT NULL,
  `isPaused` tinyint(1) NOT NULL DEFAULT '0',
  `resumesAt` date DEFAULT NULL,
  `isTrial` tinyint(1) NOT NULL DEFAULT '0',
  `trialEnd` date DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`subscriptionId`),
  KEY `ixBillingSubscriptionStripeStatusUpdatedAt` (`status`,`updatedAt`),
  KEY `ixBillingSubscriptionStripeCreatedAt` (`createdAt`),
  KEY `ixBillingSubscriptionStripeComposite` (`subscriptionId`,`customerId`,`status`),
  KEY `ixBillingSubscriptionStripeCustomerId` (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `blacklist` (
  `blacklistTypeId` tinyint NOT NULL,
  `value` varchar(255) NOT NULL,
  `notes` varchar(1000) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `createdBy` varchar(255) NOT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `modifiedBy` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`blacklistTypeId`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `blacklistType` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `bleMesh` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `name` varchar(150) NOT NULL,
  `address` varchar(500) NOT NULL,
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBLEMesh_clientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `bleMeshConfiguration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `imeiMain` varchar(15) NOT NULL,
  `btmacMain` varchar(20) NOT NULL,
  `bleMac` varchar(20) NOT NULL,
  `connectionType` varchar(20) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuBLEMeshConfigurationImeiMainBLEMac` (`imeiMain`,`bleMac`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `boundary` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `type` char(1) DEFAULT NULL,
  `buffer` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `createdBy` varchar(50) DEFAULT NULL,
  `modifiedBy` varchar(50) DEFAULT NULL,
  `clientId` int NOT NULL DEFAULT '0',
  `location` varchar(255) DEFAULT NULL,
  `status` tinyint NOT NULL DEFAULT '1',
  `radiusDisplayUnit` char(2) DEFAULT NULL,
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `geometry` mediumtext,
  `color` varchar(25) DEFAULT NULL,
  `isUserCreated` tinyint(1) NOT NULL DEFAULT '1',
  `area` float DEFAULT NULL,
  `centerLat` decimal(8,6) DEFAULT NULL,
  `centerLon` decimal(9,6) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `placeTypeId` smallint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixBoundaryStatus` (`status`),
  KEY `ixBoundaryClientIdStatus` (`clientId`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `cellTower` (
  `id` int NOT NULL,
  `mcc` tinyint DEFAULT NULL,
  `mnc` tinyint DEFAULT NULL,
  `lac` mediumint DEFAULT NULL,
  `radio` varchar(5) DEFAULT NULL,
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `range` mediumint DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `churnNotification` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `subscriptionId` varchar(256) DEFAULT NULL,
  `customerId` varchar(255) DEFAULT NULL,
  `clientId` int DEFAULT NULL,
  `billingInterval` int DEFAULT NULL,
  `priority` enum('low','moderate','high','critical') DEFAULT NULL,
  `phase` enum('payment_failed','past_due','account_locked','prepaid_add_card','prepaid_ending_soon','add_backup_card') DEFAULT NULL,
  `paymentAttempt` int DEFAULT '0',
  `isPaymentFailedPushSent` tinyint(1) DEFAULT '0',
  `isPastDuePushSent` tinyint(1) DEFAULT '0',
  `isAccountLockedPushSent` tinyint(1) DEFAULT '0',
  `isPrepaidAddCardPushSent` tinyint(1) DEFAULT '0',
  `isPrepaidEndingSoonPushSent` tinyint(1) DEFAULT '0',
  `isAddBackupCardPushSent` tinyint(1) DEFAULT '0',
  `resolved` tinyint(1) NOT NULL DEFAULT '0',
  `daysInChurn` int DEFAULT '0',
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuChurnNotificationSubscriptionIdPhase` (`subscriptionId`,`phase`),
  KEY `ixChurnNotificationPhaseResolved` (`phase`,`resolved`),
  KEY `ixChurnNotificationClientIdResolved` (`clientId`,`resolved`),
  KEY `ixChurnNotificationResolved` (`resolved`),
  KEY `ixChurnNotificationCustomerId` (`customerId`),
  KEY `ixChurnNotificationSubscriptionId` (`subscriptionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `client` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `uuid` binary(16) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `modifiedBy` varchar(50) DEFAULT NULL,
  `createdBy` varchar(50) DEFAULT NULL,
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `clientTypeId` tinyint NOT NULL DEFAULT '1',
  `salesforceId` varchar(50) DEFAULT NULL,
  `accumaticaId` varchar(50) DEFAULT NULL,
  `expirationDate` datetime DEFAULT NULL,
  `useType` varchar(25) DEFAULT NULL,
  `btCustomerId` varchar(50) DEFAULT NULL,
  `activationCampaign` varchar(50) DEFAULT NULL,
  `organizationId` int DEFAULT NULL,
  `demoAccountExpirationDate` datetime DEFAULT NULL,
  `demoAccountTemplateId` smallint DEFAULT NULL,
  `lastIterableSyncDate` datetime DEFAULT NULL,
  `activeOrRecentlyChurned` tinyint(1) DEFAULT NULL,
  `status` char(1) DEFAULT 'A',
  `lockedReason` varchar(255) DEFAULT NULL,
  `lockedAt` datetime DEFAULT NULL,
  `lockedBy` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixClientComposite` (`id`,`email`,`accumaticaId`,`clientTypeId`),
  KEY `ixClientEmail` (`email`),
  KEY `ixClientCreatedAt` (`createdAt`),
  KEY `ixClientClientTypeId` (`clientTypeId`),
  KEY `ixClientEmailLower` ((lower(`email`))),
  FULLTEXT KEY `ftClientSearch` (`name`,`email`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientAlertConfiguration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `alertTypeId` tinyint NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `alertValue` varchar(10) DEFAULT NULL,
  `alertMetadata` varchar(255) DEFAULT NULL,
  `filter` json DEFAULT NULL,
  `readOnly` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `createdBy` varchar(100) DEFAULT NULL,
  `modifiedBy` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixClientAlertConfigurationClientIdCreatedAt` (`clientId`,`createdAt`),
  KEY `ixClientAlertConfigurationClientIdAlertTypeId` (`clientId`,`alertTypeId`),
  KEY `ixClientAlertConfigurationAlertTypeIdStatus` (`alertTypeId`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientAlertRecipient` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientAlertConfigurationId` int NOT NULL,
  `userId` binary(16) DEFAULT NULL,
  `roleId` tinyint DEFAULT NULL,
  `isEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `smsNotification` tinyint(1) NOT NULL DEFAULT '1',
  `emailNotification` tinyint(1) NOT NULL DEFAULT '1',
  `appNotification` tinyint(1) NOT NULL DEFAULT '1',
  `pushNotification` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ixClientAlertConfigurationId` (`clientAlertConfigurationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientApiToken` (
  `clientId` int NOT NULL,
  `appClientId` varchar(64) NOT NULL,
  `userId` binary(16) NOT NULL,
  `appClientName` varchar(64) NOT NULL,
  `appClientSecret` varchar(64) NOT NULL,
  `scopes` json NOT NULL,
  `createdAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`clientId`,`appClientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientApiTokenShareHistory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `appClientId` varchar(64) NOT NULL,
  `clientId` int NOT NULL,
  `sharedBy` varchar(100) NOT NULL,
  `sharedWith` varchar(100) NOT NULL,
  `sharedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_clientId_sharedAt` (`clientId`,`sharedAt`),
  KEY `ix_appClientId_sharedAt` (`appClientId`,`sharedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientAppFeature` (
  `clientId` int NOT NULL,
  `appFeatureTypeId` int NOT NULL,
  `entityId` int NOT NULL,
  `statusId` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`clientId`,`appFeatureTypeId`,`entityId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientConfiguration` (
  `clientId` int NOT NULL,
  `movementTripThreshold` double DEFAULT NULL,
  `stopTripThreshold` double DEFAULT NULL,
  `mapUpdateMode` tinyint DEFAULT NULL,
  `maxUsersAllowed` smallint NOT NULL DEFAULT '10',
  `ssoDomain` varchar(250) DEFAULT NULL,
  `ssoMetadata` json DEFAULT NULL,
  `assetProfileStatusLabels` json DEFAULT NULL,
  `apiKeyId` varchar(64) DEFAULT NULL,
  `assetProfileStatusLabelsEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `usagePlanTier` enum('STANDARD','EXTENDED') NOT NULL DEFAULT 'STANDARD',
  `businessHoursEnabled` tinyint(1) DEFAULT '0',
  `businessHours` json DEFAULT NULL,
  `fleetUtilizationBenchmarkPercent` smallint NOT NULL DEFAULT '40',
  `fleetUtilizationIdleDays` smallint NOT NULL DEFAULT '5',
  `cellularAssistEnabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`clientId`),
  UNIQUE KEY `ssoDomain` (`ssoDomain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientDeviceIcon` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `fileName` varchar(255) NOT NULL,
  `s3FilesPath` varchar(255) DEFAULT NULL,
  `altText` varchar(100) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixClientDeviceIcon_clientId_fileName` (`clientId`,`fileName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientDeviceSetting` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `movingPingFrequency` mediumint DEFAULT NULL,
  `stoppedPingFrequency` mediumint DEFAULT NULL,
  `fixedStatus` varchar(3) DEFAULT NULL,
  `generateTrips` tinyint(1) NOT NULL DEFAULT '1',
  `generateTripsChangedAt` datetime DEFAULT NULL,
  `stopThresholdMinutes` smallint DEFAULT NULL,
  `offlineThresholdMinutes` smallint DEFAULT NULL,
  `movementThresholdSpeed` float DEFAULT NULL,
  `externalBatteryInformation` tinyint(1) DEFAULT '0',
  `reportingMode` varchar(30) DEFAULT '',
  `starterInterruptInstalled` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuUserDeviceSettingClientIdImei` (`clientId`,`imei`),
  KEY `ixClientDeviceSettingImei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientIngestorConfiguration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `userActivationId` int NOT NULL,
  `tokenUrl` varchar(250) NOT NULL,
  `fleetUrl` varchar(250) NOT NULL,
  `requestParams` json NOT NULL,
  `isEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `systemName` varchar(250) NOT NULL DEFAULT 'aemp',
  `synchroParams` json DEFAULT NULL,
  `providerId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixClientIngestorConfigurationClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientIssuesConfigurations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `issueTypeId` int NOT NULL,
  `dismissed` tinyint(1) DEFAULT '0',
  `thresholdValue` int DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `modifiedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuClientIssuesConfigurationClientIdIssueTypeId` (`clientId`,`issueTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientStatusHistory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `status` char(1) NOT NULL DEFAULT 'A',
  `modifiedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedBy` varchar(255) DEFAULT NULL,
  `lockedReason` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_clientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `clientType` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `ixClientTypeName` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `country` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `currencies` varchar(255) DEFAULT NULL,
  `capital` varchar(50) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `subregion` varchar(50) DEFAULT NULL,
  `languages` varchar(255) DEFAULT NULL,
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `borders` varchar(255) DEFAULT NULL,
  `area` int DEFAULT NULL,
  `internationalDirectDialing` varchar(10) DEFAULT NULL,
  `alpha2Code` varchar(2) DEFAULT NULL,
  `alpha3Code` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixCountryName` (`name`),
  KEY `ixCountryAlpha2Code` (`alpha2Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customReport` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'Active',
  `dataSourceId` tinyint NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  `lastAccessedAt` datetime DEFAULT NULL,
  `createdBy` binary(16) DEFAULT NULL,
  `modifiedBy` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixu_clientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customReportColumn` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `key` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `columnCategoryId` tinyint NOT NULL,
  `ordinalPosition` tinyint NOT NULL,
  `requiredDateFilters` tinyint(1) NOT NULL DEFAULT '0',
  `dataType` varchar(20) NOT NULL DEFAULT 'text',
  `status` varchar(20) NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_name_columnCategoryId` (`name`,`columnCategoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customReportColumnCategory` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `dataSourceId` tinyint NOT NULL,
  `ordinalPosition` tinyint NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_name_dataSourceId` (`name`,`dataSourceId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customReportDataSource` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sourceTableName` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `ordinalPosition` tinyint NOT NULL,
  `icon` varchar(50) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `requiredDateFilters` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customReportSelectedColumn` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customReportId` int NOT NULL,
  `customReportColumnId` smallint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_reportId_columnId` (`customReportId`,`customReportColumnId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customSecurityRole` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `permissions` json DEFAULT NULL,
  `createdAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_clientId_name` (`clientId`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `customerAttribute` (
  `clientId` int NOT NULL,
  `enterprise` tinyint(1) NOT NULL DEFAULT '0',
  `maintenanceModule` tinyint(1) NOT NULL DEFAULT '0',
  `icp` varchar(100) DEFAULT NULL,
  `billingMethod` varchar(100) DEFAULT NULL,
  `customerSegment` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `dataExport` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `completedAt` datetime DEFAULT NULL,
  `dataType` varchar(100) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `parameters` json DEFAULT NULL,
  `exportLocation` varchar(500) DEFAULT NULL,
  `downloadLink` varchar(3000) DEFAULT NULL,
  `imei` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `dataSourceType` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deactivation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `billingSubscriptionId` varchar(50) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `billingPlatform` varchar(100) DEFAULT NULL,
  `deactivationReason` varchar(2000) DEFAULT NULL,
  `deactivationReasonId` tinyint DEFAULT NULL,
  `createdBy` varchar(50) NOT NULL,
  `createdAt` datetime NOT NULL,
  `scheduledDate` datetime DEFAULT NULL,
  `displayOrder` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixDeactivationImei` (`imei`),
  KEY `ixDeactivationDeactivationReasonId` (`deactivationReasonId`),
  KEY `ixDeactivationBillingSubscriptionIdDeactivationReasonId` (`billingSubscriptionId`,`deactivationReasonId`),
  KEY `ixDeactivationClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deactivationReason` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `text` varchar(255) NOT NULL,
  `code` varchar(50) NOT NULL,
  `customerVisible` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountDeviceData` (
  `imei` varchar(15) NOT NULL,
  `demoAccountTemplateDeviceId` smallint NOT NULL,
  `isBusy` tinyint(1) NOT NULL DEFAULT '0',
  `currentSiteId` int DEFAULT NULL,
  `nextAvailableAt` datetime DEFAULT NULL,
  `inMesh` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountSiteLocation` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `demoAccountTemplateId` smallint NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `latitude` decimal(8,6) NOT NULL,
  `longitude` decimal(9,6) NOT NULL,
  `boundaryRadius` smallint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_demoAccountTemplateId` (`demoAccountTemplateId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountTemplate` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `randomMovementEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `maxMovingDevices` smallint NOT NULL DEFAULT '2',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountTemplateAssetProfile` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `demoAccountTemplateDeviceId` smallint NOT NULL,
  `assetName` varchar(50) NOT NULL,
  `assetProfileTypeId` smallint DEFAULT NULL,
  `make` varchar(50) DEFAULT NULL,
  `model` varchar(50) DEFAULT NULL,
  `serialNumber` varchar(50) DEFAULT NULL,
  `year` smallint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountTemplateBleMesh` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `demoAccountTemplateId` smallint NOT NULL,
  `name` varchar(50) NOT NULL,
  `originLocationId` smallint NOT NULL,
  `destinationLocationId` smallint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_demoAccountTemplateId` (`demoAccountTemplateId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountTemplateDevice` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `demoAccountTemplateId` smallint NOT NULL,
  `trackerName` varchar(50) NOT NULL,
  `trackerDescription` varchar(500) NOT NULL,
  `deviceTypeId` smallint NOT NULL,
  `demoAccountTemplateBleMeshId` smallint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountTripTemplate` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `demoAccountTemplateId` smallint NOT NULL,
  `startSiteLocationId` smallint NOT NULL,
  `endSiteLocationId` smallint NOT NULL,
  `distance` int NOT NULL,
  `duration` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_demoAccountTemplateId` (`demoAccountTemplateId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoAccountTripTemplatePosition` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `demoAccountTripTemplateId` smallint NOT NULL,
  `latitude` decimal(8,6) NOT NULL,
  `longitude` decimal(9,6) NOT NULL,
  `speed` float NOT NULL,
  `elapsedTime` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_demoAccountTripTemplateId` (`demoAccountTripTemplateId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoDeviceData` (
  `imei` varchar(15) NOT NULL,
  `associatedDeviceImei` varchar(15) DEFAULT NULL,
  `deviceAssociationStartDate` datetime DEFAULT NULL,
  `deviceAssociationEndDate` datetime DEFAULT NULL,
  `lastProcessedAt` datetime DEFAULT NULL,
  `lastProcessedSendTime` datetime DEFAULT NULL,
  PRIMARY KEY (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoFormField` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key` varchar(64) NOT NULL,
  `label` varchar(255) NOT NULL,
  `type` varchar(32) NOT NULL,
  `required` tinyint(1) NOT NULL DEFAULT '0',
  `options` json DEFAULT NULL,
  `sortOrder` int NOT NULL DEFAULT '0',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_b58e32af89827416e8833e9b69` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `demoLead` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `industry` varchar(255) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `source` varchar(64) DEFAULT NULL,
  `syncedToHubspot` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `idxDemoLeadEmail` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `device` (
  `id` int NOT NULL AUTO_INCREMENT,
  `identifier` varchar(100) NOT NULL,
  `identifierKey` varchar(255) NOT NULL,
  `deviceTypeId` smallint NOT NULL,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(255) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `iccid` varchar(25) DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `createdBy` varchar(50) DEFAULT NULL,
  `modifiedBy` varchar(50) DEFAULT NULL,
  `forward` tinyint(1) DEFAULT NULL,
  `color` varchar(25) DEFAULT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `customIconId` int DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `assetCategoryId` tinyint DEFAULT NULL,
  `usageRegion` varchar(25) DEFAULT NULL,
  `btmac` varchar(20) DEFAULT NULL,
  `serialNumber` varchar(36) DEFAULT NULL,
  `assignedTo` varchar(255) DEFAULT NULL,
  `assignmentMode` varchar(50) DEFAULT NULL,
  `demoAccountTemplateDeviceId` smallint DEFAULT NULL,
  `extendedAttributes` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuDeviceIdentifierKey` (`identifierKey`),
  KEY `ixDeviceSearch` (`clientId`,`status`,`imei`,`createdAt`,`deviceTypeId`),
  KEY `ixDeviceCreatedAt` (`createdAt`),
  KEY `ixDeviceClientIdImei` (`clientId`,`imei`),
  KEY `ixDeviceStatus` (`status`),
  KEY `ixDeviceImei` (`imei`),
  KEY `ixDeviceIccidImei` (`iccid`,`imei`),
  KEY `ixDeviceDeviceTypeId` (`deviceTypeId`),
  KEY `ixDeviceImeiLower` ((lower(`imei`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceBehavior` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `deviceTypeId` smallint NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `type` varchar(25) DEFAULT NULL,
  `status` varchar(10) NOT NULL,
  `behaviorName` varchar(50) NOT NULL,
  `behaviorParams` json DEFAULT NULL,
  `extraParams` json DEFAULT NULL,
  `pendingNotes` varchar(1000) DEFAULT NULL,
  `clientId` int DEFAULT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `confirmationNotes` varchar(1000) DEFAULT NULL,
  `ordinalPosition` tinyint NOT NULL DEFAULT '1',
  `stage` varchar(10) DEFAULT NULL,
  `additionalSettings` json DEFAULT NULL,
  `blocking` tinyint(1) DEFAULT '1',
  `displayType` varchar(25) DEFAULT NULL,
  `minWebVersion` varchar(20) DEFAULT NULL,
  `minAppVersion` varchar(20) DEFAULT NULL,
  `minProtocolVersion` varchar(255) DEFAULT NULL,
  `maxProtocolVersion` varchar(255) DEFAULT NULL,
  `isACKBehavior` tinyint(1) DEFAULT '1',
  `transmissionType` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixDeviceBehaviorOrdinalPosition` (`ordinalPosition`),
  KEY `ixDeviceBehaviorType` (`type`),
  KEY `ixDeviceBehaviorImei` (`imei`),
  KEY `ixDeviceBehaviorClientId` (`clientId`),
  KEY `ixDeviceBehaviorDeviceTypeId` (`deviceTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceBleMesh` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `deviceId` int NOT NULL,
  `bleMeshId` int NOT NULL,
  `status` varchar(100) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuDeviceBleMesh_deviceId` (`deviceId`),
  KEY `ixDeviceBleMesh_clientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceBleMeshUpdateTask` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `deviceId` int NOT NULL,
  `deviceBleMeshId` int NOT NULL,
  `status` varchar(100) NOT NULL,
  `behaviorName` varchar(100) DEFAULT NULL,
  `behaviorParams` json DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixDeviceBleMeshUpdateTask_clientId_modifiedAt` (`clientId`,`modifiedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraConfiguration` (
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `configurationType` varchar(20) NOT NULL,
  `currentConfiguration` json DEFAULT NULL,
  `pendingConfiguration` json DEFAULT NULL,
  `updateConfigPendingCommandId` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`clientId`,`imei`,`configurationType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraMedia` (
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `mediaFileName` varchar(255) NOT NULL,
  `s3FilesPath` varchar(255) DEFAULT NULL,
  `thumbnailsFileName` varchar(255) DEFAULT NULL,
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `messageId` binary(16) DEFAULT NULL,
  `eventTypeId` smallint DEFAULT NULL,
  `mediaType` enum('image','video') DEFAULT NULL COMMENT 'The type of the media: image or video',
  `cameraSource` enum('front','interior') DEFAULT NULL COMMENT 'The camera source: front or interior',
  `mediaLocation` enum('sdCard','server') NOT NULL DEFAULT 'sdCard' COMMENT 'The location of the media: sdCard or server',
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `tripId` binary(16) DEFAULT NULL,
  `requestedVideoDataId` int DEFAULT NULL,
  `requestedPictureDataId` int DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`clientId`,`imei`,`mediaFileName`),
  KEY `ixDeviceCameraMedia_TripId` (`tripId`),
  KEY `ixClientIdImeiCreatedAt` (`clientId`,`imei`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraMediaEventType` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `code` varchar(2) NOT NULL,
  `name` varchar(100) NOT NULL,
  `bitPosition` smallint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixCode` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraMediaStreamingData` (
  `imei` varchar(15) NOT NULL,
  `cameraSource` enum('front','interior') NOT NULL COMMENT 'The camera source: front or interior',
  `mediaLiveInputId` varchar(50) DEFAULT NULL,
  `mediaLiveChannelId` varchar(50) DEFAULT NULL,
  `rtmpServerUrl` varchar(100) DEFAULT NULL,
  `rtmpStreamKey` varchar(40) DEFAULT NULL,
  `outputUrl` varchar(255) DEFAULT NULL,
  `status` enum('requested','streaming','closed','failed','closing') DEFAULT NULL,
  `commandSequenceNumber` varchar(10) DEFAULT NULL,
  `lastRequestedAt` datetime DEFAULT NULL,
  `lastRequestedBy` binary(16) DEFAULT NULL,
  `lastViewerSeenAt` datetime DEFAULT NULL,
  PRIMARY KEY (`imei`,`cameraSource`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraMediaStreamingSession` (
  `id` binary(16) NOT NULL,
  `imei` varchar(15) NOT NULL,
  `cameraSource` enum('front','interior') NOT NULL COMMENT 'The camera source: front or interior',
  `startedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastHeartbeatAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `closedAt` datetime DEFAULT NULL,
  `userId` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraMediaSyncStatus` (
  `imei` varchar(15) NOT NULL,
  `startTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `retries` int NOT NULL DEFAULT '0',
  `totalFrames` int NOT NULL DEFAULT '0',
  `syncedFrames` int NOT NULL DEFAULT '0',
  `commandSequenceNumber` varchar(10) DEFAULT NULL,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `syncedFrameIds` json DEFAULT NULL,
  `filesInCard` tinyint(1) DEFAULT '1',
  `deleteCmdSequenceNumber` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`imei`,`startTime`,`endTime`),
  KEY `ixDeviceCameraMediaSyncStatus_imei_status_updatedAt` (`imei`,`status`,`updatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraRequestedPictureData` (
  `id` int NOT NULL AUTO_INCREMENT,
  `commandSequenceNumber` varchar(10) NOT NULL,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `cameraSource` int NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `receivedFileName` varchar(255) DEFAULT NULL,
  `receivedFileDate` datetime DEFAULT NULL,
  `requestedBy` binary(16) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_clientId_imei_createdAt` (`clientId`,`imei`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCameraRequestedVideoData` (
  `id` int NOT NULL AUTO_INCREMENT,
  `commandSequenceNumber` varchar(10) NOT NULL,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `startTime` datetime NOT NULL,
  `duration` int NOT NULL,
  `cameraSource` int NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `totalFilesCount` int DEFAULT '0',
  `receivedFilesCount` int DEFAULT '0',
  `receivedFiles` json DEFAULT NULL,
  `expectedFiles` json DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `resultFileName` varchar(255) DEFAULT NULL,
  `failureReason` varchar(255) DEFAULT NULL,
  `parentRequestId` int DEFAULT NULL,
  `retryCount` int NOT NULL DEFAULT '0',
  `lastRetriedAt` datetime DEFAULT NULL,
  `requestedBy` binary(16) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_clientId_imei_createdAt` (`clientId`,`imei`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceChangesHistory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `assignedTo` varchar(255) NOT NULL,
  `startDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `endDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixuDeviceChangesHistoryClientIdImei` (`clientId`,`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceCustomConfiguration` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `clientId` int DEFAULT NULL,
  `deviceTypeId` smallint DEFAULT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `configFileUrl` varchar(1000) DEFAULT NULL,
  `skipActivationDefaults` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime DEFAULT NULL,
  `po` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixDeviceCustomConfigurationsClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceEventsTriggerConfiguration` (
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `eventBehaviorType` enum('takePicture','takeVideo','uploadPicture','uploadVideo') NOT NULL,
  `eventsConfiguration` json DEFAULT NULL,
  `pendingEventsConfiguration` json DEFAULT NULL,
  `updateConfigPendingCommandId` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  `extraParams` json DEFAULT NULL,
  `pendingExtraParams` json DEFAULT NULL,
  PRIMARY KEY (`clientId`,`imei`,`eventBehaviorType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceHistory` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `clientId` int DEFAULT NULL,
  `imei` varchar(15) NOT NULL,
  `trackerName` varchar(255) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `createAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceModelListener` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `url` varchar(1000) DEFAULT NULL,
  `portNumber` smallint DEFAULT NULL,
  `protocol` varchar(5) DEFAULT NULL,
  `deviceTypeId` smallint DEFAULT NULL,
  `status` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `devicePairing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `imeiMain` varchar(15) NOT NULL,
  `btmacMain` varchar(20) NOT NULL,
  `imeiSecondary` varchar(15) NOT NULL,
  `btmacSecondary` varchar(20) NOT NULL,
  `pairingMode` varchar(20) DEFAULT NULL,
  `connectionType` varchar(20) DEFAULT NULL,
  `mainDeviceStatus` varchar(1000) DEFAULT NULL,
  `secondaryDeviceStatus` varchar(1000) DEFAULT NULL,
  `notes` varchar(1000) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuDevicePairingImeiMainImeiSecondary` (`imeiMain`,`imeiSecondary`),
  UNIQUE KEY `ixuDevicePairingImeiMain` (`imeiMain`),
  UNIQUE KEY `ixuDevicePairingImeiSecondary` (`imeiSecondary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `devicePromotion` (
  `imei` varchar(15) NOT NULL,
  `promotionId` int NOT NULL,
  PRIMARY KEY (`imei`,`promotionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `devicePropertyMapping` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `deviceTypeId` int NOT NULL,
  `imei` varchar(16) DEFAULT NULL,
  `rawProperty` varchar(255) NOT NULL,
  `displayName` varchar(255) NOT NULL,
  `description` text,
  `sensorConfigId` binary(16) NOT NULL,
  `configOverride` json DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuDevicePropertyMappingDeviceTypeImeiRawProperty` (`clientId`,`deviceTypeId`,`imei`,`rawProperty`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceReplacement` (
  `id` int NOT NULL AUTO_INCREMENT,
  `imeiOld` varchar(15) NOT NULL,
  `iccidOld` varchar(25) NOT NULL,
  `imeiNew` varchar(15) NOT NULL,
  `iccidNew` varchar(25) NOT NULL,
  `userId` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `createAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ixDeviceReplacementImeiOld` (`imeiOld`),
  KEY `ixDeviceReplacementClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceStatus` (
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `created` datetime NOT NULL,
  `sendTime` datetime NOT NULL,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`imei`),
  KEY `ixDeviceStatusSendTime` (`sendTime`),
  KEY `ixDeviceStatusClientId_SendTime` (`clientId`,`sendTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceTask` (
  `taskScheduleId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `startingOdometer` double DEFAULT NULL,
  `startingActiveHours` double DEFAULT NULL,
  PRIMARY KEY (`taskScheduleId`,`imei`),
  KEY `ixDeviceTask_Imei` (`imei`),
  KEY `ixDeviceTask_ClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceType` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `brand` varchar(50) NOT NULL,
  `model` varchar(50) NOT NULL,
  `modelFamily` varchar(50) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `url` varchar(1000) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `pictureUrl` varchar(1000) DEFAULT NULL,
  `userManualUrl` varchar(1000) DEFAULT NULL,
  `notes` varchar(2000) DEFAULT NULL,
  `supported` tinyint(1) DEFAULT NULL,
  `activatable` tinyint(1) DEFAULT NULL,
  `selfActivatable` tinyint(1) DEFAULT NULL,
  `port` smallint DEFAULT NULL,
  `protocolMatch` varchar(5) DEFAULT NULL,
  `stage` varchar(10) DEFAULT NULL,
  `identifierType` varchar(20) NOT NULL,
  `landingPage` varchar(1000) DEFAULT NULL,
  `troubleshooting` varchar(1000) DEFAULT NULL,
  `protocol` varchar(50) DEFAULT NULL,
  `readOnly` tinyint(1) DEFAULT NULL,
  `customerFacingModel` varchar(50) DEFAULT NULL,
  `customerFacingName` varchar(50) DEFAULT NULL,
  `hapnModel` varchar(50) DEFAULT NULL,
  `hapnName` varchar(50) DEFAULT NULL,
  `acumaticaInventoryIds` json DEFAULT NULL,
  `assetTrackerLongSleep` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceTypeFeature` (
  `deviceTypeId` smallint NOT NULL,
  `featureKey` varchar(50) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`deviceTypeId`,`featureKey`),
  KEY `ixDeviceTypeFeatureFeatureKey` (`featureKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceTypeFirmware` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `deviceTypeId` smallint NOT NULL,
  `sourceFirmwareVersion` varchar(25) DEFAULT NULL,
  `firmwareVersion` varchar(25) DEFAULT NULL,
  `firmwareCode` varchar(50) DEFAULT NULL,
  `firmwareUrl` varchar(1000) DEFAULT NULL,
  `releaseDate` datetime DEFAULT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  `upgradeType` varchar(25) DEFAULT NULL,
  `postUpgradeConfig` varchar(1000) DEFAULT NULL,
  `stage` varchar(25) DEFAULT NULL,
  `parentId` mediumint DEFAULT NULL,
  `maxDeployPercentage` smallint DEFAULT NULL,
  `qmsRate` smallint DEFAULT NULL,
  `pendingTimeout` smallint DEFAULT NULL,
  `forceSMS` tinyint(1) NOT NULL DEFAULT '0',
  `MCUFirmwareVersion` varchar(25) DEFAULT NULL,
  `sourceMCUFirmwareVersion` varchar(25) DEFAULT NULL,
  `sourceBTFirmwareVersion` varchar(25) DEFAULT NULL,
  `BTFirmwareVersion` varchar(25) DEFAULT NULL,
  `sourceModemFirmwareVersion` varchar(25) DEFAULT NULL,
  `modemFirmwareVersion` varchar(25) DEFAULT NULL,
  `sourcePlatformFirmwareVersion` varchar(25) DEFAULT NULL,
  `platformFirmwareVersion` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixDeviceTypeFirmwareDeviceTypeId` (`deviceTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `deviceTypePromotion` (
  `deviceTypeId` smallint NOT NULL,
  `promotionId` int NOT NULL,
  PRIMARY KEY (`deviceTypeId`,`promotionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `emailDomain` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `isBusiness` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `entityTag` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tagId` int NOT NULL,
  `clientId` int NOT NULL,
  `entityType` enum('Device','Boundary','User','UserInvitation','ClientAlertConfiguration','LiveLink') NOT NULL,
  `entityId` int NOT NULL,
  `metadata` json DEFAULT NULL,
  `createdBy` varchar(50) NOT NULL,
  `createdAt` datetime NOT NULL,
  `modifiedBy` varchar(50) DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `userId` binary(16) DEFAULT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `isSecurityGroup` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixEntityTagClientIdEntityIdTagIdEntityType` (`clientId`,`entityId`,`tagId`,`entityType`),
  KEY `ixEntityTagEntityIdEntityTypeIsSecurityGroupTagId` (`entityId`,`entityType`,`isSecurityGroup`,`tagId`),
  KEY `ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId` (`clientId`,`userId`,`entityType`,`isSecurityGroup`,`tagId`),
  KEY `ixEntityTagUserIdEntityTypeIsSecurityGroupTagId` (`userId`,`entityType`,`isSecurityGroup`,`tagId`),
  KEY `ixEntityTagUserIdIsSecurityGroupTagId` (`userId`,`isSecurityGroup`,`tagId`),
  KEY `ixEntityTagImeiIsSecurityGroupTagId` (`imei`,`isSecurityGroup`,`tagId`),
  KEY `ixEntityTagTagId` (`tagId`),
  KEY `ixEntityTagEntityType` (`entityType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `entityType` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `ephemeralBoundary` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `geometry` json DEFAULT NULL,
  `startAt` datetime NOT NULL,
  `expiresAt` datetime NOT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'active',
  `firedAt` datetime DEFAULT NULL,
  `firedLat` decimal(8,6) DEFAULT NULL,
  `firedLon` decimal(9,6) DEFAULT NULL,
  `firedDistanceMeters` int DEFAULT NULL,
  `notified` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `createdBy` varchar(100) DEFAULT NULL,
  `modifiedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `modifiedBy` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixEphemeralBoundaryStatus_ExpiresAt` (`status`,`expiresAt`),
  KEY `ixEphemeralBoundaryClientId_Imei_Status` (`clientId`,`imei`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `feature` (
  `key` varchar(50) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `tags` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `firmwareAutoUpgradeBlacklist` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `clientId` int DEFAULT NULL,
  `deviceTypeId` int DEFAULT NULL,
  `imei` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuFirmwareAutoUpgradeBlacklistClientIdDeviceTypeId` (`clientId`,`deviceTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `firmwareUpgradeTask` (
  `id` binary(16) NOT NULL,
  `deviceTypeFirmwareId` mediumint NOT NULL,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `status` varchar(40) NOT NULL,
  `behaviorName` varchar(25) DEFAULT NULL,
  `behaviorParams` json DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime DEFAULT NULL,
  `preConfig` text,
  `postConfig` text,
  `commandsTaskId` varchar(50) DEFAULT NULL,
  `postUpgradeCompleted` tinyint(1) DEFAULT '0',
  `fwCompletedOn` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_deviceTypeFirmwareId_status` (`deviceTypeFirmwareId`,`status`),
  KEY `ixFirmwareUpgradeTaskStatusEtc` (`status`,`postUpgradeCompleted`,`deviceTypeFirmwareId`,`modifiedAt`),
  KEY `ixFirmwareUpgradeTaskImeiModifiedAt` (`imei`,`modifiedAt`),
  KEY `ixFirmwareUpgradeTaskDeviceTypeFirmwareIdModifiedAt` (`deviceTypeFirmwareId`,`modifiedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `historicalBillingPlan` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `planId` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(256) DEFAULT NULL,
  `consumer` varchar(30) NOT NULL,
  `modelFamily` varchar(30) NOT NULL,
  `model` varchar(30) DEFAULT NULL,
  `planCode` varchar(100) NOT NULL,
  `price` decimal(19,4) NOT NULL,
  `totalPrice` decimal(19,4) NOT NULL,
  `billingInterval` smallint NOT NULL,
  `annual` tinyint(1) NOT NULL,
  `prepaid` tinyint(1) NOT NULL,
  `legacy` tinyint(1) NOT NULL,
  `test` tinyint(1) NOT NULL,
  `validFrom` date DEFAULT NULL,
  `validUntil` date DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `new` tinyint(1) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuHistoricalBillingPlanPlanIdConsumerModelValidFromValidUntil` (`planId`,`consumer`,`model`,`validFrom`,`validUntil`),
  KEY `ixHistoricalBillingPlanModelConsumerAnnualPlanCode` (`model`,`consumer`,`annual`,`planCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `iccidStatus` (
  `iccid` varchar(25) NOT NULL,
  `status` varchar(100) DEFAULT NULL,
  `processing` tinyint(1) DEFAULT NULL,
  `carrierFound` int DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`iccid`),
  KEY `ixIccidStatusModifiedAt` (`modifiedAt`),
  KEY `ixIccidStatusProcessing` (`processing`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `imeiIccidCarrier` (
  `id` int NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) DEFAULT NULL,
  `iccid` varchar(25) DEFAULT NULL,
  `carrier` tinyint DEFAULT NULL,
  `phone` varchar(25) DEFAULT NULL,
  `deviceTypeId` smallint DEFAULT NULL,
  `serviceProviderProductId` tinyint DEFAULT NULL,
  `isValid` tinyint(1) NOT NULL DEFAULT '1',
  `model` varchar(50) DEFAULT NULL,
  `realModel` varchar(50) DEFAULT NULL,
  `po` varchar(25) DEFAULT NULL,
  `outOfInventory` tinyint(1) NOT NULL DEFAULT '0',
  `apn` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `itsEsim` tinyint(1) DEFAULT '0',
  `btmac` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixImeiIccidCarrierProductId` (`serviceProviderProductId`),
  KEY `ixImeiIccidCarrierModifiedAt` (`modifiedAt`),
  KEY `ixImeiIccidCarrierImei` (`imei`),
  KEY `ixImeiIccidCarrierIccid` (`iccid`),
  KEY `ixImeiIccidCarrierDeviceTypeId` (`deviceTypeId`),
  KEY `ixImeiIccidCarrierCarrier` (`carrier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `imeiIccidHistory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `iccid` varchar(25) DEFAULT NULL,
  `imei` varchar(15) NOT NULL,
  `carrier` tinyint NOT NULL,
  `lastUpdatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ixImeiIccidHistoryImei` (`imei`),
  KEY `ixImeiIccidHistoryIccid` (`iccid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `imeiIccidImport` (
  `imei` varchar(15) NOT NULL,
  `iccid` varchar(25) NOT NULL,
  `btmac` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`imei`,`iccid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `industry` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `industry` varchar(100) NOT NULL,
  `mainIndustry` varchar(100) NOT NULL,
  `group` varchar(100) NOT NULL,
  `sector` varchar(100) NOT NULL,
  `displayName` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `ingestorProvider` (
  `id` int NOT NULL AUTO_INCREMENT,
  `providerName` varchar(250) NOT NULL,
  `providerKind` enum('aemp','samsara') NOT NULL DEFAULT 'aemp',
  `tokenUrl` varchar(250) NOT NULL DEFAULT '',
  `fleetUrl` varchar(250) NOT NULL,
  `requestMetadata` json DEFAULT NULL,
  `synchroDefaults` json DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `issueSuppressionRules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `issueTypeId` int NOT NULL,
  `createdBy` binary(16) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuIssueSuppressionRuleClientIdImeiIssueTypeId` (`clientId`,`imei`,`issueTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `issueTypes` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` varchar(2000) DEFAULT NULL,
  `resolution` varchar(2000) DEFAULT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `issues` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `issueTypeId` smallint NOT NULL,
  `value` varchar(5000) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `modifiedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `modifiedBy` binary(16) DEFAULT NULL,
  `sendTime` datetime(3) DEFAULT NULL,
  `messageId` binary(16) DEFAULT NULL,
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'active',
  `snoozedUntil` datetime(3) DEFAULT NULL,
  `resolvedAt` datetime(3) DEFAULT NULL,
  `resolvedConfirmationAt` datetime(3) DEFAULT NULL,
  `resolvedMessageId` binary(16) DEFAULT NULL,
  `resolvedBy` binary(16) DEFAULT NULL,
  `autoResolved` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ixIssueStatus_SnoozedUntil_ModifiedAt` (`status`,`snoozedUntil`,`modifiedAt`),
  KEY `ixIssueClientId_Status_IssueTypeId_CreatedAt` (`clientId`,`status`,`issueTypeId`,`createdAt`),
  KEY `ixIssueClientId_Imei_Status_CreatedAt` (`clientId`,`imei`,`status`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `jarvisWebGroupAccess` (
  `groupName` varchar(64) NOT NULL,
  `pages` json NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`groupName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `job` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `name` varchar(255) NOT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `customerPhone` varchar(20) DEFAULT NULL,
  `lifecycle` varchar(20) NOT NULL DEFAULT 'Upcoming',
  `healthSignals` json DEFAULT NULL,
  `proofState` varchar(20) NOT NULL DEFAULT 'NotArrived',
  `outcome` varchar(10) DEFAULT NULL,
  `startLat` double DEFAULT NULL,
  `startLng` double DEFAULT NULL,
  `startLabel` varchar(500) DEFAULT NULL,
  `startDetail` varchar(500) DEFAULT NULL,
  `startRadiusM` int NOT NULL DEFAULT '150',
  `endLat` double DEFAULT NULL,
  `endLng` double DEFAULT NULL,
  `endLabel` varchar(500) DEFAULT NULL,
  `endDetail` varchar(500) DEFAULT NULL,
  `endRadiusM` int NOT NULL DEFAULT '150',
  `scheduledStart` datetime DEFAULT NULL,
  `targetArrival` datetime DEFAULT NULL,
  `startedAt` datetime DEFAULT NULL,
  `firstObservedAt` datetime DEFAULT NULL,
  `arrivedAt` datetime DEFAULT NULL,
  `completedAt` datetime DEFAULT NULL,
  `cancelledAt` datetime DEFAULT NULL,
  `pingsInEndZone` int NOT NULL DEFAULT '0',
  `lastPositionAt` datetime DEFAULT NULL,
  `lastEvaluatedAt` datetime DEFAULT NULL,
  `etaArrivalAt` datetime DEFAULT NULL,
  `etaUpdatedAt` datetime DEFAULT NULL,
  `etaDriveSeconds` int DEFAULT NULL,
  `etaDistanceMeters` int DEFAULT NULL,
  `originalEtaDriveSeconds` int DEFAULT NULL,
  `etaWindowBufferMinutes` int DEFAULT NULL,
  `rules` json DEFAULT NULL,
  `timeline` json DEFAULT NULL,
  `recipients` json DEFAULT NULL,
  `liveLinkToken` binary(16) DEFAULT NULL,
  `showAttachmentsOnLiveLink` tinyint(1) NOT NULL DEFAULT '0',
  `priorMovingFrequency` int DEFAULT NULL,
  `priorStoppedFrequency` int DEFAULT NULL,
  `createdBy` binary(16) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `modifiedBy` binary(16) DEFAULT NULL,
  `modifiedAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `deletedAt` datetime(6) DEFAULT NULL,
  `deletedBy` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxJobLiveLinkToken` (`liveLinkToken`),
  KEY `ixJobClientIdCreatedBy` (`clientId`,`createdBy`),
  KEY `ixJobImeiLifecycle` (`imei`,`lifecycle`),
  KEY `ixJobClientIdLifecycle` (`clientId`,`lifecycle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `jobAttachment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `jobId` int NOT NULL,
  `clientId` int NOT NULL,
  `fileName` varchar(255) NOT NULL,
  `fileExtension` varchar(15) DEFAULT NULL,
  `contentType` varchar(100) DEFAULT NULL,
  `sizeBytes` int DEFAULT NULL,
  `s3FilesPath` varchar(500) NOT NULL,
  `createdBy` binary(16) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `deletedAt` datetime(6) DEFAULT NULL,
  `deletedBy` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixJobAttachmentJobId` (`jobId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `jobCustomerLink` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `jobId` int NOT NULL,
  `status` varchar(12) NOT NULL DEFAULT 'active',
  `phoneNumber` varchar(20) NOT NULL,
  `sentAt` datetime DEFAULT NULL,
  `lastSentAt` datetime DEFAULT NULL,
  `sendCount` int NOT NULL DEFAULT '0',
  `deliveryStatus` varchar(12) DEFAULT NULL,
  `deliveryStatusAt` datetime DEFAULT NULL,
  `openedAt` datetime DEFAULT NULL,
  `lastOpenedAt` datetime DEFAULT NULL,
  `openCount` int NOT NULL DEFAULT '0',
  `revokedAt` datetime DEFAULT NULL,
  `revokedBy` binary(16) DEFAULT NULL,
  `approachSmsSentAt` datetime DEFAULT NULL,
  `arrivedSmsSentAt` datetime DEFAULT NULL,
  `createdBy` binary(16) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `modifiedAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ixJobCustomerLinkPhoneNumberStatus` (`phoneNumber`,`status`),
  KEY `ixJobCustomerLinkJobIdStatus` (`jobId`,`status`),
  KEY `ixJobCustomerLinkClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `jobCustomerPageSetting` (
  `clientId` int NOT NULL,
  `displayName` varchar(255) DEFAULT NULL,
  `businessPhone` varchar(20) DEFAULT NULL,
  `logoFileName` varchar(255) DEFAULT NULL,
  `logoS3FilesPath` varchar(255) DEFAULT NULL,
  `brandColor` char(7) DEFAULT NULL,
  `reviewUrl` varchar(500) DEFAULT NULL,
  `paymentUrl` varchar(500) DEFAULT NULL,
  `sendDefaults` json DEFAULT NULL,
  `defaultJobRules` json DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `modifiedAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `modifiedBy` binary(16) DEFAULT NULL,
  PRIMARY KEY (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `latestUserAppInfo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `deviceOs` varchar(15) NOT NULL,
  `appVersion` varchar(25) NOT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixLatestUserAppInfoUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `liveLink` (
  `id` binary(16) NOT NULL,
  `name` varchar(100) NOT NULL,
  `clientId` int NOT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'active',
  `startDate` datetime DEFAULT NULL,
  `endDate` datetime DEFAULT NULL,
  `deviceAccess` varchar(255) NOT NULL,
  `devices` json DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `createdBy` varchar(100) DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `modifiedBy` varchar(100) DEFAULT NULL,
  `historyAccess` tinyint(1) NOT NULL DEFAULT '0',
  `estimatedTheftValue` decimal(19,4) DEFAULT NULL,
  `licensePlate` varchar(50) DEFAULT NULL,
  `suspectDescription` varchar(255) DEFAULT NULL,
  `logoFileName` varchar(255) DEFAULT NULL,
  `logoS3FilesPath` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixLiveLinkClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `liveLinkMedia` (
  `id` int NOT NULL AUTO_INCREMENT,
  `liveLinkId` binary(16) NOT NULL,
  `fileName` varchar(255) NOT NULL,
  `fileExtension` varchar(15) DEFAULT NULL,
  `s3FilesPath` varchar(255) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_liveLinkId_fileName` (`liveLinkId`,`fileName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `m2Sync` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `clientId` int DEFAULT NULL,
  `userId` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `braintreeCustomerId` varchar(255) DEFAULT NULL,
  `paymentMethodToken` varchar(255) DEFAULT NULL,
  `subscriptionId` varchar(255) DEFAULT NULL,
  `imei` varchar(255) DEFAULT NULL,
  `syncType` enum('CREATE_CUSTOMER','UPDATE_USER','UPDATE_CLIENT','AVALARA_EXEMPT','AVALARA_EXEMPT_REMOVE','AVALARA_ITEM','CREATE_DEVICE','REMOVE_DEVICE','UPDATE_DEVICE','SUSPEND_DEVICE','UNSUSPEND_DEVICE','CANCEL_DEVICE') DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `magento2Gps` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customerId` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `customerUpdatedAt` varchar(255) DEFAULT NULL,
  `createdIn` varchar(255) DEFAULT NULL,
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `middleName` varchar(255) DEFAULT NULL,
  `postcode` varchar(255) DEFAULT NULL,
  `prefix` varchar(255) DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `telephone` varchar(255) DEFAULT NULL,
  `countryId` varchar(255) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `btAccountId` varchar(255) DEFAULT NULL,
  `phoneCode` varchar(255) DEFAULT NULL,
  `phoneCodeCountryId` varchar(255) DEFAULT NULL,
  `pinCode` varchar(255) DEFAULT NULL,
  `stripeCustomerId` varchar(255) DEFAULT NULL,
  `activationCreated` varchar(255) DEFAULT NULL,
  `imei` varchar(255) DEFAULT NULL,
  `deviceId` varchar(255) DEFAULT NULL,
  `deviceType` varchar(255) DEFAULT NULL,
  `deviceColor` varchar(255) DEFAULT NULL,
  `btSubscriptionId` varchar(255) DEFAULT NULL,
  `servicePlanId` varchar(255) DEFAULT NULL,
  `billingInterval` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `orderId` varchar(255) DEFAULT NULL,
  `deviceUpdatedAt` varchar(255) DEFAULT NULL,
  `deviceCreatedAt` varchar(255) DEFAULT NULL,
  `importedDevice` varchar(255) DEFAULT NULL,
  `trackerName` varchar(255) DEFAULT NULL,
  `trackerUse` varchar(255) DEFAULT NULL,
  `deviceStatus` varchar(255) DEFAULT NULL,
  `planCode` varchar(255) DEFAULT NULL,
  `platformCode` varchar(255) DEFAULT NULL,
  `platformName` varchar(255) DEFAULT NULL,
  `stripeCreatedAt` varchar(255) DEFAULT NULL,
  `stripeUpdatedAt` varchar(255) DEFAULT NULL,
  `dateCaptured` varchar(255) DEFAULT NULL,
  `cancelationReason` text,
  `cancelationCreated` varchar(255) DEFAULT NULL,
  `cancelationReactivated` varchar(255) DEFAULT NULL,
  `clientId` varchar(255) DEFAULT NULL,
  `itemsForRecovery` text,
  `platform` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixMagento2GpsImei` (`imei`),
  KEY `ixMagento2GpsbtSubscriptionId` (`btSubscriptionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `magento2Plan` (
  `magentoPlanCode` varchar(100) NOT NULL,
  `magentoBillingInterval` varchar(50) NOT NULL,
  `planAnnual` tinyint(1) NOT NULL,
  `planId` varchar(100) NOT NULL,
  PRIMARY KEY (`magentoPlanCode`,`magentoBillingInterval`),
  KEY `ixMagento2PlanComposite` (`planId`,`magentoPlanCode`,`magentoBillingInterval`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `marketplacePromotion` (
  `marketplaceId` varchar(15) NOT NULL,
  `promotionId` int NOT NULL,
  PRIMARY KEY (`marketplaceId`,`promotionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `migration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `timestamp` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `mobileNetwork` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `mcc` smallint NOT NULL,
  `mnc` smallint NOT NULL,
  `networkName` varchar(255) DEFAULT NULL,
  `operatorName` varchar(100) DEFAULT NULL,
  `countryCode` varchar(50) DEFAULT NULL,
  `countryName` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `notificationRecipient` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `notificationType` tinyint NOT NULL,
  `recipient` varchar(100) NOT NULL,
  `isEnabled` tinyint(1) NOT NULL,
  `isUserProfile` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `unsubscribed` tinyint(1) NOT NULL DEFAULT '0',
  `unsubscribedInfo` varchar(256) DEFAULT NULL,
  `userId` binary(16) DEFAULT NULL,
  `userAlertConfigurationId` int DEFAULT NULL,
  `clientAlertConfigurationId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuNotificationRecipientClientIdRecipientUserId` (`clientId`,`recipient`,`userId`),
  KEY `ixNotificationRecipientClientIdUserIdNotificationTypeRecipient` (`clientId`,`userId`,`notificationType`,`recipient`),
  KEY `ixNotificationRecipientNotificationTypeRecipient` (`notificationType`,`recipient`),
  KEY `ixNotificationRecipientRecipient` (`recipient`),
  KEY `ixNotificationRecipientIsUserProfile` (`isUserProfile`),
  KEY `ixNotificationRecipientIsEnabled` (`isEnabled`),
  KEY `ixNotificationRecipientUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `orderShipmentInternational` (
  `imei` varchar(15) NOT NULL,
  `iccid` varchar(50) NOT NULL,
  `model` varchar(50) NOT NULL,
  `labelModel` varchar(50) NOT NULL,
  `partNo` varchar(50) NOT NULL,
  `serialNo` varchar(50) NOT NULL,
  `shipNum` varchar(50) NOT NULL,
  `workOrder` varchar(50) NOT NULL,
  `cartonNo` varchar(50) NOT NULL,
  `location` varchar(6) NOT NULL,
  PRIMARY KEY (`imei`,`shipNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `organization` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `websiteUrl` varchar(1000) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `mainContact` binary(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `placeCategory` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuPlaceCategoryName` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `placeType` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `placeCategoryId` tinyint NOT NULL,
  `clientId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_placeCategoryId_name_clientId` (`placeCategoryId`,`name`,`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `plan` (
  `id` varchar(50) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `billingFrequencyMonths` tinyint NOT NULL,
  `price` float NOT NULL,
  `trialPeriod` varchar(25) DEFAULT NULL,
  `discount` float DEFAULT NULL,
  `platform` varchar(25) DEFAULT NULL,
  `planGroup` varchar(25) DEFAULT NULL,
  `gracePeriod` varchar(25) DEFAULT NULL,
  `status` char(1) NOT NULL,
  `platformPrice` float DEFAULT NULL,
  `servicePlan` varchar(10) DEFAULT NULL,
  `contractLengthMonths` tinyint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `position` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) NOT NULL,
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `actualDate` datetime(3) NOT NULL,
  `speed` float DEFAULT NULL,
  `odometer` double DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL,
  `messageId` binary(16) NOT NULL,
  `batteryPercentage` smallint DEFAULT NULL,
  `clientId` int NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `sendTime` datetime(3) DEFAULT NULL,
  `gpsUtcTime` datetime(3) DEFAULT NULL,
  `externalBatteryPercentage` smallint DEFAULT NULL,
  `externalPowerVoltage` float DEFAULT NULL,
  `azimuth` smallint DEFAULT NULL,
  `motionDetected` tinyint(1) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ixPositionClientId_Imei_ActualDate` (`clientId`,`imei`,`actualDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Trigger table rebuild 2026-06-24';

CREATE TABLE `promotion` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `type` enum('freeService','discount') NOT NULL COMMENT 'The type of promotion: freeService or discount.',
  `discount` decimal(19,4) DEFAULT NULL COMMENT 'The discount value if the type is discount offered in US Dollars.',
  `discountBillingCycles` int DEFAULT NULL COMMENT 'The number of billing cycles that the discount will be applied. Required when type is discount',
  `freeServiceMonths` int DEFAULT NULL COMMENT 'The number of months that the service will be free. Required when type is freeService',
  `startDate` datetime NOT NULL COMMENT 'The date that the promotion will start',
  `endDate` datetime DEFAULT NULL COMMENT 'The date that the promotion will end',
  `status` varchar(25) NOT NULL COMMENT 'The status of the promotion: active, inactive',
  `bannerText` varchar(255) DEFAULT NULL,
  `trackerText` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `promotionHistory` (
  `imei` varchar(15) NOT NULL,
  `promotionId` int NOT NULL,
  `clientId` int DEFAULT NULL,
  `marketPlaceId` varchar(25) DEFAULT NULL COMMENT 'The marketplace of the device at the time the promotion was applied',
  `asin` varchar(25) DEFAULT NULL,
  `promotionType` varchar(25) NOT NULL COMMENT 'The type of promotion: freeService or discount.',
  `promotionDiscount` decimal(19,4) DEFAULT NULL COMMENT 'The discount value if the type is discount offered in US Dollars.',
  `promotionDiscountBillingCycles` int DEFAULT NULL COMMENT 'The number of billing cycles that the discount will be applied. Required when type is discount',
  `promotionFreeServiceMonths` int DEFAULT NULL COMMENT 'The number of months that the service will be free. Required when type is freeService',
  `appliedAt` datetime NOT NULL COMMENT 'The date that the promotion was applied',
  PRIMARY KEY (`imei`,`promotionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `referral` (
  `id` int NOT NULL AUTO_INCREMENT,
  `referrerId` int NOT NULL,
  `referrerClientId` int NOT NULL,
  `code` varchar(36) NOT NULL,
  `invitedEmail` varchar(255) DEFAULT NULL,
  `invitedAt` datetime DEFAULT NULL,
  `firstClickAt` datetime DEFAULT NULL,
  `attributionExpiresAt` datetime DEFAULT NULL,
  `referredClientId` int DEFAULT NULL,
  `referredUserId` binary(16) DEFAULT NULL,
  `referredEmail` varchar(255) DEFAULT NULL,
  `signedUpAt` datetime DEFAULT NULL,
  `activatedAt` datetime DEFAULT NULL,
  `deviceCount` int NOT NULL DEFAULT '0',
  `clockStartedAt` datetime DEFAULT NULL,
  `clockPausedAt` datetime DEFAULT NULL,
  `clockPausedSeconds` int NOT NULL DEFAULT '0',
  `qualifiesAt` datetime DEFAULT NULL,
  `qualifiedAt` datetime DEFAULT NULL,
  `baseMonthlyAmountCents` int DEFAULT NULL,
  `billedAmountCents` int NOT NULL DEFAULT '0',
  `earnedAmountCents` int DEFAULT NULL,
  `creditedAt` datetime DEFAULT NULL,
  `status` enum('invited','attributed','active','qualified','credited','canceled','voided') NOT NULL DEFAULT 'invited',
  `voidReason` enum('refund','cancelled_early','self_referral','fraud','existing_active_customer','referrer_ineligible','attribution_expired','annual_cap_reached') DEFAULT NULL,
  `voidedAt` datetime DEFAULT NULL,
  `parentReferralId` int DEFAULT NULL,
  `tier` tinyint NOT NULL DEFAULT '1',
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuReferralReferredClient` (`referredClientId`),
  KEY `ixReferralCreatedAt` (`createdAt`),
  KEY `ixReferralInvitedEmail` (`invitedEmail`),
  KEY `ixReferralStatusQualifiesAt` (`status`,`qualifiesAt`),
  KEY `ixReferralCode` (`code`),
  KEY `ixReferralReferrerClientStatus` (`referrerClientId`,`status`),
  KEY `ixReferralReferrer` (`referrerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `referralCredit` (
  `id` int NOT NULL AUTO_INCREMENT,
  `referralId` int DEFAULT NULL,
  `clientId` int NOT NULL,
  `customerId` varchar(50) DEFAULT NULL,
  `type` enum('earned','applied','expired','clawback','manual_adjustment') NOT NULL,
  `amountCents` int NOT NULL,
  `issuedAt` datetime NOT NULL,
  `expiresAt` datetime DEFAULT NULL,
  `stripeBalanceTxId` varchar(100) DEFAULT NULL,
  `appliedInvoiceId` varchar(100) DEFAULT NULL,
  `appliedAt` datetime DEFAULT NULL,
  `adjustedByUsername` varchar(255) DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuReferralCreditStripeTx` (`stripeBalanceTxId`),
  KEY `ixReferralCreditClientIssuedAt` (`clientId`,`issuedAt`),
  KEY `ixReferralCreditExpiresAt` (`expiresAt`),
  KEY `ixReferralCreditReferral` (`referralId`),
  KEY `ixReferralCreditClientType` (`clientId`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `referrer` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `code` varchar(36) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `disabledReason` enum('enterprise','no_active_subscription','fraud','manual') DEFAULT NULL,
  `enrolledAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuReferrerClientUser` (`clientId`,`userId`),
  UNIQUE KEY `ixuReferrerCode` (`code`),
  KEY `ixReferrerEnabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `region` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `countryCode` varchar(255) DEFAULT NULL,
  `countryName` varchar(255) DEFAULT NULL,
  `regionName` varchar(255) DEFAULT NULL,
  `regionCode` varchar(255) DEFAULT NULL,
  `regionType` varchar(255) DEFAULT NULL,
  `regionLat` decimal(19,10) DEFAULT NULL,
  `regionLong` decimal(19,10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_countryName_regionCode` (`countryName`,`regionCode`),
  KEY `ix_countryCode_regionCode` (`countryCode`,`regionCode`),
  KEY `ix_countryName_regionName` (`countryName`,`regionName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `reportScheduleConfiguration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `reportName` varchar(100) NOT NULL,
  `reportType` varchar(100) NOT NULL,
  `customReportId` int DEFAULT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'Active',
  `emailMessage` varchar(255) DEFAULT NULL,
  `frequency` varchar(10) DEFAULT NULL,
  `timeOfDay` time DEFAULT NULL,
  `daysOfWeek` json DEFAULT NULL,
  `quarters` json DEFAULT NULL,
  `monthlyOption` varchar(20) DEFAULT NULL,
  `dayOfMonth` int DEFAULT NULL,
  `recipients` varchar(255) DEFAULT NULL,
  `format` varchar(10) DEFAULT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime DEFAULT NULL,
  `parameters` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailAlert` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `alertTypeId` tinyint NOT NULL,
  `clientId` int NOT NULL,
  `value` varchar(5000) DEFAULT NULL,
  `message` varchar(1000) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `storeId` binary(16) NOT NULL,
  `zoneId` binary(16) DEFAULT NULL,
  `isNotification` tinyint(1) DEFAULT NULL,
  `retailClientAlertConfigurationId` int DEFAULT NULL,
  `severity` varchar(50) NOT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `status` varchar(50) DEFAULT 'new',
  `category` varchar(50) NOT NULL,
  `bleTagId` binary(16) DEFAULT NULL,
  `productId` binary(16) DEFAULT NULL,
  `acknowledgedAt` datetime DEFAULT NULL,
  `resolvedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_clientId_alertTypeId_createdAt` (`clientId`,`alertTypeId`,`createdAt`),
  KEY `ix_clientId_status_createdAt` (`clientId`,`status`,`createdAt`),
  KEY `ix_clientId_category_createdAt` (`clientId`,`category`,`createdAt`),
  KEY `ix_clientId_severity_createdAt` (`clientId`,`severity`,`createdAt`),
  KEY `ix_clientId_zoneId_createdAt` (`clientId`,`zoneId`,`createdAt`),
  KEY `ix_clientId_storeId_createdAt` (`clientId`,`storeId`,`createdAt`),
  KEY `ix_clientId_severity_status_createdAt` (`clientId`,`severity`,`status`,`createdAt`),
  KEY `ix_clientId_acknowledgedAt_createdAt` (`clientId`,`acknowledgedAt`,`createdAt`),
  KEY `ix_clientId_createdAt` (`clientId`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailAlertExternalRecipient` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `notificationType` tinyint NOT NULL,
  `recipient` varchar(100) NOT NULL,
  `isEnabled` tinyint(1) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `modifiedAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `unsubscribed` tinyint(1) NOT NULL DEFAULT '0',
  `unsubscribedInfo` varchar(256) DEFAULT NULL,
  `retailClientAlertConfigurationId` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailAlertInternalRecipient` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientAlertConfigurationId` int NOT NULL,
  `userId` binary(16) DEFAULT NULL,
  `roleId` tinyint DEFAULT NULL,
  `isEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `smsNotification` tinyint(1) NOT NULL DEFAULT '1',
  `emailNotification` tinyint(1) NOT NULL DEFAULT '1',
  `appNotification` tinyint(1) NOT NULL DEFAULT '1',
  `pushNotification` tinyint(1) NOT NULL DEFAULT '1',
  `retailClientAlertConfigurationId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_retailClientAlertConfigurationId` (`clientAlertConfigurationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailAlertType` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(2000) NOT NULL,
  `category` varchar(50) NOT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `stage` varchar(10) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailBleTag` (
  `id` binary(16) NOT NULL,
  `tagId` varchar(50) NOT NULL,
  `macAddress` varchar(20) NOT NULL,
  `storeId` binary(16) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `batteryLevel` smallint DEFAULT NULL,
  `lastSeenAt` datetime DEFAULT NULL,
  `lastGatewayId` int DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_tagId` (`tagId`),
  UNIQUE KEY `ix_macAddress` (`macAddress`),
  KEY `ix_storeId` (`storeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailClientAlertConfiguration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `alertTypeId` tinyint NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `severity` varchar(50) NOT NULL,
  `triggerConditions` json DEFAULT NULL,
  `readOnly` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `createdBy` varchar(100) DEFAULT NULL,
  `modifiedAt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `modifiedBy` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_clientId_alertTypeId` (`clientId`,`alertTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailDepartmentType` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  `color` varchar(25) DEFAULT NULL,
  `sortOrder` int NOT NULL DEFAULT '0',
  `isActive` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_clientId_code` (`clientId`,`code`),
  KEY `ix_clientId_isActive` (`clientId`,`isActive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailGateway` (
  `id` binary(16) NOT NULL,
  `storeId` binary(16) NOT NULL,
  `zoneId` binary(16) DEFAULT NULL,
  `macAddress` varchar(20) NOT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'online',
  `lastSeenAt` datetime DEFAULT NULL,
  `firmwareVersion` varchar(20) DEFAULT NULL,
  `signalStrengthThreshold` smallint DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_macAddress` (`macAddress`),
  KEY `ix_storeId_status` (`storeId`,`status`),
  KEY `ix_storeId_zoneId_status` (`storeId`,`zoneId`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailIncident` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `storeId` binary(16) NOT NULL,
  `itemId` binary(16) DEFAULT NULL,
  `type` varchar(20) NOT NULL,
  `status` varchar(20) NOT NULL,
  `severity` varchar(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `estimatedLoss` decimal(19,4) DEFAULT NULL,
  `zoneId` binary(16) DEFAULT NULL,
  `detectedAt` datetime NOT NULL,
  `resolvedAt` datetime DEFAULT NULL,
  `resolvedBy` varchar(50) DEFAULT NULL,
  `resolution` varchar(255) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ix_itemId_detectedAt_type` (`itemId`),
  KEY `ix_clientId_detectedAt_type_estimatedLoss` (`clientId`,`detectedAt`,`type`,`estimatedLoss`),
  KEY `ix_clientId_storeId_type_detectedAt` (`clientId`,`storeId`,`type`,`detectedAt`),
  KEY `ix_clientId_storeId_detectedAt_type_estimatedLoss` (`clientId`,`storeId`,`detectedAt`,`type`,`estimatedLoss`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailItem` (
  `id` binary(16) NOT NULL,
  `bleTagId` binary(16) NOT NULL,
  `productId` binary(16) NOT NULL,
  `storeId` binary(16) NOT NULL,
  `homeZoneId` binary(16) NOT NULL,
  `currentZoneId` binary(16) DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `batteryLevel` smallint DEFAULT NULL,
  `lastMovedAt` datetime DEFAULT NULL,
  `enteredCurrentZoneAt` datetime DEFAULT NULL,
  `receivedAt` datetime NOT NULL,
  `soldAt` datetime DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_bleTagId_productId` (`bleTagId`,`productId`),
  KEY `ix_productId_receivedAt` (`productId`,`receivedAt`),
  KEY `ix_productId_storeId_receivedAt` (`productId`,`storeId`,`receivedAt`),
  KEY `ix_storeId_currentZoneId` (`storeId`,`currentZoneId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailItemEvent` (
  `id` binary(16) NOT NULL,
  `itemId` binary(16) NOT NULL,
  `bleTagId` binary(16) NOT NULL,
  `productId` binary(16) DEFAULT NULL,
  `storeId` binary(16) NOT NULL,
  `eventType` varchar(20) NOT NULL,
  `zoneId` binary(16) DEFAULT NULL,
  `zoneCategory` varchar(50) DEFAULT NULL,
  `departmentTypeId` binary(16) DEFAULT NULL,
  `gatewayId` binary(16) DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `timestamp` datetime(3) NOT NULL,
  `duration` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_storeId_deptType_eventType_ts` (`storeId`,`departmentTypeId`,`eventType`,`timestamp`),
  KEY `ix_bleTagId` (`bleTagId`),
  KEY `ix_itemId_zoneId_eventType_timestamp` (`itemId`,`zoneId`,`eventType`,`timestamp`),
  KEY `ix_itemId_eventType_timestamp_storeId` (`itemId`,`eventType`,`timestamp`,`storeId`),
  KEY `ix_itemId_eventType_zoneCategory_timestamp` (`itemId`,`eventType`,`zoneCategory`,`timestamp`),
  KEY `ix_storeId_itemId_zoneId_eventType_timestamp` (`storeId`,`itemId`,`zoneId`,`eventType`,`timestamp`),
  KEY `ix_storeId_itemId_eventType_timestamp` (`storeId`,`itemId`,`eventType`,`timestamp`),
  KEY `ix_storeId_eventType_zoneCategory_timestamp` (`storeId`,`eventType`,`zoneCategory`,`timestamp`),
  KEY `ix_storeId_eventType_timestamp_productId` (`storeId`,`eventType`,`timestamp`,`productId`),
  KEY `ix_eventType_zoneCategory_timestamp` (`eventType`,`zoneCategory`,`timestamp`),
  KEY `ix_eventType_timestamp_productId` (`eventType`,`timestamp`,`productId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailProduct` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `upc` varchar(12) NOT NULL,
  `sku` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `brand` varchar(50) NOT NULL,
  `category` varchar(50) NOT NULL,
  `departmentTypeId` binary(16) DEFAULT NULL,
  `defaultZoneId` binary(16) DEFAULT NULL,
  `price` decimal(19,4) NOT NULL,
  `imageUrl` varchar(255) DEFAULT NULL,
  `attributes` json DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_clientId_upc` (`clientId`,`upc`),
  KEY `ix_clientId_brand_category` (`clientId`,`brand`,`category`),
  KEY `ix_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailStore` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `storeNumber` varchar(15) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  `zipCode` varchar(30) NOT NULL,
  `country` varchar(50) NOT NULL,
  `timezone` varchar(100) NOT NULL,
  `region` varchar(50) DEFAULT NULL,
  `businessHours` json DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'online',
  `floorplanUrl` varchar(255) DEFAULT NULL,
  `inferenceModel` varchar(255) DEFAULT NULL,
  `inferenceModelMetadata` json DEFAULT NULL,
  `latitude` decimal(8,6) DEFAULT NULL,
  `longitude` decimal(9,6) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ix_clientId_storeNumber` (`clientId`,`storeNumber`),
  KEY `ix_clientId_region_status` (`clientId`,`region`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailTrainingSet` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `storeId` binary(16) NOT NULL,
  `type` varchar(100) NOT NULL,
  `baseTrainingRunId` binary(16) DEFAULT NULL,
  `deviceMacAddress` varchar(20) NOT NULL,
  `performedBy` json DEFAULT NULL,
  `status` varchar(100) NOT NULL DEFAULT 'in_progress',
  `startedAt` datetime DEFAULT NULL,
  `submittedAt` datetime DEFAULT NULL,
  `completedAt` datetime DEFAULT NULL,
  `segmentCount` int DEFAULT NULL,
  `totalDuration` int DEFAULT NULL,
  `coveredZoneIds` json DEFAULT NULL,
  `segments` json DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ix_clientId_storeId` (`clientId`,`storeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `retailZone` (
  `id` binary(16) NOT NULL,
  `storeId` binary(16) NOT NULL,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  `category` varchar(50) NOT NULL,
  `department` varchar(100) DEFAULT NULL,
  `departmentTypeId` binary(16) DEFAULT NULL,
  `sortOrder` int NOT NULL,
  `capacity` int DEFAULT NULL,
  `coordinates` json DEFAULT NULL,
  `color` varchar(25) DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ix_departmentTypeId` (`departmentTypeId`),
  KEY `ix_category` (`category`),
  KEY `ix_storeId_category` (`storeId`,`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `savedView` (
  `id` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `ownerUserId` binary(16) NOT NULL,
  `name` varchar(120) NOT NULL,
  `path` varchar(255) NOT NULL,
  `params` json NOT NULL,
  `sourcePageKey` varchar(80) NOT NULL,
  `filterCount` int NOT NULL DEFAULT '0',
  `hasSort` tinyint(1) NOT NULL DEFAULT '0',
  `hasMapViewport` tinyint(1) NOT NULL DEFAULT '0',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `ixSavedViewClientIdOwnerUserId` (`clientId`,`ownerUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `savedViewShare` (
  `id` int NOT NULL AUTO_INCREMENT,
  `savedViewId` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `recipientUserId` binary(16) NOT NULL,
  `sharedByUserId` binary(16) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxSavedViewShareViewRecipient` (`savedViewId`,`recipientUserId`),
  KEY `ixSavedViewShareClientIdRecipientUserId` (`clientId`,`recipientUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `securityRole` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `key` varchar(25) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(100) NOT NULL,
  `ordinalPosition` tinyint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sensorConfig` (
  `id` binary(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `config` json NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `serviceProvider` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `loginUrl` varchar(1000) DEFAULT NULL,
  `apn` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `serviceProviderProduct` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `productId` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `serviceProviderProductTypeId` tinyint DEFAULT NULL,
  `serviceProviderProductNetworkId` tinyint DEFAULT NULL,
  `mbIncluded` smallint DEFAULT NULL,
  `bundleCost` decimal(19,4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixServiceProviderProductServiceProviderProductTypeId` (`serviceProviderProductTypeId`),
  KEY `ixServiceProviderProductServiceProviderProductNetworkId` (`serviceProviderProductNetworkId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `serviceProviderProductNetwork` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `serviceProviderProductType` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sim` (
  `id` int NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) DEFAULT NULL,
  `iccid` varchar(25) DEFAULT NULL,
  `carrier` tinyint DEFAULT NULL,
  `phone` varchar(25) DEFAULT NULL,
  `deviceTypeId` smallint DEFAULT NULL,
  `serviceProviderProductId` tinyint DEFAULT NULL,
  `isValid` tinyint(1) NOT NULL DEFAULT '1',
  `realModel` varchar(50) DEFAULT NULL,
  `po` varchar(25) DEFAULT NULL,
  `outOfInventory` tinyint(1) NOT NULL DEFAULT '0',
  `apn` varchar(50) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixImeiIccidCarrierProductId` (`serviceProviderProductId`),
  KEY `ixImeiIccidCarrierModifiedAt` (`modifiedAt`),
  KEY `ixImeiIccidCarrierImei` (`imei`),
  KEY `ixImeiIccidCarrierIccid` (`iccid`),
  KEY `ixImeiIccidCarrierDeviceTypeId` (`deviceTypeId`),
  KEY `ixImeiIccidCarrierCarrier` (`carrier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `simConnectivityStatus` (
  `iccid` varchar(25) NOT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `simSid` varchar(50) DEFAULT NULL,
  `isConnected` tinyint(1) NOT NULL DEFAULT '0',
  `lastEventType` varchar(80) DEFAULT NULL,
  `lastEventTime` datetime(3) DEFAULT NULL,
  `lastSeenAt` datetime(3) DEFAULT NULL,
  `networkName` varchar(50) DEFAULT NULL,
  `networkMcc` varchar(5) DEFAULT NULL,
  `networkMnc` varchar(5) DEFAULT NULL,
  `ratType` varchar(20) DEFAULT NULL,
  `apn` varchar(50) DEFAULT NULL,
  `ipAddress` varchar(45) DEFAULT NULL,
  `consecutiveRejections` int NOT NULL DEFAULT '0',
  `lastRejectionCode` int DEFAULT NULL,
  `lastRejectionMessage` varchar(255) DEFAULT NULL,
  `lastRejectionTime` datetime(3) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `modifiedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`iccid`),
  KEY `ixSimConnStatus_rejections` (`consecutiveRejections`),
  KEY `ixSimConnStatus_simSid` (`simSid`),
  KEY `ixSimConnStatus_imei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `simMonthlyUsage` (
  `iccid` varchar(25) NOT NULL,
  `yearMonth` char(7) NOT NULL,
  `bytesUpload` bigint NOT NULL DEFAULT '0',
  `bytesDownload` bigint NOT NULL DEFAULT '0',
  `bytesTotal` bigint NOT NULL DEFAULT '0',
  `sessionCount` int NOT NULL DEFAULT '0',
  `modifiedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`iccid`,`yearMonth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `snapToRoadCache` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `imei` varchar(20) NOT NULL,
  `date` varchar(10) NOT NULL,
  `windowIndex` int NOT NULL DEFAULT '0',
  `inputCount` int NOT NULL DEFAULT '0',
  `snappedPoints` json DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixu_snap_imei_date_window` (`imei`,`date`,`windowIndex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `stripeAddonMapping` (
  `priceId` varchar(100) NOT NULL,
  `internalAddonId` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `billingInterval` smallint NOT NULL,
  PRIMARY KEY (`priceId`),
  KEY `ixStripeAddonMappingBillingInterval` (`billingInterval`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `stripeHardwareMapping` (
  `priceId` varchar(100) NOT NULL,
  `internalHardwareId` varchar(100) NOT NULL,
  `type` enum('trackers','accessories') NOT NULL,
  `price` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `target` enum('consumer','enterprise') NOT NULL DEFAULT 'consumer',
  PRIMARY KEY (`priceId`),
  KEY `ixStripeHardwareMappingTargetInternalHardwareId` (`target`,`internalHardwareId`),
  KEY `ixStripeHardwareMappingInternalHardwareIdType` (`internalHardwareId`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `stripePriceMapping` (
  `priceId` varchar(100) NOT NULL,
  `internalPlanId` varchar(100) NOT NULL,
  `validFrom` date DEFAULT NULL,
  `validUntil` date DEFAULT NULL,
  `isActivePrice` tinyint(1) NOT NULL,
  `type` enum('plan','solution') NOT NULL DEFAULT 'plan',
  PRIMARY KEY (`priceId`),
  KEY `ixStripePriceMappingTypeInternalPlanId` (`type`,`internalPlanId`),
  KEY `ixStripePriceMappingIsActivePriceInternalPlanId` (`isActivePrice`,`internalPlanId`),
  KEY `ixStripePriceMappingInternalPlanIdValidFromValidUntil` (`internalPlanId`,`validFrom`,`validUntil`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `supportConversation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `adaConversationId` varchar(64) NOT NULL,
  `clientId` int DEFAULT NULL,
  `userId` binary(16) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `channel` varchar(40) DEFAULT NULL,
  `startedAt` datetime NOT NULL,
  `endedAt` datetime DEFAULT NULL,
  `escalatedZendeskTicketId` bigint DEFAULT NULL,
  `csatScore` tinyint DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `syncedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `classifiedAt` datetime DEFAULT NULL,
  `issueType` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxSupportConversationAdaId` (`adaConversationId`),
  KEY `ixSupportConversationIssueTypeStartedAt` (`issueType`,`startedAt`),
  KEY `ixSupportConversationClassifiedAt` (`classifiedAt`),
  KEY `ixSupportConversationStartedAt` (`startedAt`),
  KEY `ixSupportConversationEmail` (`email`),
  KEY `ixSupportConversationClientIdStartedAt` (`clientId`,`startedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `supportConversationMessage` (
  `id` int NOT NULL AUTO_INCREMENT,
  `adaMessageId` varchar(64) NOT NULL,
  `conversationId` int NOT NULL,
  `role` varchar(20) NOT NULL,
  `body` mediumtext,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxSupportConversationMessageAdaId` (`adaMessageId`),
  KEY `ixSupportConversationMessageConversationIdCreatedAt` (`conversationId`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `supportInteractionTopic` (
  `id` int NOT NULL AUTO_INCREMENT,
  `interactionType` varchar(16) NOT NULL,
  `interactionId` int NOT NULL,
  `topicId` smallint NOT NULL,
  `confidence` decimal(3,2) DEFAULT NULL,
  `model` varchar(40) DEFAULT NULL,
  `classifiedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxSupportInteractionTopic` (`interactionType`,`interactionId`,`topicId`),
  KEY `ixSupportInteractionTopicTopicIdClassifiedAt` (`topicId`,`classifiedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `supportSyncState` (
  `source` varchar(40) NOT NULL,
  `cursor` varchar(1024) DEFAULT NULL,
  `lastRunAt` datetime(3) DEFAULT NULL,
  `lastStatus` varchar(20) DEFAULT NULL,
  `detail` json DEFAULT NULL,
  PRIMARY KEY (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `supportTicket` (
  `id` int NOT NULL AUTO_INCREMENT,
  `zendeskId` bigint NOT NULL,
  `clientId` int DEFAULT NULL,
  `userId` binary(16) DEFAULT NULL,
  `requesterEmail` varchar(255) DEFAULT NULL,
  `subject` varchar(512) DEFAULT NULL,
  `description` mediumtext,
  `status` varchar(20) NOT NULL,
  `priority` varchar(20) DEFAULT NULL,
  `channel` varchar(40) DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `csatScore` varchar(10) DEFAULT NULL,
  `csatComment` text,
  `assigneeZendeskId` bigint DEFAULT NULL,
  `customFields` json DEFAULT NULL,
  `organizationId` bigint DEFAULT NULL,
  `groupId` bigint DEFAULT NULL,
  `ticketFormId` bigint DEFAULT NULL,
  `type` varchar(40) DEFAULT NULL,
  `collaboratorIds` json DEFAULT NULL,
  `emailCcIds` json DEFAULT NULL,
  `zendeskCreatedAt` datetime NOT NULL,
  `zendeskUpdatedAt` datetime NOT NULL,
  `syncedAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `classifiedAt` datetime DEFAULT NULL,
  `issueType` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxSupportTicketZendeskId` (`zendeskId`),
  KEY `ixSupportTicketIssueTypeCreatedAt` (`issueType`,`zendeskCreatedAt`),
  KEY `ixSupportTicketClassifiedAt` (`classifiedAt`),
  KEY `ixSupportTicketStatus` (`status`),
  KEY `ixSupportTicketCreatedAt` (`zendeskCreatedAt`),
  KEY `ixSupportTicketRequesterEmail` (`requesterEmail`),
  KEY `ixSupportTicketClientIdCreatedAt` (`clientId`,`zendeskCreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `supportTicketComment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `zendeskCommentId` bigint NOT NULL,
  `ticketId` int NOT NULL,
  `authorZendeskId` bigint DEFAULT NULL,
  `authorRole` varchar(20) DEFAULT NULL,
  `isPublic` tinyint(1) NOT NULL DEFAULT '1',
  `body` mediumtext,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxSupportTicketCommentZendeskCommentId` (`zendeskCommentId`),
  KEY `ixSupportTicketCommentTicketIdCreatedAt` (`ticketId`,`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `supportTopic` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `description` varchar(512) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'active',
  `mergedIntoId` smallint DEFAULT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uxSupportTopicName` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tag` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `color` varchar(32) DEFAULT NULL,
  `createdBy` varchar(50) NOT NULL,
  `createdAt` datetime NOT NULL,
  `modifiedBy` varchar(50) DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `isSecurityGroup` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixTagClientIdName` (`clientId`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `task` (
  `id` int NOT NULL AUTO_INCREMENT,
  `taskScheduleId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `dueDate` datetime DEFAULT NULL,
  `dueOdometer` double DEFAULT NULL,
  `dueActiveHours` double DEFAULT NULL,
  `startDate` datetime DEFAULT NULL,
  `startingOdometer` double DEFAULT NULL,
  `startingActiveHours` double DEFAULT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'pending',
  `createdAt` datetime NOT NULL,
  `pastDueAt` datetime DEFAULT NULL,
  `completedAt` datetime DEFAULT NULL,
  `completionNotes` text,
  `completionOdometer` double DEFAULT NULL,
  `completionActiveHours` double DEFAULT NULL,
  `completedBy` varchar(50) DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `modifiedBy` varchar(50) DEFAULT NULL,
  `queuedAt` datetime DEFAULT NULL,
  `predictedDueDate` datetime DEFAULT NULL,
  `deletedAt` datetime DEFAULT NULL,
  `deletedBy` varchar(50) DEFAULT NULL,
  `completionMetadata` json DEFAULT NULL,
  `includeTimeInCompletedAt` tinyint(1) DEFAULT NULL,
  `assignedTo` varchar(50) DEFAULT NULL,
  `timeSpent` double DEFAULT NULL,
  `cost` double DEFAULT NULL,
  `awaitingParts` tinyint(1) DEFAULT '0',
  `awaitingPartsEta` datetime DEFAULT NULL,
  `reminderSentAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixTask_Status` (`status`),
  KEY `ixTask_ClientId` (`clientId`),
  KEY `ixTask_Imei` (`imei`),
  KEY `ixTask_TaskScheduleId` (`taskScheduleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `taskSchedule` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `title` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `repeats` tinyint(1) NOT NULL DEFAULT '0',
  `dueDate` datetime DEFAULT NULL,
  `dueOdometer` double DEFAULT NULL,
  `dueActiveHours` double DEFAULT NULL,
  `intervalDays` double DEFAULT NULL,
  `intervalStartDate` datetime DEFAULT NULL,
  `intervalOdometer` double DEFAULT NULL,
  `intervalStartingOdometer` double DEFAULT NULL,
  `intervalActiveHours` double DEFAULT NULL,
  `intervalStartingActiveHours` double DEFAULT NULL,
  `notes` text,
  `createdBy` varchar(50) NOT NULL,
  `createdAt` datetime NOT NULL,
  `modifiedBy` varchar(50) DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `deletedBy` varchar(50) DEFAULT NULL,
  `deletedAt` datetime DEFAULT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'active',
  `assignedTo` varchar(50) DEFAULT NULL,
  `endsOn` datetime DEFAULT NULL,
  `endsAfterOccurrences` int DEFAULT NULL,
  `reminderDaysBefore` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixTaskScheduleStatus` (`status`),
  KEY `ixTaskScheduleClientId` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `ticketManager` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `type` varchar(256) NOT NULL,
  `sentAt` datetime NOT NULL,
  `expiresAt` datetime NOT NULL,
  `source` varchar(256) NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ixTicketManagerImeiTypeSourceExpiresAt` (`imei`,`type`,`source`,`expiresAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `trip` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tripGuid` binary(16) NOT NULL,
  `imei` varchar(15) NOT NULL,
  `startDate` datetime(3) NOT NULL,
  `endDate` datetime(3) DEFAULT NULL,
  `distance` float NOT NULL,
  `startLat` decimal(8,6) NOT NULL,
  `startLon` decimal(9,6) NOT NULL,
  `endLat` decimal(8,6) DEFAULT NULL,
  `endLon` decimal(9,6) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL,
  `startAddress` varchar(255) NOT NULL,
  `endAddress` varchar(255) DEFAULT NULL,
  `positionStart` varchar(255) NOT NULL,
  `positionEnd` varchar(255) DEFAULT NULL,
  `tripCompletionStatusId` tinyint NOT NULL DEFAULT '1',
  `clientId` int NOT NULL,
  `duration` int NOT NULL,
  `tripType` tinyint NOT NULL,
  `startMessageId` binary(16) NOT NULL,
  `endMessageId` binary(16) DEFAULT NULL,
  `startSendTimeMessage` datetime(3) DEFAULT NULL,
  `endSendTimeMessage` datetime(3) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `startOdometer` double DEFAULT NULL,
  `endOdometer` double DEFAULT NULL,
  `startPositionId` bigint DEFAULT NULL,
  `endPositionId` bigint DEFAULT NULL,
  `forceReason` varchar(32) DEFAULT NULL,
  `summaryVersion` tinyint unsigned NOT NULL DEFAULT '0',
  `summaryAt` datetime(3) DEFAULT NULL,
  `maxSpeed` float DEFAULT NULL,
  `avgSpeed` float DEFAULT NULL,
  `idleSec` int unsigned DEFAULT NULL,
  `polyline` text,
  `alertCount` smallint unsigned DEFAULT NULL,
  `alertTypeIds` varchar(128) DEFAULT NULL,
  `issueCount` smallint unsigned DEFAULT NULL,
  `issueTypeIds` varchar(128) DEFAULT NULL,
  `speedingCount` smallint unsigned DEFAULT NULL,
  `maxOverKph` float DEFAULT NULL,
  `placeIds` varchar(255) DEFAULT NULL,
  `endedOffPlace` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuTripGuid` (`tripGuid`),
  KEY `ixTripClientIdImeiStartDate` (`clientId`,`imei`,`startDate`),
  KEY `ixCreatedAt` (`createdAt`),
  KEY `ixTripEndDate` (`endDate`),
  KEY `ixTripStartDate` (`startDate`),
  KEY `ixTripImei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Trigger table rebuild 2026-06-24';

CREATE TABLE `tripCompletionStatus` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(500) NOT NULL,
  `statusId` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tripPosition` (
  `tripId` bigint NOT NULL,
  `positionId` bigint NOT NULL,
  PRIMARY KEY (`tripId`,`positionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tripStatus` (
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `tripGuid` binary(16) DEFAULT NULL,
  `tripStartDate` datetime DEFAULT NULL,
  PRIMARY KEY (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user` (
  `id` binary(16) NOT NULL,
  `numberId` int NOT NULL AUTO_INCREMENT,
  `firstName` varchar(100) NOT NULL,
  `lastName` varchar(100) NOT NULL,
  `companyName` varchar(255) DEFAULT NULL,
  `telephone` varchar(20) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `postalCode` varchar(30) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `region` varchar(50) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `userName` varchar(255) NOT NULL,
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `status` char(1) DEFAULT 'A',
  `clientId` int NOT NULL,
  `pincode` varchar(20) DEFAULT NULL,
  `isAdmin` tinyint(1) NOT NULL,
  `countryId` smallint DEFAULT NULL,
  `telephoneCountryId` smallint DEFAULT NULL,
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `preferredUserName` varchar(255) DEFAULT NULL,
  `lastLoginAt` datetime DEFAULT NULL,
  `emailVerified` tinyint(1) NOT NULL DEFAULT '1',
  `lat` decimal(8,6) DEFAULT NULL,
  `lon` decimal(9,6) DEFAULT NULL,
  `userAgreementAcceptedAt` datetime DEFAULT NULL,
  `fullPhoneNumber` varchar(20) DEFAULT NULL,
  `profile` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_8eb27834bf79e32decf36fc013` (`numberId`),
  KEY `ixCountryIdRegion` (`countryId`,`region`),
  KEY `ixRegion` (`region`),
  KEY `ixUserTelephone` (`telephone`),
  KEY `ixUserUserName` (`userName`),
  KEY `ixUserStatus` (`status`),
  KEY `ixUserLastLoginAt` (`lastLoginAt`),
  KEY `ixUserEmail` (`email`),
  KEY `ixUserCreatedAt` (`createdAt`),
  KEY `ixUserClientId` (`clientId`),
  KEY `ixUserEmailLower` ((lower(`email`))),
  KEY `ixUserNameLower` ((lower(`userName`))),
  KEY `ixPreferredUserNameLower` ((lower(`preferredUserName`))),
  FULLTEXT KEY `ftUserSearch` (`email`,`firstName`,`lastName`,`userName`,`companyName`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userActivation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createdAt` datetime DEFAULT NULL,
  `status` char(1) DEFAULT 'A',
  `userId` binary(16) NOT NULL,
  `legacyCreatedAt` datetime DEFAULT NULL,
  `clientId` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ixUserActivationUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userActivationDevice` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userActivationId` int NOT NULL,
  `deviceId` int NOT NULL,
  `imei` varchar(15) NOT NULL,
  `model` varchar(50) DEFAULT NULL,
  `billingInterval` varchar(25) DEFAULT NULL,
  `deviceName` varchar(255) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `status` char(1) DEFAULT 'A',
  `trackingReason` varchar(500) DEFAULT NULL,
  `deviceColor` varchar(25) DEFAULT NULL,
  `notificationByEmail` tinyint(1) DEFAULT NULL,
  `notificationBySms` tinyint(1) DEFAULT NULL,
  `planAnnual` tinyint(1) DEFAULT NULL,
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `subscriptionId` varchar(50) DEFAULT NULL,
  `platform` varchar(25) DEFAULT NULL,
  `eventType` varchar(50) DEFAULT NULL,
  `planCode` varchar(50) DEFAULT NULL,
  `servicePlan` varchar(50) DEFAULT NULL,
  `clientId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixUserActivationDeviceImeiCreatedAt` (`imei`,`createdAt`),
  KEY `ixUserActivationDeviceActivationId` (`userActivationId`),
  KEY `ixUserActivationDeviceSubscriptionId` (`subscriptionId`),
  KEY `ixUserActivationDeviceDeviceId` (`deviceId`),
  KEY `ixUserActivationDeviceCreatedAt` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userActivationEvent` (
  `userId` binary(16) NOT NULL,
  `eventId` binary(16) NOT NULL,
  `eventName` text NOT NULL,
  `eventValue` text,
  `eventMetadata` text,
  `createdAt` datetime NOT NULL,
  `platform` varchar(25) DEFAULT NULL,
  `ip` text,
  `userAgent` text,
  PRIMARY KEY (`userId`,`eventId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userActivationMetric` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `firstName` text NOT NULL,
  `lastName` text NOT NULL,
  `email` text NOT NULL,
  `phone` text,
  `trackingReason` text,
  `companyName` text,
  `role` text,
  `industry` text,
  `devicesActivatingCategory` text,
  `assetsCountRange` text,
  `devicesActivatingCount` decimal(10,0) DEFAULT NULL,
  `plan` text,
  `usersInvitedCount` decimal(10,0) DEFAULT NULL,
  `meetingScheduled` tinyint(1) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `planAnnual` text,
  `planId` text,
  `spyEnvironment` text,
  `jarvisScore` int DEFAULT NULL,
  `enrichmentCompanyDescription` text,
  `jarvisEnrichmentCompanyEin` text,
  `enrichmentCompanyEmployees` text,
  `enrichmentCompanyIndustry` text,
  `enrichmentCompanyLocation` text,
  `enrichmentCompanyName` text,
  `enrichmentCompanyRevenue` text,
  `enrichmentPrivateDomain` text,
  `enrichmentPublicTicker` text,
  `enrichmentRole` text,
  `enrichmentTags` text,
  `jenrichmentTitle` text,
  `enrichmentVerifiedEmployment` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuUserActivationMetricUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userAlertComplain` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `alertTypeId` smallint NOT NULL,
  `clientId` int NOT NULL,
  `email` varchar(255) NOT NULL,
  `reason` varchar(25) NOT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuUserAlertComplainUserIdAlertTypeId` (`userId`,`alertTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userAlertConfiguration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `smsNotification` tinyint(1) DEFAULT NULL,
  `emailNotification` tinyint(1) DEFAULT NULL,
  `alertTypeId` smallint NOT NULL,
  `alertValue` varchar(10) DEFAULT NULL,
  `appNotification` tinyint(1) DEFAULT '1',
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `pushNotification` tinyint(1) NOT NULL DEFAULT '1',
  `alertMetadata` varchar(255) DEFAULT NULL,
  `filter` json DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT '1',
  `createdBy` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixUserAlertConfigurationUserIdAlertTypeId` (`userId`,`alertTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userAlertNotificationsConfig` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientAlertConfigurationId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `smsNotification` tinyint(1) NOT NULL DEFAULT '1',
  `emailNotification` tinyint(1) NOT NULL DEFAULT '1',
  `appNotification` tinyint(1) NOT NULL DEFAULT '1',
  `pushNotification` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixUserIdClientAlertConfigurationId` (`userId`,`clientAlertConfigurationId`),
  KEY `ixClientAlertConfigurationId` (`clientAlertConfigurationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userAppFeedback` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `platform` varchar(25) NOT NULL,
  `appVersion` varchar(20) NOT NULL,
  `feedback` varchar(2000) DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `amazonReviewDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixUserAppFeedbackUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userAppIncident` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `userName` varchar(50) DEFAULT NULL,
  `userEmail` varchar(255) DEFAULT NULL,
  `deviceOs` varchar(15) NOT NULL,
  `deviceManufacturer` varchar(100) DEFAULT NULL,
  `deviceModel` varchar(100) DEFAULT NULL,
  `stage` varchar(25) NOT NULL,
  `appVersion` varchar(25) NOT NULL,
  `createdAt` datetime NOT NULL,
  `errorMessage` text,
  `errorFirstReceivedAt` datetime DEFAULT NULL,
  `errorReceivedAt` datetime DEFAULT NULL,
  `deviceJailbroken` tinyint(1) DEFAULT NULL,
  `deviceOsVersion` varchar(20) DEFAULT NULL,
  `deviceFreeMemory` int DEFAULT NULL,
  `deviceTotalMemory` int DEFAULT NULL,
  `deviceCharging` tinyint(1) DEFAULT NULL,
  `deviceBatteryLevel` smallint DEFAULT NULL,
  `deviceNetworkAccess` varchar(100) DEFAULT NULL,
  `appActiveScreen` varchar(50) DEFAULT NULL,
  `appInForeground` tinyint(1) DEFAULT NULL,
  `appDurationInForeground` int DEFAULT NULL,
  `appBundleId` varchar(500) DEFAULT NULL,
  `incidentUrl` varchar(1000) DEFAULT NULL,
  `errorId` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuUserAppIncidentUserIdErrorId` (`userId`,`errorId`),
  KEY `ixUserAppIncidentErrorId` (`errorId`),
  KEY `ixUserAppIncidentCreatedAt` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userAuthorizedBoundary` (
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `boundaryId` int NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`clientId`,`userId`,`boundaryId`),
  KEY `ixUserAuthorizedBoundaryBoundaryIdUserId` (`boundaryId`,`userId`),
  KEY `ixUserAuthorizedBoundaryUserIdBoundaryId` (`userId`,`boundaryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userAuthorizedDevice` (
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `imei` varchar(15) NOT NULL,
  `createdAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`clientId`,`userId`,`imei`),
  KEY `ixUserAuthorizedDeviceImeiUserId` (`imei`,`userId`),
  KEY `ixUserAuthorizedDeviceUserIdImei` (`userId`,`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userCancellationDueDevice` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `subscriptionId` varchar(256) DEFAULT NULL,
  `code` varchar(256) NOT NULL,
  `createdBy` varchar(256) NOT NULL,
  `displayOrder` smallint DEFAULT NULL,
  `scheduledAt` datetime NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_userCancellationDueDeviceCodeCreatedBy` (`code`,`createdBy`),
  KEY `ix_userCancellationDueDeviceCreatedByScheduledAt` (`createdBy`,`scheduledAt`),
  KEY `ix_userCancellationDueDeviceCodeScheduledAt` (`code`,`scheduledAt`),
  KEY `ix_userCancellationDueDeviceClientIdScheduledAt` (`clientId`,`scheduledAt`),
  KEY `ix_userCancellationDueDeviceSubscriptionIdScheduledAt` (`subscriptionId`,`scheduledAt`),
  KEY `ix_userCancellationDueDeviceImeiScheduledAt` (`imei`,`scheduledAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userCarrierDeactivateDevice` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `subscriptionId` varchar(256) DEFAULT NULL,
  `type` varchar(50) NOT NULL,
  `scheduledAt` datetime NOT NULL,
  `executedAt` datetime DEFAULT NULL,
  `finishedAt` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `purgeable` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ixUserCarrierDeactivateDeviceImeiCreatedAt` (`imei`,`createdAt`),
  KEY `ixUserCarrierDeactivateDeviceSubscriptionId` (`subscriptionId`),
  KEY `ixUserCarrierDeactivateDeviceClientIdCreatedAt` (`clientId`,`createdAt`),
  KEY `ixUserCarrierDeactivateDeviceImeiSubscriptionId` (`imei`,`subscriptionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userCarrierReactivateDevice` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `subscriptionId` varchar(256) DEFAULT NULL,
  `type` varchar(50) NOT NULL,
  `scheduledAt` datetime NOT NULL,
  `executedAt` datetime DEFAULT NULL,
  `finishedAt` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ixUserCarrierReactivateDeviceImeiCreatedAt` (`imei`,`createdAt`),
  KEY `ixUserCarrierReactivateDeviceSubscriptionId` (`subscriptionId`),
  KEY `ixUserCarrierReactivateDeviceClientIdCreatedAt` (`clientId`,`createdAt`),
  KEY `ixUserCarrierDeactivateDeviceImeiSubscriptionId` (`imei`,`subscriptionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userConfiguration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `isMetric` tinyint(1) NOT NULL,
  `timezone` varchar(255) DEFAULT 'EST',
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `defaultMapLayer` varchar(25) DEFAULT NULL,
  `speedUnits` varchar(10) DEFAULT NULL,
  `distanceUnits` varchar(10) DEFAULT NULL,
  `alertsNotificationsOn` tinyint(1) DEFAULT '1',
  `dataSourceTypeId` tinyint DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `temperatureUnits` varchar(10) DEFAULT NULL,
  `addressFormat` varchar(100) DEFAULT NULL,
  `assetProfileStatusLabelsEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `trackerClusteringEnabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `ixUserConfigurationUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userDataDeletionRequest` (
  `id` int NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL,
  `clientId` int NOT NULL,
  `userSecurityRoleName` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `status` varchar(25) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixUserDataDeletionRequestUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userDeviceBehaviorTask` (
  `id` binary(16) NOT NULL,
  `deviceBehaviorId` smallint NOT NULL,
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `imei` varchar(15) NOT NULL,
  `behaviorName` varchar(50) NOT NULL,
  `behaviorParams` json DEFAULT NULL,
  `status` varchar(10) NOT NULL,
  `createdAt` datetime NOT NULL,
  `modifiedAt` datetime DEFAULT NULL,
  `extraParams` json DEFAULT NULL,
  `commandTaskId` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixUserDeviceBehaviorTaskModifiedAt` (`modifiedAt`),
  KEY `ixUserDeviceBehaviorTaskDeviceBehaviorId` (`deviceBehaviorId`),
  KEY `ixUserDeviceBehaviorTaskClientId` (`clientId`),
  KEY `ixUserDeviceBehaviorTaskImei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userFreeOfferDevice` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `imei` varchar(15) NOT NULL,
  `clientId` int NOT NULL,
  `subscriptionId` varchar(256) DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client_imei` (`clientId`,`imei`),
  KEY `idx_created_at` (`createdAt`),
  KEY `idx_subscription_id` (`subscriptionId`),
  KEY `idx_imei` (`imei`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userInvitation` (
  `id` binary(16) NOT NULL,
  `numberId` int NOT NULL AUTO_INCREMENT,
  `inviterClientId` int NOT NULL,
  `inviterUserId` binary(16) NOT NULL,
  `inviteeEmail` varchar(255) NOT NULL,
  `inviteeSecurityRoleId` tinyint NOT NULL,
  `statusId` tinyint NOT NULL,
  `createdAt` datetime NOT NULL,
  `modifiedAt` datetime NOT NULL,
  `inviteeUserId` binary(16) DEFAULT NULL,
  `trackerImeis` mediumtext,
  `trackerAccess` text,
  `minAccessDate` datetime DEFAULT NULL,
  `boundaries` text,
  `boundaryAccess` text,
  `tasksAccess` text,
  `customSecurityRoleId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_eed52f71523517126416cc2208` (`numberId`),
  KEY `ixUserInvtationInviteeSecurityRoleId` (`inviteeSecurityRoleId`),
  KEY `ixUserInvtationInviteUserId` (`inviterUserId`),
  KEY `ixUserInvtationInviterClientId` (`inviterClientId`),
  KEY `ixUserInvtationInviteeEmail` (`inviteeEmail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userIssueNotificationsConfig` (
  `id` int NOT NULL AUTO_INCREMENT,
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `issueTypeId` int NOT NULL,
  `pushNotification` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `modifiedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ixuUserIssueNotificationsConfigClientIdUserIdIssueTypeId` (`clientId`,`userId`,`issueTypeId`),
  KEY `ixUserIssueNotificationsConfigClientIdIssueTypeId` (`clientId`,`issueTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userRegistrationAttempt` (
  `id` int NOT NULL AUTO_INCREMENT,
  `firstName` varchar(100) DEFAULT NULL,
  `lastName` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phoneNumber` varchar(20) DEFAULT NULL,
  `phoneCode` varchar(10) DEFAULT NULL,
  `phoneCodeCountryId` varchar(10) DEFAULT NULL,
  `companyName` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `postalCode` varchar(20) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `pin` int DEFAULT NULL,
  `imei` varchar(15) DEFAULT NULL,
  `deviceType` varchar(50) DEFAULT NULL,
  `deviceName` varchar(255) DEFAULT NULL,
  `orderId` varchar(255) DEFAULT NULL,
  `trackingReason` varchar(255) DEFAULT NULL,
  `userName` varchar(50) DEFAULT NULL,
  `plan` varchar(50) DEFAULT NULL,
  `billingFrequency` varchar(50) DEFAULT NULL,
  `planId` varchar(50) DEFAULT NULL,
  `subscriptionSource` varchar(50) DEFAULT NULL,
  `subscriptionId` varchar(50) DEFAULT NULL,
  `platform` varchar(25) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `lastUpdatedAt` datetime DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `clientId` int DEFAULT NULL,
  `countryCode` varchar(3) DEFAULT NULL,
  `tempUserId` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ixUserRegistrationAttemptTempUserId` (`tempUserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `userSecurityRole` (
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `securityRoleId` tinyint NOT NULL,
  `access` enum('full','limited') NOT NULL DEFAULT 'full',
  `boundariesAccess` enum('full','limited') NOT NULL DEFAULT 'full',
  `minAccessDate` datetime DEFAULT NULL,
  `tasksAccess` enum('full','can_complete','read_only','no_access') NOT NULL DEFAULT 'full',
  `devicesAccess` enum('full','limited') NOT NULL DEFAULT 'full',
  `customSecurityRoleId` int DEFAULT NULL,
  PRIMARY KEY (`userId`,`securityRoleId`),
  KEY `ixUserSecurityRoleClientIdAccessUserId` (`clientId`,`access`,`userId`),
  KEY `ixUserSecurityRoleSecurityRoleId` (`securityRoleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `websocketConnection` (
  `connectionId` varchar(36) NOT NULL,
  `clientId` int NOT NULL,
  `userId` binary(16) NOT NULL,
  `host` varchar(255) NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `subscription` json DEFAULT NULL,
  PRIMARY KEY (`connectionId`),
  KEY `ixWebsocketConnectionCreatedAt` (`createdAt`),
  KEY `ixWebsocketConnectionClientIdUserIdHost` (`clientId`,`userId`,`host`),
  KEY `ixWebsocketConnectionUserId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP VIEW IF EXISTS `user_authorized_boundary_security_view`;
CREATE ALGORITHM=UNDEFINED VIEW `user_authorized_boundary_security_view` AS select `b`.`clientId` AS `clientId`,`usr`.`userId` AS `userId`,`b`.`id` AS `boundaryId` from (`userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) join `boundary` `b` on((`b`.`clientId` = `usr`.`clientId`))) where ((`usr`.`boundariesAccess` = 'full') and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `usr`.`clientId`) and (`ut`.`userId` = `usr`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false) union all select `b`.`clientId` AS `clientId`,`usr`.`userId` AS `userId`,`b`.`id` AS `boundaryId` from (((`entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) join `entityTag` `rt` on(((`rt`.`tagId` = `ut`.`tagId`) and (`rt`.`entityType` = 'Boundary') and (`rt`.`isSecurityGroup` = 1) and (`rt`.`clientId` = `ut`.`clientId`)))) join `boundary` `b` on(((`b`.`clientId` = `rt`.`clientId`) and (`b`.`id` = `rt`.`entityId`)))) join `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) on(((`usr`.`clientId` = `rt`.`clientId`) and (`usr`.`userId` = `ut`.`userId`)))) where ((`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1)) union all select `uab`.`clientId` AS `clientId`,`uab`.`userId` AS `userId`,`uab`.`boundaryId` AS `boundaryId` from `userAuthorizedBoundary` `uab` where (exists(select 1 from `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) where ((`usr`.`clientId` = `uab`.`clientId`) and (`usr`.`userId` = `uab`.`userId`) and (`usr`.`boundariesAccess` = 'full'))) is false and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `uab`.`clientId`) and (`ut`.`userId` = `uab`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false);

DROP VIEW IF EXISTS `user_authorized_boundary_security_view_v2`;
CREATE ALGORITHM=UNDEFINED VIEW `user_authorized_boundary_security_view_v2` AS select `b`.`clientId` AS `clientId`,`usr`.`userId` AS `userId`,`b`.`id` AS `boundaryId` from (`userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) join `boundary` `b` on((`b`.`clientId` = `usr`.`clientId`))) where ((`usr`.`boundariesAccess` = 'full') and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `usr`.`clientId`) and (`ut`.`userId` = `usr`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false) union all select `b`.`clientId` AS `clientId`,`usr`.`userId` AS `userId`,`b`.`id` AS `boundaryId` from (((`entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) join `entityTag` `rt` on(((`rt`.`tagId` = `ut`.`tagId`) and (`rt`.`entityType` = 'Boundary') and (`rt`.`isSecurityGroup` = 1) and (`rt`.`clientId` = `ut`.`clientId`)))) join `boundary` `b` on(((`b`.`clientId` = `rt`.`clientId`) and (`b`.`id` = `rt`.`entityId`)))) join `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) on(((`usr`.`clientId` = `rt`.`clientId`) and (`usr`.`userId` = `ut`.`userId`)))) where ((`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1)) union all select `uab`.`clientId` AS `clientId`,`uab`.`userId` AS `userId`,`uab`.`boundaryId` AS `boundaryId` from `userAuthorizedBoundary` `uab` where (exists(select 1 from `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) where ((`usr`.`clientId` = `uab`.`clientId`) and (`usr`.`userId` = `uab`.`userId`) and (`usr`.`boundariesAccess` = 'full'))) is false and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `uab`.`clientId`) and (`ut`.`userId` = `uab`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false);

DROP VIEW IF EXISTS `user_authorized_device_security_view`;
CREATE ALGORITHM=UNDEFINED VIEW `user_authorized_device_security_view` AS select `d`.`clientId` AS `clientId`,`d`.`imei` AS `imei`,`usr`.`userId` AS `userId` from (`userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) join `device` `d` on((`d`.`clientId` = `usr`.`clientId`))) where ((`usr`.`access` = 'full') and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `usr`.`clientId`) and (`ut`.`userId` = `usr`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false) union all select `d`.`clientId` AS `clientId`,`d`.`imei` AS `imei`,`usr`.`userId` AS `userId` from (((`entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) join `entityTag` `rt` USE INDEX (`ixEntityTagImeiIsSecurityGroupTagId`) on(((`rt`.`tagId` = `ut`.`tagId`) and (`rt`.`entityType` = 'Device') and (`rt`.`isSecurityGroup` = 1) and (`rt`.`clientId` = `ut`.`clientId`)))) join `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) on(((`usr`.`clientId` = `rt`.`clientId`) and (`usr`.`userId` = `ut`.`userId`)))) join `device` `d` USE INDEX (`ixDeviceClientIdImei`) on(((`d`.`clientId` = `rt`.`clientId`) and (`d`.`imei` = `rt`.`imei`)))) where ((`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1)) union all select `uad`.`clientId` AS `clientId`,`uad`.`imei` AS `imei`,`uad`.`userId` AS `userId` from `userAuthorizedDevice` `uad` where (exists(select 1 from `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) where ((`usr`.`clientId` = `uad`.`clientId`) and (`usr`.`userId` = `uad`.`userId`) and (`usr`.`access` = 'full'))) is false and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `uad`.`clientId`) and (`ut`.`userId` = `uad`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false);

DROP VIEW IF EXISTS `user_authorized_device_security_view_v2`;
CREATE ALGORITHM=UNDEFINED VIEW `user_authorized_device_security_view_v2` AS select `d`.`clientId` AS `clientId`,`d`.`imei` AS `imei`,`usr`.`userId` AS `userId` from (`userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) join `device` `d` on((`d`.`clientId` = `usr`.`clientId`))) where ((`usr`.`access` = 'full') and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `usr`.`clientId`) and (`ut`.`userId` = `usr`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false) union all select `d`.`clientId` AS `clientId`,`d`.`imei` AS `imei`,`usr`.`userId` AS `userId` from (((`entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) join `entityTag` `rt` USE INDEX (`ixEntityTagImeiIsSecurityGroupTagId`) on(((`rt`.`tagId` = `ut`.`tagId`) and (`rt`.`entityType` = 'Device') and (`rt`.`isSecurityGroup` = 1) and (`rt`.`clientId` = `ut`.`clientId`)))) join `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) on(((`usr`.`clientId` = `rt`.`clientId`) and (`usr`.`userId` = `ut`.`userId`)))) join `device` `d` USE INDEX (`ixDeviceClientIdImei`) on(((`d`.`clientId` = `rt`.`clientId`) and (`d`.`imei` = `rt`.`imei`)))) where ((`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1)) union all select `uad`.`clientId` AS `clientId`,`uad`.`imei` AS `imei`,`uad`.`userId` AS `userId` from `userAuthorizedDevice` `uad` where (exists(select 1 from `userSecurityRole` `usr` USE INDEX (`ixUserSecurityRoleClientIdAccessUserId`) where ((`usr`.`clientId` = `uad`.`clientId`) and (`usr`.`userId` = `uad`.`userId`) and (`usr`.`access` = 'full'))) is false and exists(select 1 from `entityTag` `ut` USE INDEX (`ixEntityTagClientIdUserIdEntityTypeIsSecurityGroupTagId`) where ((`ut`.`clientId` = `uad`.`clientId`) and (`ut`.`userId` = `uad`.`userId`) and (`ut`.`entityType` = 'User') and (`ut`.`isSecurityGroup` = 1))) is false);

