-- Expand the candidate profile with explicit role, sector and credential matching data.
ALTER TABLE `CandidateProfile`
    ADD COLUMN `preferredRoles` JSON NULL,
    ADD COLUMN `preferredJobCategories` JSON NULL,
    ADD COLUMN `qualifications` JSON NULL,
    ADD COLUMN `certifications` JSON NULL,
    ADD COLUMN `languages` JSON NULL,
    ADD COLUMN `workSectors` JSON NULL,
    ADD COLUMN `willingToRelocate` BOOLEAN NOT NULL DEFAULT false,
    MODIFY `bio` TEXT NULL;

UPDATE `CandidateProfile`
SET `preferredRoles` = JSON_ARRAY(),
    `preferredJobCategories` = JSON_ARRAY(),
    `qualifications` = JSON_ARRAY(),
    `certifications` = JSON_ARRAY(),
    `languages` = JSON_ARRAY(),
    `workSectors` = JSON_ARRAY()
WHERE `preferredRoles` IS NULL
   OR `preferredJobCategories` IS NULL
   OR `qualifications` IS NULL
   OR `certifications` IS NULL
   OR `languages` IS NULL
   OR `workSectors` IS NULL;

ALTER TABLE `CandidateProfile`
    MODIFY `preferredRoles` JSON NOT NULL,
    MODIFY `preferredJobCategories` JSON NOT NULL,
    MODIFY `qualifications` JSON NOT NULL,
    MODIFY `certifications` JSON NOT NULL,
    MODIFY `languages` JSON NOT NULL,
    MODIFY `workSectors` JSON NOT NULL;

-- A completed rewarded advert grants one short-lived, non-transferable generation.
CREATE TABLE `RewardedAdGrant` (
    `id` VARCHAR(191) NOT NULL,
    `userId` VARCHAR(191) NOT NULL,
    `feature` VARCHAR(191) NOT NULL,
    `adUnitId` VARCHAR(191) NULL,
    `transactionId` VARCHAR(191) NULL,
    `clientEarnedAt` DATETIME(3) NULL,
    `verifiedAt` DATETIME(3) NULL,
    `consumedAt` DATETIME(3) NULL,
    `expiresAt` DATETIME(3) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `RewardedAdGrant_transactionId_key`(`transactionId`),
    INDEX `RewardedAdGrant_userId_feature_consumedAt_expiresAt_idx`(`userId`, `feature`, `consumedAt`, `expiresAt`),
    INDEX `RewardedAdGrant_createdAt_idx`(`createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

ALTER TABLE `RewardedAdGrant`
    ADD CONSTRAINT `RewardedAdGrant_userId_fkey`
    FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;