-- Public job query indexes. JobPost.slug and JobCategory.slug are already unique.
CREATE INDEX `JobPost_status_createdAt_idx` ON `JobPost`(`status`, `createdAt`);
CREATE INDEX `JobPost_status_province_idx` ON `JobPost`(`status`, `province`);
CREATE INDEX `JobPost_status_categorySlug_idx` ON `JobPost`(`status`, `categorySlug`);
CREATE INDEX `JobPost_status_isGovernment_idx` ON `JobPost`(`status`, `isGovernment`);
CREATE INDEX `JobPost_status_isSponsored_idx` ON `JobPost`(`status`, `isSponsored`);
CREATE INDEX `JobPost_closingDate_idx` ON `JobPost`(`closingDate`);
CREATE INDEX `JobPost_province_city_idx` ON `JobPost`(`province`, `city`);
CREATE INDEX `JobPost_title_idx` ON `JobPost`(`title`);

CREATE TABLE `GuestToolUsage` (
  `id` VARCHAR(191) NOT NULL,
  `anonymousId` VARCHAR(191) NOT NULL,
  `weekStartDate` DATETIME(3) NOT NULL,
  `freeUsesUsed` INTEGER NOT NULL DEFAULT 0,
  `lastUsedAt` DATETIME(3) NULL,
  `ipHash` VARCHAR(191) NULL,
  `userAgentHash` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,

  UNIQUE INDEX `GuestToolUsage_anonymousId_weekStartDate_key`(`anonymousId`, `weekStartDate`),
  INDEX `GuestToolUsage_weekStartDate_idx`(`weekStartDate`),
  INDEX `GuestToolUsage_ipHash_weekStartDate_idx`(`ipHash`, `weekStartDate`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `GuestToolRun` (
  `id` VARCHAR(191) NOT NULL,
  `anonymousId` VARCHAR(191) NOT NULL,
  `toolType` VARCHAR(191) NOT NULL,
  `weekStartDate` DATETIME(3) NOT NULL,
  `consumedFreeUse` BOOLEAN NOT NULL DEFAULT false,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

  INDEX `GuestToolRun_anonymousId_weekStartDate_idx`(`anonymousId`, `weekStartDate`),
  INDEX `GuestToolRun_createdAt_idx`(`createdAt`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;