/* This file was generated by ODB, object-relational mapping (ORM)
 * compiler for C++.
 */

CREATE TABLE "public_key" (
  "tenant" TEXT NOT NULL,
  "fingerprint" TEXT NOT NULL,
  "data" TEXT NOT NULL,
  PRIMARY KEY ("tenant",
               "fingerprint"));

ALTER TABLE "package"
  ADD COLUMN "custom_bot" BOOLEAN NULL;

CREATE TABLE "package_build_bot_keys" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "key_tenant" TEXT NOT NULL,
  "key_fingerprint" TEXT NOT NULL);

CREATE INDEX "package_build_bot_keys_object_id_i"
  ON "package_build_bot_keys" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_build_bot_keys_index_i"
  ON "package_build_bot_keys" ("index");

CREATE TABLE "package_build_config_bot_keys" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "config_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "key_tenant" TEXT NOT NULL,
  "key_fingerprint" TEXT NOT NULL);

CREATE INDEX "package_build_config_bot_keys_object_id_i"
  ON "package_build_config_bot_keys" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

ALTER TABLE "public_key"
  ADD CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED;

ALTER TABLE "package_build_bot_keys"
  ADD CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  ADD CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE,
  ADD CONSTRAINT "key_tenant_fk"
    FOREIGN KEY ("key_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  ADD CONSTRAINT "key_fk"
    FOREIGN KEY ("key_tenant",
                 "key_fingerprint")
    REFERENCES "public_key" ("tenant",
                             "fingerprint")
    INITIALLY DEFERRED;

ALTER TABLE "package_build_config_bot_keys"
  ADD CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  ADD CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE,
  ADD CONSTRAINT "key_tenant_fk"
    FOREIGN KEY ("key_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  ADD CONSTRAINT "key_fk"
    FOREIGN KEY ("key_tenant",
                 "key_fingerprint")
    REFERENCES "public_key" ("tenant",
                             "fingerprint")
    INITIALLY DEFERRED;

UPDATE "schema_version"
  SET "version" = 33, "migration" = TRUE
  WHERE "name" = 'package';