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"),
  CONSTRAINT "FK_OCRs.onboardingId"
    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"),
  CONSTRAINT "FK_Challenges.antispoofingId"
    FOREIGN KEY ("antispoofingId")
      REFERENCES "Antispoofing"("id")
);

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

CREATE TABLE "SMSEvents" (
  "onboardingId" VARCHAR(100),
  "timestamp" TIMESTAMPTZ,
  "code" VARCHAR(100),
  PRIMARY KEY ("onboardingId", "timestamp"),
  CONSTRAINT "FK_SMSEvents.onboardingId"
    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"),
  CONSTRAINT "FK_Acceptations.onboardingId"
    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"),
  CONSTRAINT "FK_Changes.onboardingId"
    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"),
  CONSTRAINT "FK_ProgressSteps.onboardingId"
    FOREIGN KEY ("onboardingId")
      REFERENCES "Identifications"("onboardingId")
);