-- AlterTable Certificate: allow multiple certs per booking + snapshot/revoke fields
DROP INDEX IF EXISTS "Certificate_bookingId_key";

ALTER TABLE "Certificate" ADD COLUMN "signatoryName" TEXT NOT NULL DEFAULT '';
ALTER TABLE "Certificate" ADD COLUMN "signatoryTitle" TEXT NOT NULL DEFAULT '';
ALTER TABLE "Certificate" ADD COLUMN "signatureVersion" INTEGER NOT NULL DEFAULT 0;
ALTER TABLE "Certificate" ADD COLUMN "signatureApplied" BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE "Certificate" ADD COLUMN "signatureStorageKey" TEXT NOT NULL DEFAULT '';
ALTER TABLE "Certificate" ADD COLUMN "issuedByEmail" TEXT NOT NULL DEFAULT '';
ALTER TABLE "Certificate" ADD COLUMN "issuedByUserId" TEXT NOT NULL DEFAULT '';
ALTER TABLE "Certificate" ADD COLUMN "revokedAt" TIMESTAMP(3);
ALTER TABLE "Certificate" ADD COLUMN "revokedByEmail" TEXT NOT NULL DEFAULT '';
ALTER TABLE "Certificate" ADD COLUMN "reissuedFromId" TEXT NOT NULL DEFAULT '';

CREATE INDEX "Certificate_bookingId_idx" ON "Certificate"("bookingId");
CREATE INDEX "Certificate_status_idx" ON "Certificate"("status");

CREATE TABLE "CertificateSignatory" (
    "id" TEXT NOT NULL,
    "fullName" TEXT NOT NULL,
    "professionalTitle" TEXT NOT NULL,
    "signatureStorageKey" TEXT NOT NULL DEFAULT '',
    "signatureMimeType" TEXT NOT NULL DEFAULT '',
    "signatureWidth" INTEGER NOT NULL DEFAULT 0,
    "signatureHeight" INTEGER NOT NULL DEFAULT 0,
    "active" BOOLEAN NOT NULL DEFAULT true,
    "signatureVersion" INTEGER NOT NULL DEFAULT 1,
    "effectiveDate" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "CertificateSignatory_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "CertificateSignatory_active_updatedAt_idx" ON "CertificateSignatory"("active", "updatedAt");