-- Persist DPSA source circulars, page text, department sections and reviewed posts.
ALTER TABLE `JobPost`
    ADD COLUMN `circularNumber` INTEGER NULL,
    ADD COLUMN `circularYear` INTEGER NULL,
    ADD COLUMN `circularTitle` VARCHAR(191) NULL,
    ADD COLUMN `sourceType` VARCHAR(191) NULL,
    ADD COLUMN `sourcePdfUrl` VARCHAR(191) NULL,
    ADD COLUMN `sourcePageStart` INTEGER NULL,
    ADD COLUMN `sourcePageEnd` INTEGER NULL,
    ADD COLUMN `dpsaCircularId` VARCHAR(191) NULL,
    ADD COLUMN `dpsaExtractedPostId` VARCHAR(191) NULL,
    ADD COLUMN `dpsaDepartmentName` VARCHAR(191) NULL,
    ADD COLUMN `numberOfPostsText` VARCHAR(191) NULL,
    ADD COLUMN `sourceStatedPosts` BOOLEAN NOT NULL DEFAULT false,
    ADD COLUMN `requiredDocumentsText` TEXT NULL,
    ADD COLUMN `enquiriesText` TEXT NULL,
    ADD COLUMN `notesText` TEXT NULL;

CREATE TABLE `DpsaCircular` (
    `id` VARCHAR(191) NOT NULL,
    `circularNumber` INTEGER NULL,
    `circularYear` INTEGER NULL,
    `title` VARCHAR(191) NULL,
    `sourceFileName` VARCHAR(191) NOT NULL,
    `originalFileName` VARCHAR(191) NOT NULL,
    `sourcePdfUrl` VARCHAR(191) NOT NULL,
    `sourceLocalPath` VARCHAR(191) NOT NULL,
    `sourceFileHash` VARCHAR(191) NOT NULL,
    `sourceFileSize` INTEGER NULL,
    `pageCount` INTEGER NOT NULL DEFAULT 0,
    `closingDate` DATETIME(3) NULL,
    `importStatus` VARCHAR(191) NOT NULL DEFAULT 'uploaded',
    `uploadedByAdminId` VARCHAR(191) NULL,
    `extractionVersion` VARCHAR(191) NULL,
    `extractionStrategy` VARCHAR(191) NOT NULL DEFAULT 'deterministic',
    `rawText` LONGTEXT NULL,
    `circularNotes` TEXT NULL,
    `applicationDisclaimer` TEXT NULL,
    `extractionReportJson` JSON NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `extractedAt` DATETIME(3) NULL,
    `importedAt` DATETIME(3) NULL,
    `publishedAt` DATETIME(3) NULL,

    UNIQUE INDEX `DpsaCircular_sourceFileHash_key`(`sourceFileHash`),
    UNIQUE INDEX `DpsaCircular_circularYear_circularNumber_key`(`circularYear`, `circularNumber`),
    INDEX `DpsaCircular_importStatus_createdAt_idx`(`importStatus`, `createdAt`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE `DpsaCircularPage` (
    `id` VARCHAR(191) NOT NULL,
    `circularId` VARCHAR(191) NOT NULL,
    `pageNumber` INTEGER NOT NULL,
    `text` LONGTEXT NOT NULL,
    `detectedDepartmentName` VARCHAR(191) NULL,
    `detectedHeadingsJson` JSON NOT NULL,
    `lowTextWarning` BOOLEAN NOT NULL DEFAULT false,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `DpsaCircularPage_circularId_pageNumber_key`(`circularId`, `pageNumber`),
    INDEX `DpsaCircularPage_circularId_idx`(`circularId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE `DpsaDepartmentSection` (
    `id` VARCHAR(191) NOT NULL,
    `circularId` VARCHAR(191) NOT NULL,
    `departmentName` VARCHAR(191) NOT NULL,
    `normalizedName` VARCHAR(191) NOT NULL,
    `startPage` INTEGER NOT NULL,
    `endPage` INTEGER NOT NULL,
    `sectionOrder` INTEGER NOT NULL,
    `applicationsText` TEXT NULL,
    `applicationAddress` TEXT NULL,
    `notesText` TEXT NULL,
    `rawText` LONGTEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `DpsaDepartmentSection_circularId_sectionOrder_key`(`circularId`, `sectionOrder`),
    INDEX `DpsaDepartmentSection_circularId_normalizedName_idx`(`circularId`, `normalizedName`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE `DpsaExtractedPost` (
    `id` VARCHAR(191) NOT NULL,
    `circularId` VARCHAR(191) NOT NULL,
    `departmentSectionId` VARCHAR(191) NULL,
    `postNumber` VARCHAR(191) NULL,
    `title` VARCHAR(191) NOT NULL,
    `branch` VARCHAR(191) NULL,
    `directorate` VARCHAR(191) NULL,
    `component` VARCHAR(191) NULL,
    `departmentName` VARCHAR(191) NOT NULL,
    `numberOfPosts` INTEGER NOT NULL DEFAULT 1,
    `numberOfPostsText` VARCHAR(191) NULL,
    `sourceStatedPosts` BOOLEAN NOT NULL DEFAULT false,
    `referenceNumber` VARCHAR(191) NULL,
    `salaryText` VARCHAR(191) NULL,
    `salaryLevel` VARCHAR(191) NULL,
    `centreText` VARCHAR(191) NULL,
    `province` VARCHAR(191) NULL,
    `city` VARCHAR(191) NULL,
    `requirementsText` LONGTEXT NULL,
    `dutiesText` LONGTEXT NULL,
    `knowledgeSkillsText` LONGTEXT NULL,
    `enquiriesText` TEXT NULL,
    `applicationInstructionsText` LONGTEXT NULL,
    `applicationMethod` VARCHAR(191) NULL,
    `applicationUrl` VARCHAR(191) NULL,
    `applicationEmail` VARCHAR(191) NULL,
    `applicationAddress` TEXT NULL,
    `closingDate` DATETIME(3) NULL,
    `notesText` LONGTEXT NULL,
    `requiredDocumentsText` TEXT NULL,
    `z83Required` BOOLEAN NOT NULL DEFAULT false,
    `employmentType` VARCHAR(191) NULL,
    `educationLevel` VARCHAR(191) NULL,
    `requiredSkillsJson` JSON NOT NULL,
    `requiredDriversLicenceJson` JSON NOT NULL,
    `requiredExperienceYears` INTEGER NOT NULL DEFAULT 0,
    `requiredQualificationsJson` JSON NOT NULL,
    `requiredCertificationsJson` JSON NOT NULL,
    `jobCategoryName` VARCHAR(191) NULL,
    `jobCategorySlug` VARCHAR(191) NULL,
    `sourcePageStart` INTEGER NOT NULL,
    `sourcePageEnd` INTEGER NOT NULL,
    `rawText` LONGTEXT NOT NULL,
    `extractionStrategy` VARCHAR(191) NOT NULL DEFAULT 'deterministic',
    `extractionVersion` VARCHAR(191) NOT NULL,
    `confidenceScore` INTEGER NOT NULL DEFAULT 0,
    `warningsJson` JSON NOT NULL,
    `missingFieldsJson` JSON NOT NULL,
    `duplicateMatchesJson` JSON NOT NULL,
    `reviewStatus` VARCHAR(191) NOT NULL DEFAULT 'pending',
    `importStatus` VARCHAR(191) NOT NULL DEFAULT 'not_imported',
    `reviewedByAdminId` VARCHAR(191) NULL,
    `reviewedAt` DATETIME(3) NULL,
    `mappedJobPostId` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `DpsaExtractedPost_mappedJobPostId_key`(`mappedJobPostId`),
    INDEX `DpsaExtractedPost_circularId_reviewStatus_idx`(`circularId`, `reviewStatus`),
    INDEX `DpsaExtractedPost_circularId_importStatus_idx`(`circularId`, `importStatus`),
    INDEX `DpsaExtractedPost_departmentName_referenceNumber_idx`(`departmentName`, `referenceNumber`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE UNIQUE INDEX `JobPost_dpsaExtractedPostId_key` ON `JobPost`(`dpsaExtractedPostId`);
CREATE INDEX `JobPost_dpsaCircularId_idx` ON `JobPost`(`dpsaCircularId`);
CREATE INDEX `JobPost_sourceType_circularYear_circularNumber_idx` ON `JobPost`(`sourceType`, `circularYear`, `circularNumber`);

ALTER TABLE `DpsaCircularPage`
    ADD CONSTRAINT `DpsaCircularPage_circularId_fkey`
    FOREIGN KEY (`circularId`) REFERENCES `DpsaCircular`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `DpsaDepartmentSection`
    ADD CONSTRAINT `DpsaDepartmentSection_circularId_fkey`
    FOREIGN KEY (`circularId`) REFERENCES `DpsaCircular`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `DpsaExtractedPost`
    ADD CONSTRAINT `DpsaExtractedPost_circularId_fkey`
    FOREIGN KEY (`circularId`) REFERENCES `DpsaCircular`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `DpsaExtractedPost`
    ADD CONSTRAINT `DpsaExtractedPost_departmentSectionId_fkey`
    FOREIGN KEY (`departmentSectionId`) REFERENCES `DpsaDepartmentSection`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `DpsaExtractedPost`
    ADD CONSTRAINT `DpsaExtractedPost_mappedJobPostId_fkey`
    FOREIGN KEY (`mappedJobPostId`) REFERENCES `JobPost`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `JobPost`
    ADD CONSTRAINT `JobPost_dpsaCircularId_fkey`
    FOREIGN KEY (`dpsaCircularId`) REFERENCES `DpsaCircular`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;