-- Integrations: SiteIntegration, IntegrationDelivery, IntegrationPolicy. Additive only; safe to re-run.
-- Run against the application database (MariaDB), then `pnpm prisma generate` and restart the dev server.

CREATE TABLE IF NOT EXISTS `SiteIntegration` (
  `id` VARCHAR(191) NOT NULL,
  `siteId` VARCHAR(191) NOT NULL,
  `type` VARCHAR(20) NOT NULL,
  `name` VARCHAR(80) NOT NULL,
  `config` LONGTEXT NOT NULL,
  `secretSealed` TEXT NULL,
  `status` VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
  `approvedHash` VARCHAR(64) NULL,
  `reviewNote` VARCHAR(500) NULL,
  `reviewedById` VARCHAR(191) NULL,
  `reviewedAt` DATETIME(3) NULL,
  `createdById` VARCHAR(191) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `SiteIntegration_siteId_type_idx` (`siteId`, `type`),
  KEY `SiteIntegration_status_idx` (`status`),
  CONSTRAINT `SiteIntegration_siteId_fkey` FOREIGN KEY (`siteId`) REFERENCES `Site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `IntegrationDelivery` (
  `id` VARCHAR(191) NOT NULL,
  `integrationId` VARCHAR(191) NOT NULL,
  `event` VARCHAR(40) NOT NULL,
  `ok` TINYINT(1) NOT NULL,
  `httpStatus` INT NULL,
  `error` VARCHAR(300) NULL,
  `durationMs` INT NOT NULL DEFAULT 0,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `IntegrationDelivery_integrationId_createdAt_idx` (`integrationId`, `createdAt`),
  CONSTRAINT `IntegrationDelivery_integrationId_fkey` FOREIGN KEY (`integrationId`) REFERENCES `SiteIntegration` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `IntegrationPolicy` (
  `type` VARCHAR(20) NOT NULL,
  `requiresApproval` TINYINT(1) NOT NULL DEFAULT 0,
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;