-- CreateTable
CREATE TABLE "invitations" (
    "id" TEXT NOT NULL,
    "clubId" TEXT NOT NULL,
    "playerProfileId" TEXT NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'pending',
    "invitedById" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- CreateIndex
CREATE INDEX "invitations_clubId_idx" ON "invitations"("clubId");

-- CreateIndex
CREATE INDEX "invitations_playerProfileId_idx" ON "invitations"("playerProfileId");

-- CreateIndex
CREATE INDEX "invitations_status_idx" ON "invitations"("status");

-- CreateIndex
CREATE UNIQUE INDEX "invitations_clubId_playerProfileId_status_key" ON "invitations"("clubId", "playerProfileId", "status");

-- AddForeignKey
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_clubId_fkey" FOREIGN KEY ("clubId") REFERENCES "clubs"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_playerProfileId_fkey" FOREIGN KEY ("playerProfileId") REFERENCES "user_profiles"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "invitations" ADD CONSTRAINT "invitations_invitedById_fkey" FOREIGN KEY ("invitedById") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
