ALTER TABLE `JobPost`
  ADD COLUMN `governmentSourceName` VARCHAR(191) NULL,
  ADD COLUMN `governmentSourceUrl` TEXT NULL,
  ADD COLUMN `sourceCircularUrl` TEXT NULL,
  ADD COLUMN `sourceCircularNumber` VARCHAR(191) NULL,
  ADD COLUMN `sourceCircularYear` VARCHAR(191) NULL,
  ADD COLUMN `sourceDepartmentUrl` TEXT NULL,
  ADD COLUMN `sourceLastCheckedAt` DATETIME(3) NULL,
  ADD COLUMN `sourceVerified` BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN `referenceNotStated` BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN `closingDateNotStated` BOOLEAN NOT NULL DEFAULT false;

ALTER TABLE `DpsaCircular`
  ADD COLUMN `governmentSourceUrl` TEXT NULL,
  ADD COLUMN `sourceCircularUrl` TEXT NULL,
  ADD COLUMN `sourceLastCheckedAt` DATETIME(3) NULL,
  ADD COLUMN `sourceVerified` BOOLEAN NOT NULL DEFAULT false;

UPDATE `DpsaCircular`
SET
  `governmentSourceUrl` = 'https://www.dpsa.gov.za/newsroom/psvc/',
  `sourceCircularUrl` = CASE
    WHEN `circularNumber` IS NOT NULL AND `circularYear` IS NOT NULL
      THEN CONCAT('https://www.dpsa.gov.za/newsroom/psvc/circular-', LPAD(`circularNumber`, 2, '0'), '-of-', `circularYear`, '/')
    ELSE NULL
  END,
  `sourceLastCheckedAt` = COALESCE(`extractedAt`, `updatedAt`),
  `sourceVerified` = CASE WHEN `circularNumber` IS NOT NULL AND `circularYear` IS NOT NULL THEN true ELSE false END;

UPDATE `JobPost`
SET
  `governmentSourceName` = CASE
    WHEN `sourceType` = 'DPSA' OR `circularNumber` IS NOT NULL
      THEN 'DPSA Public Service Vacancy Circular'
    ELSE NULLIF(`sourceName`, '')
  END,
  `governmentSourceUrl` = CASE
    WHEN `sourceType` = 'DPSA' OR `circularNumber` IS NOT NULL
      THEN 'https://www.dpsa.gov.za/newsroom/psvc/'
    WHEN `sourceUrl` LIKE 'https://%.gov.za/%' OR `sourceUrl` LIKE 'http://%.gov.za/%'
      THEN `sourceUrl`
    ELSE NULL
  END,
  `sourceCircularNumber` = CASE WHEN `circularNumber` IS NOT NULL THEN CAST(`circularNumber` AS CHAR) ELSE NULL END,
  `sourceCircularYear` = CASE WHEN `circularYear` IS NOT NULL THEN CAST(`circularYear` AS CHAR) ELSE NULL END,
  `sourceCircularUrl` = CASE
    WHEN `circularNumber` IS NOT NULL AND `circularYear` IS NOT NULL
      THEN CONCAT('https://www.dpsa.gov.za/newsroom/psvc/circular-', LPAD(`circularNumber`, 2, '0'), '-of-', `circularYear`, '/')
    WHEN `sourceUrl` LIKE 'https://%.gov.za/%' OR `sourceUrl` LIKE 'http://%.gov.za/%' THEN `sourceUrl`
    ELSE NULL
  END,
  `sourceLastCheckedAt` = `updatedAt`,
  `sourceVerified` = CASE
    WHEN (`circularNumber` IS NOT NULL AND `circularYear` IS NOT NULL)
      OR `sourceUrl` LIKE 'https://%.gov.za/%'
      OR `sourceUrl` LIKE 'http://%.gov.za/%'
    THEN true ELSE false END
WHERE `isGovernment` = true;