CREATE TABLE `Identifications` (
  `onboardingId` VARCHAR(100),
  `customId` INT,
  `creation` TIMESTAMPTZ,
  `lastRevision` TIMESTAMPTZ,
  `revisionStatus` CHAR(1),
  `revisionNotes` VARCHAR(2000),
  `ocrTypes` ARRAY(VARCHAR(20)),
  `processSteps` ARRAY(VARCHAR(20)),
  `processStatus` VARCHAR(20),
  `score` SMALLINT,
  `abortReason` VARCHAR(2000),
  `firstName` VARCHAR(100),
  `lastName` VARCHAR(100),
  `nationality` VARCHAR(100),
  `email` VARCHAR(320),
  `phone` VARCHAR(50),
  PRIMARY KEY (`onboardingId`)
);

CREATE TABLE `OCRs` (
  `id` INT,
  `onboardingId` VARCHAR(100),
  `idDocumentFront` INT,
  `idDocumentBack` INT,
  `fields` JSON,
  `validations` JSON,
  `clippings` ARRAY(VARCHAR(20)),
  `mrz` JSON,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`onboardingId`) REFERENCES `Identifications`(`onboardingId`)
);

CREATE TABLE `Antispoofing` (
  `id` INT,
  `onboardingId` VARCHAR(100),
  `photoDoc` INT,
  `photoSelfie` INT,
  `smileChallenge` INT,
  `blinkChallenge` INT,
  `biometryScore` VARCHAR(100),
  PRIMARY KEY (`id`)
);

CREATE TABLE `Challenges` (
  `id` INT,
  `antispoofingId` VARCHAR(100),
  `type` SMALLINT,
  `image` INT,
  `success` BOOLEAN,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`antispoofingId`) REFERENCES `Antispoofing`(`id`)
);

CREATE TABLE `Logs` (
  `onboardingId` VARCHAR(100),
  `timestamp` TIMESTAMPTZ,
  `title` VARCHAR(100),
  `message` VARCHAR(2000),
  PRIMARY KEY (`onboardingId`, `timestamp`),
  FOREIGN KEY (`onboardingId`) REFERENCES `Identifications`(`onboardingId`)
);

CREATE TABLE `SMSEvents` (
  `onboardingId` VARCHAR(100),
  `timestamp` TIMESTAMPTZ,
  `code` VARCHAR(100),
  PRIMARY KEY (`onboardingId`, `timestamp`),
  FOREIGN KEY (`onboardingId`) REFERENCES `Identifications`(`onboardingId`)
);

CREATE TABLE `Metadata` (
  `onboardingId` VARCHAR(100),
  `processId` VARCHAR(100),
  `address` INET,
  `device` VARCHAR(20),
  `browser` VARCHAR(20),
  `os` VARCHAR(20),
  `cameraPermission` BOOLEAN,
  `latitude` VARCHAR(20),
  `longitude` VARCHAR(20),
  PRIMARY KEY (`onboardingId`)
);

CREATE TABLE `Acceptations` (
  `id` INT,
  `onboardingId` VARCHAR(100),
  `text` VARCHAR(2000),
  `accepted` BOOLEAN,
  `mandatory` BOOLEAN,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`onboardingId`) REFERENCES `Identifications`(`onboardingId`)
);

CREATE TABLE `Images` (
  `id` INT,
  `onboardingId` VARCHAR(100),
  `name` VARCHAR(20),
  `thumbnail` TEXT,
  `url` VARCHAR(500),
  PRIMARY KEY (`id`)
);

CREATE TABLE `Changes` (
  `onboardingId` VARCHAR(100),
  `timestamp` TIMESTAMPTZ,
  `operation` VARCHAR(100),
  `message` VARCHAR(2000),
  `user` INT,
  `role` VARCHAR(20),
  PRIMARY KEY (`onboardingId`, `timestamp`),
  FOREIGN KEY (`onboardingId`) REFERENCES `Identifications`(`onboardingId`)
);

CREATE TABLE `ProgressSteps` (
  `onboardingId` VARCHAR(100),
  `position` SMALLINT,
  `name` VARCHAR(20),
  `success` BOOLEAN,
  `message` VARCHAR(500),
  PRIMARY KEY (`onboardingId`, `position`),
  FOREIGN KEY (`onboardingId`) REFERENCES `Identifications`(`onboardingId`)
);